SQL Server: New Tools to Diagnose Index Health

Artigo
08/19/2016

SQL Server

Novas ferramentas para diagnosticar a saúde do índice

Randy Dyess

Visão geral:

Fragmentação de índices
Uso de índices
Atividade operacional dos índices

Faça download do código deste artigo: DyessSQLIndex2007_03.exe (151KB)

Um dos principais itens em qualquer lista de verificação para ajuste de desempenho do SQL Server é o refinamento dos índices em um banco de dados. A capacidade do otimizador de consultas do SQL Server de utilizar corretamente os índices durante a execução de uma consulta depende não só da criação de índices efetivos, mas

também do bom funcionamento desses índices. Uma série de DMVs (modos de exibição dinâmicos de gerenciamento) e DMFs (funções dinâmicas de gerenciamento) introduzidos no SQL Server™ 2005 podem ajudar os administradores de banco de dados a determinar a eficácia de seus índices e a descobrir problemas de desempenho.

DMVs e DMFs permitem examinar um servidor e retornar informações sobre o estado desse servidor que ajudem a monitorar o funcionamento e o desempenho da instância do servidor e a diagnosticar problemas. Os administradores de bancos de dados familiarizados com as versões anteriores do SQL Server perceberão que esses DMVs e DMFs substituirão o uso de comandos DBCC, a execução de certos procedimentos armazenados do sistema, a consulta de várias tabelas do sistema e a captura de eventos com o SQL Profiler.

Três funções e modos de exibição principais, ou seja, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats e sys.dm_db_index_operational_stats, estão disponíveis para ajudá-lo a compreender se os seus índices estão funcionando conforme esperado. Eles permitem revisar a E/S e os padrões de bloqueio dos índices e compreender se os índices estão sendo utilizados pelo otimizador de consultas de uma forma a não resultar em uma disputa desnecessária no banco de dados.

Fragmentação de índices

A DMF sys.dm_db_index_physical_stats foi criada como substituição para o DBCC SHOWCONTIG e mostrará uma fragmentação de índice. Entretanto, ao contrário do DBCC SHOWCONTIG, que insere um bloqueio compartilhado (S) na tabela que contém o índice, sys.dm_db_index_physical_stats apenas insere um bloqueio compartilhado intencional (IS), o que reduz significativamente o bloqueio da tabela durante a execução da função.

Para determinar a fragmentação de um índice ao usar sys.dm_db_index_physical_stats, examine uma combinação de três colunas na saída da função. A fragmentação lógica de índices (fragmentação da extensão de heaps) pode ser determinada examinando o valor retornado na coluna avg_fragmentation_in_percent column. A fragmentação lógica é a porcentagem de páginas que estão desorganizadas no nível de folha de um índice, enquanto a fragmentação de extensão é a porcentagem de extensões fora de ordem no nível de folha de um índice. A fragmentação lógica e a fragmentação lógica de extensão podem afetar o desempenho de um índice, exigindo E/S adicional e movimentação do cabeçote de disco, pois esse cabeçote deve saltar de forma a ler as páginas em ordem. Você deve fazer o máximo para manter o nível da fragmentação lógica e da fragmentação de extensão o mais próximo possível de zero.

A fragmentação interna de um índice é a porcentagem de plenitude da página. É claro que você deseja ter a página de índice mais cheia possível, mas também precisa equilibrar a plenitude com base no número de inserções em páginas de índice de forma a manter o número de divisões de página no mínimo absoluto.

O argumento avg_page_space_used_in_percent de sys.dm_db_index_physical_stats deve ser examinado para determinar a plenitude da página de índice. Para configurar corretamente o quão próximo esse número se encontra de 100 por cento, ajuste o fator de preenchimento de um índice ao observar o número de divisões de página que estão ocorrendo. Em um determinado ponto, o número de divisões de páginas começará a aumentar dramaticamente, indicando que você definiu o fator de preenchimento de um índice como um nível maior que o necessário. O ajuste do fator de preenchimento de um índice exige tempo e os testes não devem ser feitos sem um planejamento correto. (Índices que não têm inserções aleatórias em um índice podem ter seus fatores de preenchimento definidos como 100 sem se preocuparem com divisões de páginas maiores.)

Para determinar os níveis de fragmentação de todos os índices na tabela AdventureWorks.HumanResources.Employee, você pode usar uma instrução como esta:

SELECT * 
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL 
-- NULL to view all indexes; 
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information

Use essa DMF para determinar automaticamente quais índices precisam ser reconstruídos, quais precisam ser reorganizados e quais precisam de manutenção. O exame dos valores das colunas avg_page_space_used_in_percent e avg_fragmentation_in_percent dessa DMF para a fragmentação de índice fora do limite aceito de lógica e densidade pode ajudar a determinar qual operação deve ser executada no índice.

Dependendo do estado dos índices, o exemplo mostrado na Figura 1 pode não retornar dados na sua cópia do exemplo do banco de dados da AdventureWorks, mas pode ser facilmente adaptado a outros bancos de dados.

Figure 1 Examinando a plenitude e a fragmentação de páginas

--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10 
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75 
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

Seria fácil armazenar os resultados das consultas em uma variável de tabela e efetuar um loop nessa variável para criar uma seqüência de caracteres dinâmica referente à instrução ALTER INDEX correta (consulte a Figura 2).

Figure 2 Criando uma seqüência de caracteres ALTER INDEX dinâmica

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD' 
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

Uso de índices

Embora sys.dm_db_index_physical_stats seja um eficiente substituto para o DBCC SHOWCONTIG e possa ajudar a revelar o funcionamento de um índice, você muitas vezes enfrentará o problema mais complexo de determinar quais índices são úteis para as consultas executadas com base em uma tabela. Freqüentemente, os desenvolvedores ou administradores de bancos de dados criam índices em uma tabela que eles acham que serão usados pelo otimizador de consultas durante a execução de uma consulta. Em edições anteriores do SQL Server, era muito mais difícil saber se esses índices estavam realmente sendo usados. Era necessário remover o índice e detectar se o desempenho das consultas estava sendo afetado ou capturar os planos de execução das consultas e verificar o uso do índice.

Existe um novo modo de exibição dinâmico de gerenciamento, sys.dm_db_index_usage_stats, que facilita a compreensão de como os índices estão sendo utilizados pelo otimizador de consultas e como as consultas são executadas com base em uma tabela. Esse modo de exibição pode ser examinado para determinar a utilidade de um índice, permitindo que você remova todos os índices que não estão sendo usados pelo otimizador de consultas. Já não é mais necessário preocupar-se com a possibilidade de um índice estar desperdiçando espaço de armazenamento ou de a manutenção de índices não usados estar prejudicando o desempenho do banco de dados.

Com o exame da saída desse DMV para a detecção de índices sem procuras e verificações, você pode determinar se um índice foi usado desde a última vez que o SQL Server foi iniciado. Entretanto, lembre-se de que muitos DMVs e DMFs não são preservados e serão zerados quando o SQL Server for reiniciado. Leve isso em considerado ao usar um DMV ou DMF para determinar o uso de um índice. O índice pode simplesmente não ter sido necessário desde a último vez em que o serviço foi reiniciado, mas é necessário nas consultas executadas aos finais de semana, ao final do mês ou a cada quinze dias.

Para visualizar todos os índices em uma instância que não foram usados desde o último reinício do serviço SQL Server, a seguinte instrução pode ser usada:

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

Atividade operacional dos índices

Se você quiser compreender a atividade operacional dos índices, a DMF sys.dm_db_index_operational_stats será bastante útil. Use-a para visualizar a atividade de E/S, de bloqueio e de método de acesso em cada índice de um banco de dados, o que pode ajudar a compreender como esses índices estão sendo usados e a diagnosticar problemas de bloqueio de índices devido a atividades extensas de E/S ou à existência de uma área de tensão no índice.

Use as colunas de travamento de linha nessa DMF para ajudar a estabelecer o tempo necessário para as operações READ e WRITE adquirirem acesso aos recursos de um índice. Isso pode ajudá-lo a determinar se o subsistema de disco usado para armazenar o índice é adequado para a atividade de E/S desse índice. Também pode indicar se o design e o uso do índice introduziram uma área de tensão na qual a atividade extensa em uma ou mais páginas de um índice causa disputa pelos dados contidos nessas páginas. Essa disputa muitas vezes resulta no bloqueio excessivo de operações que tentam efetuar ações READ ou WRITE nessa área.

A Figura 3 mostra como determinar os padrões de bloqueio e de E/S para todos os índices na tabela AdventureWorks.HumanResources.Employee.

Figure 3 Determinando padrões de bloqueio e de E/S

SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

Descubra mais

Os DMVs e as DMFs apresentados neste artigo podem ser usados de várias outras formas além das descritas aqui. Aproveite para revisar os artigos no SQL Server Books Online que descrevem as funções e os modos de exibição apontados na barra lateral "Recursos adicionais", para compreender a ampla variedade de informações que podem ser retornadas e examinadas com o uso desses recursos.

Para conhecer alguns DMFs e DMVs de índices adicionais não discutidos neste artigo, consulte a publicação de blog feita pela equipe de otimização de consultas do SQL Server no endereço blogs.msdn.com/queryoptteam/570176.aspx (em inglês).

Recursos adicionais

Randy Dyess é orientador na Solid Quality Learning, onde é especialista em sistemas OLTP do SQL Server. Randy é autor de vários livros e artigos sobre o SQL Server. Além disso, é fundador e principal autor dos sites www.TransactSQL.Com e www.Database-Security.Info.