Como crio trabalhos de manutenção para reconstruir índices na minha instância do Amazon 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.

O Amazon RDS para SQL Server não tem planos de manutenção que criam automaticamente planos e trabalhos para reconstruir ou reorganizar índices.

No entanto, é possível usar qualquer um dos métodos a seguir para reconstruir seus índices:

• Crie um trabalho do SQL Server agent para reconstruir índices e atualizar estatísticas.
• Use scripts e agendas para criar manualmente um trabalho de reconstrução.

Observação: quando você reconstrói um índice, o mecanismo de banco de dados descarta e 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 off-line ou on-line. Quando você reorganiza um índice, o mecanismo de banco de dados não descarta nem recria o índice. Em vez disso, o mecanismo de banco de dados reestrutura as informações nas páginas.

Resolução

Crie um trabalho do SQL Server Agent para reconstruir índices e atualizar estatísticas

Conclua as etapas a seguir:

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

2. No painel direito, clique com o botão direito do mouse em SQL Server Agent e escolha Novo trabalho.

3. Em Nome, insira um nome para o trabalho do agente e, em Descrição, insira uma descrição.

4. Escolha OK.

5. Escolha Etapas e, em seguida, escolha Novo.

6. Em Nome da etapa, insira um nome para a etapa.

7. 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:

   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

   Observação: substitua DBNAME pelo nome do seu banco de dados. É possível usar esse comando de exemplo anterior para reconstruir índices fragmentados de todas as tabelas no banco de dados especificado que excedam 30% de fragmentação. 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.

8. Escolha OK.

9. Selecione Programações e, em seguida, escolha Nova para adicionar uma programação para quando executar o trabalho de reconstrução do índice.

10. Insira os detalhes da sua agenda e, em seguida, escolha OK.

11. Clique com o botão direito do mouse na tarefa e escolha Iniciar tarefa na etapa para verificar se a tarefa pode ser executada.

Observação: antes de implantar o índice em um banco de dados de produção, teste os scripts do exemplo anterior em um banco de dados RDS de desenvolvimento. O tempo de reconstrução do índice varia 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 regularmente as estatísticas de todas as tabelas. 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 sp_updatestats em nível de banco de dados não está disponível no Amazon RDS. Para obter mais informações, consulte sp_updatestats no site da Microsoft.

Para atualizar as estatísticas de todos os objetos em um banco de dados, use estatísticas de atualização para gravar um cursor. Ou crie um wrapper em torno de sp_updatestats e programe-o.

Para usar um invólucro em torno de sp_updatestats, conclua as seguintes etapas:

1. Execute o comando a seguir para criar um procedimento armazenado:

   create procedure myRDS_updatestatswith 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. Agende trabalhos de atualização de estatísticas.

Use scripts e programações para criar manualmente trabalhos de reconstrução

É possível 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. É possível criar seu próprio código e configurar trabalhos de manutenção manual usando scripts.

Também é possível usar os scripts SQL aws-rds-indexmaintenance-job-example no site do GitHub. Esses scripts reconstroem e reorganizam índices semanalmente, dependendo do nível de fragmentação. Os scripts criam um banco de dados (IndexStats) e objetos (tabelas) para armazenar informações sobre todos os bancos de dados na instância, incluindo 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 cria as seguintes tabelas:

• Uma tabela de Mensagens que contém a mensagem da ação RECONSTRUIR ou REORGANIZAR que ocorre no índice.
  Observação: é possível copiar e executar manualmente a mensagem, se necessário.
• Uma tabela ServerDatabases que registra os bancos de dados na instância atual.
  Observação: os bancos de dados do sistema (Master, Model, TempDB e msdb) são excluídos. A tabela também exclui bancos de dados criados por componentes do SQL Server, como SSIS e SSRS (rdsadmin_ReportServer e rdsadmin_ReportServerTempDB).
• Uma tabela ServerTables que coleta tabelas para todos os bancos de dados na tabela ServerDatabases.

O script CreateWeeklyMaintenanceJob.sql cria o seguinte procedimento armazenado:

• O procedimento sp_PopulateDatabases registra todos os bancos de dados na instância na tabela ServerDatabases.
  Observação: a tabela exclui bancos de dados do sistema ou bancos de dados criados por componentes do SQL Server, como SSAS e SSRS. A tabela inclui SSIDB para SSIS.
• O procedimento sp_PopulateTables registra as tabelas de cada banco de dados em ServerTables.
  Observação: depois que o procedimento registra as tabelas, ele verifica o esquema ao qual a tabela pertence e, em seguida, procura os índices que o esquema tem. Em seguida, o procedimento pesquisa nos índices as informações mais fragmentadas do índice e as registra.
• O procedimento sp_ReindexTables lê as informações do ServerTables e usa as seguintes regras para iniciar o processo de reconstrução ou desfragmentação:
  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 scripts do GitHub, conclua as seguintes etapas:

1. Copie e execute o script CreateDatabaseAndObjects.sql.
2. Em seguida, execute o script CreateWeeklyMaintenanceJob.sql.

Observação: devido às restrições de serviços gerenciados, é possível criar trabalhos do SQL Server Agent somente em uma conta da AWS que esteja conectada ao cliente SSMS.