Como identificar índices reais e índices criados automaticamente
Publicado em: 22 de setembro de 2003
Esta dica foi extraída de uma série de dicas apresentadas na SQL Server Magazine. Para obter outras dicas, visite a Central de dicas e truques do SQL Server (em inglês).
P. Observei na tabela sysindexes inúmeras entradas de índices que eu não criei. Soube que não se tratam de índices reais e sim de estatísticas criadas automaticamente pelo otimizador de consulta do SQL Server. Como faço para diferenciar um índice "real" de um criado automaticamente pelo SQL Server?
R. Por padrão, o SQL Server criará estatísticas para as colunas de uma tabela se ainda não existir um índice para as colunas. Em seguida, o otimizador de consulta avaliará as informações estatísticas sobre as distribuições dos intervalos de dados na coluna para escolher um plano de processamento de consulta mais eficaz. É fácil diferenciar uma estatística criada automaticamente de um índice real. No SQL Server 7.0 e no SQL Server 2000, uma estatística criada automaticamente inicia com o prefixo _WA_Sys.
Também é possível identificar se um índice é real ou uma estatística criada automaticamente usando a propriedade IsAutoStatistics da função INDEXPROPERTY(). Deixe o otimizador do SQL Server selecionar as estatísticas a serem criadas. Você também pode manter a opção auto_create_statistics habilitada para bancos de dados que gerencia.
Muitas pessoas não percebem uma conclusão óbvia: a presença de uma estatística criada automaticamente sugere que um índice real poderia ser útil. Considere a saída do seguinte código:
USE tempdb
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.orders'), 'IsUserTable')=1
DROP TABLE dbo.orders
GO
SELECT * INTO tempdb..orders FROM northwind..orders
GO
SELECT * FROM tempdb..orders WHERE orderid = 10248
GO
SELECT * FROM tempdb..sysindexes WHERE id = object_id('orders')
AND name LIKE
'_wa_sys%'
GO
O script faz uma cópia da tabela Northwind Orders em tempdb, seleciona uma linha e verifica se o SQL Server adicionou uma estatística. Certamente essa tabela não tinha um índice para a coluna OrderId, portanto, o SQL Server criou uma estatística denominada _WA_Sys_OrderID_58D1301D automaticamente. A presença de uma estatística para a coluna OrderId é uma dica de que índices adicionais seriam muito úteis na tabela Northwind Orders.
A consulta a seguir mostra o número de estatísticas criadas automaticamente para cada tabela de usuário existente em um banco de dados com pelo menos uma estatística criada automaticamente.
SELECT
object_name(id) TableName
,count(*) NumberOfAutoStats
FROM
sysindexes
WHERE
OBJECTPROPERTY(id, N'IsUserTable') = 1
AND INDEXPROPERTY ( id , name , 'IsAutoStatistics' ) = 1
GROUP BY
object_name(id)
ORDER BY
count(*) DESC
Nem todas as estatísticas devem ser substituídas por um índice real. Houve casos em que o SQL Server criou automaticamente mais de 50 estatísticas para uma única tabela. Obviamente, essas tabelas tinham estratégias de indexação ineficientes. Uma contagem rápida das tabelas e do número de estatísticas criadas automaticamente associadas a elas pode ajudar a identificar quais tabelas necessitam de indexação.
—Equipe de desenvolvimento do SQL Server