Quando se trata de otimizar o desempenho do banco de dados, a indexação é um tópico crucial para entender. No SQL Server, existem vários tipos de índices disponíveis, e hoje vamos nos concentrar nos índices não clusterizados. Embora a Microsoft forneça recursos extensos sobre esse tópico, vamos resumir alguns pontos-chave.

O que é um Índice Não Clusterizado?

Um índice não clusterizado no SQL Server é semelhante a um índice clusterizado, pois ambos são estruturas de árvore B. No entanto, ao contrário de um índice clusterizado, um índice não clusterizado não contém os dados físicos no nível das folhas. Em vez disso, ele contém ponteiros para os dados reais. O tipo de ponteiros usados depende se a tabela possui um índice clusterizado ou não.

Se uma tabela tiver um índice clusterizado, os ponteiros no índice não clusterizado são os valores da chave clusterizada. Por outro lado, se a tabela não tiver um índice clusterizado (conhecido como HEAP), os ponteiros do índice não clusterizado são compostos por IDs de linha (RID).

Ao examinar um plano de consulta, você pode encontrar termos como “Key Lookup” ou “RID Lookup”. Isso indica que o SQL Server precisa recuperar dados adicionais das páginas de dados físicos porque eles não estão disponíveis no índice não clusterizado.

Exemplo de Consulta

Vamos considerar a seguinte consulta:

SELECT AddressLine1, City, PostalCode 
FROM Person.Address 
WHERE ModifiedDate = '2007-05-01'

Neste caso, adicionamos um índice não clusterizado (ncl_demo) na coluna ModifiedDate, e o otimizador de consulta escolheu usá-lo. O plano de consulta mostra que a busca de índice não clusterizado em ncl_demo é realizada, retornando 13 linhas. No entanto, a saída inclui a coluna AddressID, que nem mesmo está definida no índice. Isso ocorre porque AddressID é a chave clusterizada, e o nível das folhas do índice não clusterizado contém o RID ou a chave clusterizada.

É importante observar que manter a chave clusterizada estreita é crucial para índices não clusterizados eficientes. Se a chave clusterizada contiver muitas colunas, isso pode limitar severamente o número de linhas por página de índice, tornando o índice ineficiente.

Corrigindo Problemas de Lookup

Se você encontrar problemas de lookup em seus planos de consulta, existem algumas maneiras de resolvê-los. Uma abordagem é adicionar as colunas ausentes à seção “include” do índice não clusterizado. Isso permite que o SQL Server recupere os dados necessários diretamente do índice, eliminando a necessidade de pesquisas adicionais.

Por exemplo, para corrigir o problema de lookup em nossa consulta anterior, podemos modificar o índice não clusterizado da seguinte forma:

CREATE NONCLUSTERED INDEX [ncl_demo] ON [Person].[Address] ([ModifiedDate]) 
INCLUDE ([AddressLine1], [City], [PostalCode]) 
WITH (DROP_EXISTING = ON, ONLINE = ON)

Ao incluir as colunas necessárias no índice, podemos eliminar a busca de chave e melhorar o desempenho da consulta.

Identificando Problemas de Lookup

Para identificar tabelas que apresentam um número significativo de lookups, você pode consultar a exibição de gerenciamento dinâmico (DMV) SYS.DM_DB_INDEX_USAGE_STATS e observar a coluna USER_LOOKUPS. Isso fornecerá informações sobre quais índices podem exigir otimização.

SELECT DB_NAME(database_id) AS db, 
       OBJECT_NAME(object_id, database_id) AS obj, 
       index_id, 
       user_seeks, 
       user_scans, 
       user_lookups, 
       user_updates 
FROM sys.dm_db_index_usage_stats 
ORDER BY user_lookups DESC

Ao analisar os resultados dessa consulta, você pode identificar índices que estão causando pesquisas excessivas e priorizá-los para otimização adicional.

Conclusão

Compreender os índices não clusterizados e seu impacto no desempenho das consultas é essencial para uma gestão eficiente do banco de dados no SQL Server. Ao otimizar os índices e minimizar as pesquisas, você pode melhorar significativamente o desempenho geral das consultas do seu banco de dados.

Para obter informações mais detalhadas sobre ajuste de desempenho do SQL Server e outros tópicos relacionados, não deixe de conferir o HowsMyPlan.com.