Missing Index (Índices Ausentes)

Neste post vou falar sobre um recurso novo no SQL Server 2005 chamado Missing Index.

Quando o otimizador elabora um plano de execução, ele analisa os melhores índices para uma condição de pesquisa. Se os melhores índices não forem encontrados o otimizador gera um plano que não seria o ideal, porém registra a ausência destes índices. O otimizador só gera informações sobre os índices ausentes, para queries com cláusula WHERE e que não foram resolvidas utilizando Plano Trivial (Trivial Plane).

Estas informações são mantidas até o SQL Server reiniciar. Nas versões RTM e SP1 são armazenados até 500 índices ausentes, alcançando o limite o otimizador para de registrar. No SP2, quando o limite de 500 é alcançado, o otimizador passa a apagar 20% dos índices menos relevantes para dar espaço a novos índices ausentes.

Cada indice ausente pertence a um grupo de índices ausentes, porém no SQL Server 2005 existe relação de 1-1 entre grupo de índices e índices. Em edições futuras a Microsoft pretende agrupar índices que resolveriam uma query, facilitando a análise de queries complexas que utilizam vários índices.

O SQL Server expõe as informações dos índices ausentes em 3 DMVs (Dynamic Views) e uma função:

► sys.dm_db_missing_index_details
Esta view retorna uma linha para cada índice que o otimizador não encontra ao elaborar o plano de execução de uma query. Ela retorna a lista de colunas que devem ser utilizadas como chave e Include.

► sys.dm_db_missing_index_group_stats
Atualizada a cada execução de query (e não a cada compilação), retornando informações consolidadas sobre grupos de índices ausentes (no SQL 2005 existe uma relação de 1-1 entre grupo de índice e índice).

► sys.dm_db_missing_index_groups
Relaciona cada índice em sys.dm_db_missing_index_details com um grupo de índices em sys.dm_db_missing_index_group_stats.

► dm_db_missing_index_columns
Função que retorna uma tabela com a lista de colunas (chave e Include) que compõe um índice ausente.

Para observar os índices ausentes vamos criar duas tabelas no banco de dados TEMPDB com o script abaixo:

USE tempdb
go
SELECT * INTO dbo.OrderHeader FROM Adventureworks.Sales.SalesOrderHeader;
SELECT * INTO dbo.Customers FROM Adventureworks.Sales.Customer;
go

Agora vamos executar uma query com JOIN utilizando as tabelas criadas no script anterior. Reparem que estas tabelas não possuem índices.

SELECT SalesOrderID, OrderDate, [Status], h.CustomerID, c.AccountNumber
FROM dbo.OrderHeader h JOIN dbo.Customers c ON h.CustomerID = c.CustomerID
WHERE c.TerritoryID = 2

Executando a instrução abaixo, utilizando a DMV sys.dm_db_missing_index_details, podemos observar os índices ausentes gerados pelo otimizador de consultas.

select index_handle, object_name(object_id) as 'Tabela',
equality_columns,inequality_columns,included_columns
from sys.dm_db_missing_index_details
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

A função sys.dm_db_missing_index_columns retorna a relação de colunas de um índice ausente. Ela recebe como parâmetro do handle do índice obtido na coluna index_handle da DMV anterior.

SELECT * FROM sys.dm_db_missing_index_columns(11);

Implementando um JOIN entre as três DMVs obtemos mais informações, como a coluna user_seeks que retorna um contador incrementado a cada query executada que utilizaria o índice ausente.

SELECT d.*,s.* FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
where database_id = db_id('tempdb') and
[object_id] in (object_id('dbo.OrderHeader'),object_id('dbo.Customers'))

No próximo post vou escrever sobre como associar um índice ausente e uma query utilizando outro recurso novo do SQL Server 2005 que é plano de execução em XML.