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

Breve descrição

A fragmentação do índice é um problema crítico que pode causar problemas de desempenho nos bancos de dados do SQL Server se eles não forem recriados imediatamente. É uma prática recomendada monitorar o nível de fragmentação, reconstruir e reorganizar índices regularmente. A fragmentação do índice ocorre como lacunas nas páginas de dados e fragmentação lógica.

Observação: A recriação de um índice descarta e, em seguida, recria o índice. Dependendo do tipo de índice e da versão do mecanismo de banco de dados, uma operação de recriação pode ser feita offline ou online. Reorganizar reestrutura as informações nas páginas em vez de descartar e reconstruir 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: Crie um trabalho do SQL Server agent para reconstruir índices e atualizar estatísticas.
• Método 2: Crie trabalhos de recriação manualmente usando scripts e programados conforme necessário.

Resolução

Método 1: Crie um trabalho do SQL Server Agent para reconstruir índices e atualizar estatísticas

1.    Inicie o cliente Microsoft SQL Server Management Studio e, em seguida, acesse-o.

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

3.    Insira um Nome e Descrição para o trabalho do agente e selecione OK.

Exemplo:

• Nome: Indexrebuild_job
• Descrição: RDS para trabalho do SQL Agent Server para reconstruir índices.

4.    Selecione Etapas e, em seguida, selecione Nova para adicionar uma etapa de execução. Uma nova janela é exibida.

5.    Insira o Nome da etapa.

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

Veja a seguir um exemplo de comando do SQL para reconstruir índices. Você pode usar esse comando de exemplo para reconstruir índices fragmentados de todas as tabelas no banco de dados especificado que excedam 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 Programações e, em seguida, escolha Nova para adicionar uma programação para quando executar o trabalho de reconstrução do índice.

9.    Insira o Nome da programação, o Tipo de programação e campos semelhantes e selecione OK.

10.    Visualize o trabalho que você criou e clique nele com o botão direito do mouse. Em seguida, escolha 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 valores-chave (estatísticas) das colunas da tabela para gerar planos de execução ideais. É uma prática recomendada atualizar as estatísticas de todas as tabelas regularmente. Evite atualizar estatísticas nos dias em que você está reconstruindo índices.

As estatísticas de atualização funcionam em uma tabela por vez. O comando em nível de banco de dados sp_updatestats (no site da Microsoft) não está disponível no Amazon RDS. Grave 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 atualização de estatísticas.

Método 2: Crie trabalhos de reconstrução manualmente usando scripts e programe conforme necessário

Você pode criar scripts ou procedimentos manualmente para verificar índices fragmentados e executar a reconstrução de índices com base neles 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.

O 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 Mensagens. Essa tabela contém a mensagem que foi executada 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 os bancos de dados da 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 por índices que ela tenha. O procedimento armazenado percorre os índices, procura as informações do índice mais fragmentadas e as registra.
• Sp_ReindexTables: Esse procedimento lê as informações das ServerTables e inicia o processo de reconstrução ou desfragmentação usando as seguintes regras:
  A fragmentação é de 0 a 9% = NOTHING
  A fragmentação é de 10 a 30% = REORGANIZE
  A fragmentação é de 31 a 100% = REBUILD

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

Observação: Devido às restrições do serviço gerenciado, as tarefas do SQL Server Agent só podem ser criadas pela conta atualmente conectada. Nenhuma outra conta é permitida como proprietária do trabalho.