Como faço para criar tarefas de manutenção para reconstruir índices na minha instância do RDS para SQL Server?

Data da última atualização: 08/12/2022

Quero criar tarefas de manutenção de reconstrução de índices na minha instância do Amazon Relational Database Service (Amazon RDS) para Microsoft SQL Server. Como posso fazer isso?

Breve descrição

A fragmentação de índices é um problema crítico que pode causar problemas de performance nos bancos de dados do SQL Server se os índices não forem reconstruídos imediatamente. É uma boa prática monitorar o nível de fragmentação e reconstruir e reorganizar os índices regularmente. A fragmentação de índices ocorre como lacunas em páginas de dados e fragmentação lógica.

Observação: a reconstrução de um índice descarta e depois recria esse índice. Dependendo do tipo de índice e da versão do mecanismo de banco de dados, uma operação de reconstrução pode ser feita offline ou online. A reorganização reestrutura as informações nas páginas em vez de descartar e recriar os índices.

O RDS para SQL Server não tem planos de manutenção que criam automaticamente planos e trabalhos para reconstruir ou reorganizar índices. No entanto, você pode usar os seguintes métodos para reconstruir seus índices:

  • Método 1: criar um trabalho de agente do SQL Server para reconstruir índices e atualizar estatísticas.
  • Método 2: criar manualmente o trabalho de reconstrução usando scripts e programe-o conforme necessário.

Resolução

Método 1: criar um trabalho de agente do SQL Server para reconstruir índices e atualizar estatísticas

1.    Inicie o cliente do Microsoft SQL Server Management Studio e faça login nele.

2.    No painel direito, clique com o botão direito do mouse em SQL Server Agent. Em seguida, escolha New (Novo), Job (Trabalho) para criar um trabalho de agente do SQL.

3.    Insira informações em Name (Nome) e Description (Descrição) para o trabalho do agente e selecione OK.

Exemplo:

  • Name: Indexrebuild_job
  • Descrição: trabalho do agente do RDS para SQL Server para reconstrução do índice.

4.    Selecione Steps (Etapas) e depois New (Novo) para adicionar uma etapa de execução. Uma nova janela é exibida.

5.    Insira informações em Step name (Nome da etapa).

6.    Selecione Database (Banco de dados) e adicione o comando que você deseja executar periodicamente.

Veja a seguir um exemplo de comando SQL de reconstrução de índice. Você pode usar este comando de exemplo para reconstruir índices fragmentados de todas as tabelas no banco de dados especificado que excedem 30% de fragmentação. Altere o valor de [DBNAME] na primeira linha para o nome correto do seu banco de dados. Se você executar o mesmo comando SQL para todos os bancos de dados, modifique o comando adequadamente ou crie um trabalho separado para cada banco de dados.

Use [DBNAME]
SET NOCOUNT ON
DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fragment float,@command VARCHAR(4000)
DECLARE AWS_Cusrsor CURSOR FOR
SELECT A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,B.avg_fragmentation_in_percent AS avg_fragment FROM sys.indexes A inner join sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B
ON A.object_id=B.object_id and A.index_id=B.index_id
INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id
INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
WHERE B.avg_fragmentation_in_percent>30  AND A.index_id>0 AND A.IS_DISABLED<>1
ORDER BY tablename,ixname
OPEN AWS_Cusrsor
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
WHILE @@FETCH_STATUS=0
BEGIN
IF @avg_fragment>=30.0
BEGIN
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
--Can add following line for index reorganization. Else remove following line.
SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE';
END
--PRINT @command
EXEC(@command)
FETCH NEXT FROM AWS_Cusrsor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fragment
END
CLOSE AWS_Cusrsor
DEALLOCATE AWS_Cusrsor

7.    Selecione OK.

8.    Selecione Schedules (Agendamentos) e, em seguida, escolha New (Novo) para adicionar uma programação de quando executar o trabalho de reconstrução do índice.

9.    Insira informações em Name (Nome), Schedule type (Tipo de programação) para a programação e campos semelhantes e selecione OK.

10.    Veja o trabalho que você acabou de criar e clique com o botão direito nele. Em seguida, escolha Start Job at Step (Iniciar trabalho na etapa) para executar manualmente o trabalho e verificar se ele pode ser executado corretamente.

Observação: teste os scripts fornecidos neste exemplo em um banco de dados RDS de desenvolvimento antes de implantá-los em um banco de dados RDS de produção. O tempo de reconstrução do índice varia muito com base no tamanho e no número de índices.

O otimizador deve ter informações atualizadas sobre a distribuição dos principais valores (estatísticas) das colunas da tabela para gerar planos de execução ideais. É uma boa prática atualizar as estatísticas de todas as tabelas regularmente. Evite atualizar estatísticas nos dias em que você está reconstruindo índices.

Lembre-se de que as estatísticas de atualização funcionam em uma tabela por vez. O comando em nível de banco de dados sp_updatestats não está disponível no Amazon RDS. Escreva um cursor usando estatísticas de atualização para atualizar as estatísticas de todos os objetos em um banco de dados. Ou crie um wrapper em torno de sp_updatestats e programe-o.

Para usar um wrapper em torno de sp_updatestats, faça o seguinte:

1.    Execute o comando para criar um procedimento armazenado:

create procedure myRDS_updatestats
with execute as ‘dbo’
as
exec sp_updatestats
go

2.    Conceda permissão de execução a um usuário no novo procedimento:

grant execute on myRDS_updatestats to user

3.    Siga as etapas anteriores do Método 1 para programar trabalhos de estatísticas de atualização.

Método 2: criar manualmente o trabalho de reconstrução usando scripts e programe-o conforme necessário

Você pode criar scripts ou procedimentos manualmente para verificar índices fragmentados e executar a reconstrução desses índices com base nesses scripts de acordo com uma programação. Você pode criar seu próprio código e configurar trabalhos de manutenção manual usando scripts.

Você também pode usar os scripts SQL aws-rds-indexmaintenance-job-example do GitHub. Esses scripts reconstroem e reorganizam índices semanalmente, dependendo do nível de fragmentação. O script cria um banco de dados (IndexStats) e objetos (tabelas) para armazenar informações sobre todos os bancos de dados na instância. Essas informações incluem tabelas, índices e porcentagens de fragmentação dos bancos de dados.

aws-rds-indexmaintenance-job-example contém dois scripts: CreateDatabaseAndObjects.sql e CreateWeeklyMaintenanceJob.sql.

O script CreateDatabaseAndObjects.sql faz o seguinte:

  • Cria uma tabela chamada ServerDatabases. Essa tabela registra os bancos de dados na instância atual. Os bancos de dados do sistema (Master, Model, TempDB e msdb) são excluídos. Bancos de dados criados por componentes do SQL Server, como SSIS e SSRS (rdsadmin_ReportServer, rdsadmin_ReportServerTempDB) também são excluídos.
  • Cria uma tabela chamada ServerTables. Essa tabela coleta tabelas para todos os bancos de dados na tabela ServerDatabases.
  • Cria uma tabela chamada Messages. Essa tabela contém a mensagem que foi acionada no índice (REBUILD ou REORGANIZE). Você pode copiar a mensagem e executá-la manualmente, se necessário.

O script CreateWeeklyMaintenanceJob.sql cria o seguinte procedimento armazenado:

  • sp_PopulateDatabases: esse procedimento percorre todos os bancos de dados na instância e os registra na tabela ServerDatabases. Ele não inclui bancos de dados do sistema ou bancos de dados criados por componentes do SQL Server, como SSAS e SSRS. O SSIDB para SSIS está incluído.
  • sp_PopulateTables: esse procedimento percorre cada banco de dados e registra suas tabelas em ServerTables. Depois de gravar as tabelas, ele verifica o esquema ao qual a tabela pertence e, em seguida, procura os índices que ela possui. O procedimento armazenado percorre os índices, procura as informações de índice mais fragmentadas e as registra.
  • Sp_ReindexTables: esse procedimento lê as informações de ServerTables e inicia o processo de reconstrução ou desfragmentação usando as seguintes regras:
    A fragmentação é 0-9% = NOTHING
    A fragmentação é de 10 a 30% = REORGANIZE
    A fragmentação é 31-100% = REBUILD

Para usar os scripts aws-rds-indexmaintenance-job-example do GitHub, copie e execute CreateDatabaseAndObjects.sql primeiro. Em seguida, execute o script CreateWeeklyMaintenanceJob.sql.

Observação: devido às restrições do serviço gerenciado, os trabalhos do SQL Server Agent podem ser criados somente pela conta que está conectada no momento. Nenhuma outra conta é permitida como proprietária de trabalhos.


Este artigo ajudou?


Precisa de ajuda com faturamento ou suporte técnico?