Í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 ;
|
Nenhum comentário:
Postar um comentário