SQL Server - Realizando manutenção nos índices do banco de dados

Olá,

No SQL Server diferentemente do Oracle, temos que nos preocupar em verificar com o tempo a fragmentação dos índices das tabelas. Vale a penas salientar que no SQL Server temos dois tipo de índices que são os mais utilizados - (CLUSTERED INDEX e NONCLUSTERED INDEX). No mais, a fragmentação no SQL Server ocorre quando os índices têm páginas nas quais a ordenação lógica no índice com base no valor de chave do índice não corresponde à ordenação física das páginas de índice. O Mecanismo de Banco de Dados modifica os índices automaticamente sempre que são realizadas operações de entrada, atualização ou exclusão nos dados subjacentes. Por exemplo, a adição de linhas em uma tabela pode fazer com que as páginas existentes nos índices de rowstore sejam divididas para liberar espaço para a inserção de novos valores de chave. No decorrer do tempo, essas modificações podem fazer com que as informações do índice sejam dispersadas pelo banco de dados (fragmentadas). Índices intensamente fragmentados podem prejudicar o desempenho da consulta, porque uma E/S adicional é necessária para localizar dados para os quais o índice aponta. E/S adicionais fazem com que a resposta do aplicativo seja lenta, especialmente quando operações de verificação estão envolvidas. Depois que o grau de fragmentação for conhecido, use a seguinte tabela para determinar o melhor método para remover a fragmentação.

Avg_fragmentation_in_percent      Instrução corretiva
--------------------------------- --------------------------------------
> 5% e < = 30%                    ALTER INDEX REORGANIZE
> 30%                             ALTER INDEX REBUILD WITH (ONLINE = ON)

 

Seguindo essa recomendação da Microsoft, costumo utilizar o script abaixo para verificar o grau de fragmentação e realizar a manutenção apropriada (REBUILD ou REORGANIZE). No caso do REORGANIZE, o mesmo pode ser realizado de forma on-line sem prejudicar o desempenho do banco de dados. No caso do REBUILD, caso seja utilizado a versão Enterprise, é melhor adicionar a opção (ONLINE = ON) para não "lockar" a tabela durante a reconstrução do índice.

SELECT distinct (Index_Statement) FROM
(
            SELECT      CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30 THEN
                                   'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA FROM 
                                                                       INFORMATION_SCHEMA.TABLES
                                                                       WHERE 
                                                                       TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = 'BASE TABLE')
                                   + '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REORGANIZE;'
                                   WHEN avg_fragmentation_in_percent > 30 THEN
                                   'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA FROM 
                                                                       INFORMATION_SCHEMA.TABLES
                                                                       WHERE
                                                                       TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = 'BASE TABLE')
                                   + '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REBUILD;'
                                   END AS Index_Statement       
                        FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
                             JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
                        WHERE avg_fragmentation_in_percent > 5
                                   AND index_type_desc <> 'HEAP'
                                   AND page_count > 640) index_rebuild_reorganize
WHERE Index_Statement Is NOT NULL;


Index_Statement
----------------------------------------------------
ALTER INDEX [IDX_TBL_08] ON dbo.[TBL_08] REORGANIZE;
ALTER INDEX [IDX_TBL_07] ON dbo.[TBL_07] REBUILD;
ALTER INDEX [IDX_TBL_06] ON dbo.[TBL_06] REORGANIZE;
ALTER INDEX [IDX_TBL_05] ON dbo.[TBL_05] REBUILD;
ALTER INDEX [IDX_TBL_04] ON dbo.[TBL_04] REORGANIZE;
ALTER INDEX [IDX_TBL_03] ON dbo.[TBL_03] REBUILD;
ALTER INDEX [IDX_TBL_02] ON dbo.[TBL_02] REORGANIZE;
ALTER INDEX [IDX_TBL_01] ON dbo.[TBL_01] REORGANIZE;
ALTER INDEX [PKI_TBL_08] ON dbo.[TBL_08] REORGANIZE;
ALTER INDEX [PKI_TBL_09] ON dbo.[TBL_09] REORGANIZE;

(10 rows affected)


Completion time: 2020-08-07T16:24:33.6867313-03:00