Criação um trabalho para manutenção de instância programada

Cenários

Depois que uma instância de BD é executada por um período de tempo, o desempenho do sistema se deteriora porque os fragmentos de índice aumentam e as estatísticas não são atualizadas em tempo hábil. É aconselhável criar um trabalho de agente SQL para recriar índices periodicamente, atualizar estatísticas e reduzir o banco de dados.

Criar um trabalho de recriação de índice

1. Inicie o cliente do SQL Server Management Studio e faça logon nele como usuário rdsuser.

   

2. Clique com o botão direito do mouse em SQL Server Agent e escolha New > Job para criar um trabalho de SQL Agent.

   

3. Insira o nome e a descrição e clique em OK.

   

4. Selecione Steps e clique em New para adicionar uma etapa de execução.
   Figura 1 Adicionar uma etapa de execução
   

5. Insira o nome, o tipo e o comando da etapa e clique em OK. Defina Command para as instruções SQL que precisam ser executadas periodicamente. Quando o número de fragmentos de índice atinge um valor especificado, por exemplo, 30%, o índice pode ser recriado.
   Figura 2 Configure parâmetros
   

   Execute a seguinte instrução SQL para recriar o índice porque o número de fragmentos de índice de todas as tabelas no dbname especificado excede 30%:

   use [dbname]
   SET NOCOUNT ON
   DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fip float,@command VARCHAR(4000)
   DECLARE IX_Cursor 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_fip 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>10 and B.page_count>20 AND A.index_id>0 AND A.is_disabled<>1
   --AND OS.name='book'
   ORDER BY tablename,ixname
   OPEN IX_Cursor
   FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
   WHILE @@FETCH_STATUS=0
   BEGIN
   IF @avg_fip>=30.0
   BEGIN
   SET @command=N'ALTER INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
   END
   --PRINT @command
   EXEC(@command)
   FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
   END
   CLOSE IX_Cursor
   DEALLOCATE IX_Cursor

   

   Nas instruções SQL anteriores, você só precisa alterar o valor de Use [dbname] na primeira linha para o nome do banco de dados especificado.

   Se você precisar executar as instruções SQL para todos os bancos de dados, modifique as instruções SQL para adicionar a execução cíclica para todos os bancos de dados.

6. Selecione Schedules e clique em New para adicionar um plano de execução programado.
   Figura 3 Adicionar um plano de execução agendado
   

7. Adicione um cronograma executado uma vez por mês, modifique a frequência e a duração diárias e clique em OK.
   Figura 4 Configurar um plano de execução agendado
   

8. Exiba que o trabalho foi criado.
   Figura 5 job
   

9. Clique com o botão direito do mouse no trabalho e escolha Start Job at Step para executar manualmente o trabalho.
   Figura 6 Executar um trabalho.
   

10. Verifique se o trabalho pode ser executado corretamente. Se o trabalho for executado normalmente, o trabalho de manutenção para recriar periodicamente os índices do banco de dados db1 foi criado.

    

Atualizar estatísticas

1. Realize 1 para 4.
2. Insira o nome, o tipo e o comando da etapa e clique em OK. Defina Command como o procedimento armazenado para atualizar estatísticas. Para obter detalhes, consulte Atualização das estatísticas do banco de dados.
   Figura 7 Atualizar estatísticas
   

3. Selecione Schedules e clique em New para adicionar um plano de execução programado.
   Figura 8 Adicionar um plano de execução agendado
   

4. Adicione um cronograma executado uma vez por mês, modifique a frequência e a duração diárias e clique em OK.
   Figura 9 Configurar um plano de execução agendado
   

5. Exiba que o trabalho foi criado.
   Figura 10 Atualizar trabalho de estatística
   

6. Clique com o botão direito do mouse no trabalho e escolha Start Job at Step para executar manualmente o trabalho.

Reduzir o banco de dados periodicamente

1. Realize 1 para 4.
2. Insira o nome, o tipo e o comando da etapa e clique em OK. Defina Command como os comandos SQL para reduzir o banco de dados.

   EXEC [master].[dbo].[rds_shrink_database_log] @dbname='myDbName';

   Defina @dbname como o nome do banco de dados.

3. Selecione Schedules e clique em New para adicionar um plano de execução programado.
   Figura 11 Adicionar um plano de execução agendado
   

4. Adicione um cronograma executado uma vez por mês, modifique a frequência e a duração diárias e clique em OK.
   Figura 12 Configurar um plano de execução agendado
   

5. Clique com o botão direito do mouse no trabalho e escolha Start Job at Step para executar manualmente o trabalho.