Indexação e Fragmentação (SQLServer)
Índices
Hoje, abordarei sobre um tema que para muitos parece ser um mito e algo temoroso. Contudo, tentarei abordar de forma simples
Não é difícil nos depararmos com estruturas de tabelas mal definidas, com tipos de dados que podem ser melhor definidos, tabelas sem índices e consultas SQLs mal escritas.
Um bom time de DBAs tem como uma de suas premissas a execução de procedimentos rotineiras, a maioria deles agendado, para uma boa saúde do banco de dados. Um destes procedimentos é a indexação/desfragmentação de índices.
Os índices são utilizados para potencializar as operações de buscas em banco de dados. Índices bem definidos podem melhorar de forma exponencial o acesso aos dados gravados em disco, reduzindo drasticamente a varredura desnecessária dentro do disco e da estrutura de arquivos e páginas dos blocos de índice.
Recentemente, fui solicitado em uma consultoria para melhorar o desempenho de um GED.
Vamos lá!
As atividades foram executadas em um servidor com Microsoft SQL Server 2008 R2.
Procedimentos
Resolvi analisar o percentual de fragmentação das tabelas.
Observei diversos índices com um alto percentual de fragmentação. Para analisar a fragmentação criei uma stored procedure para auxiliar nos dados, conforme estrutura a seguir:
Stored Procedure utilizada para analisar o percentual de fragmentação:
| CREATE PROCEDURE SP_ESTATISTICA_FRAGMENTACAO AS BEGIN |
|---|
| CREATE TABLE #fragmentacao( object_id integer, tabela VARCHAR(128), index_id integer, nome_indice VARCHAR(500), percentual_fragmentacao numeric(18,6)) |
| DECLARE @Tabela VARCHAR(128) DECLARE @catalogo VARCHAR(128) |
| DECLARE Tabelas CURSOR FAST_FORWARD FOR SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN Tabelas FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela |
| WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #fragmentacao SELECT b.object_id, @tabela, a.index_id, b.name nome_indice, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(@catalogo), OBJECT_ID(@tabela), NULL, NULL, NULL) AS a |
| JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id |
| FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela END |
| CLOSE Tabelas DEALLOCATE Tabelas |
| SELECT * FROM #fragmentacao |
| DROP TABLE #fragmentacao END |
| exec SP_ESTATISTICA_FRAGMENTACAO |
Resolvi aprofundar um pouco mais nas consultas e recuperei para cada tabela o espaço consumido por Dados, Índices, Reservado, Não utilizado e o Número de tuplas. Para isso fiz a seguinte view:
View para análise de dados das tabelas:
| CREATE VIEW VW_ESTATISTICA_TABELA AS SELECT OBJECT_NAME(object_id) As Tabela, Rows As Linhas, SUM(Total_Pages * 8) As Reservado, SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados, SUM(Used_Pages * 8) - SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice, SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado FROM sys.partitions As P INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id where OBJECT_NAME(object_id) not like 'sys%' and OBJECT_NAME(object_id) not like 'ifts%' and OBJECT_NAME(object_id) not like 'queue%' and OBJECT_NAME(object_id) not like 'fulltext%' and OBJECT_NAME(object_id) not like 'frag%' and OBJECT_NAME(object_id) not like 'file%' GROUP BY OBJECT_NAME(object_id), Rows |
|---|
| SELECT * FROM VW_ESTATISTICA_TABELA ORDER BY linhas desc |
Feitas as devidas verificações passei para o passo seguinte que foi gerar os dados do antes da desfragmentação e reindexação das tabelas.
Em algumas tabelas só a desfragmentação não foi suficiente e foi necessária a reindexação.
Veja os códigos utilizados:
| -- COMANDO UTILIZADO PARA REINDEXAR A TABELA DBCC DBREINDEX ('nome_tabela'); |
|---|
| -- COMANDO PARA RECONSTRUIR O INDICIE ALTER INDEX nome_indice ON nome_tabela REBUILD; |
| -- COMANDO PARA DESFRAGMENTAR TODOS OS INDICES DA TABELA ALTER INDEX ALL ON ACS_CIDADE REORGANIZE ; |
| -- COMANDO PARA DESFRAGMENTAR INDIVIDUALMENTE CADA ÍNDICE ALTER INDEX idx_acsCidade_idEstado ON ACS_CIDADE REORGANIZE ; |