Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server e Azure SQL Database: Como Identificar ocorrências de Key Lookup através da plancache

Esse post é a parte 10 de 10 da série Performance Tuning
Tempo de Leitura: 5 minutos

Fala pessoal!
Nesse artigo eu gostaria de compartilhar com vocês um script do Kendal Van Dyke para identificar/encontrar ocorrências de Key Lookup através da plancache, o que pode ser muito útil para facilmente identificar possíveis bons candidatos para uma análise de performance.

Como vocês sabem, ocorrências de KeyLookup geralmente causam um impacto no desempenho bem grande e podem ser facilmente corrigidos criando/alterando índices.

O que é e como evitar o Key Lookup e o RID Lookup

Conforme eu já havia explicado no artigo Entendendo o funcionamento dos índices no SQL Server, quando é realizada uma consulta em uma tabela, o otimizador de consultas do SQL Server irá determinar qual o melhor método de acesso ao dados de acordo com as estatísticas coletadas e escolher o que tiver o menor custo.

Como o índice clustered é a própria tabela, gerando um grande volume de dados, é geralmente utilizado o índice não clustered de menor custo para a consulta.

Isso pode gerar um problema, pois, muitas vezes, o índice não clustered será utilizado para a busca das informações indexadas (Index Seek NonClustered), mas nem todas as colunas solicitadas no select fazem parte do índice.

Quando isso acontece, o índice clustered também terá que ser utilizado para retornar as informações restantes, utilizando o ponteiro para a posição exata da informação no índice clustered (ou o ROWID, caso a tabela não tenha índice cluster).

SQL Server - Key Lookup 2

Essa operação é chamada de Key Lookup, no caso de tabelas com índice clustered ou RID Lookup (RID = Row ID) para tabelas que não possuem índice clustered (chamadas tabelas HEAP) e por gerar 2 operações de leituras para uma única consulta, deve ser evitada sempre que possível.

Key Lookup
SQL Server - Key Lookup Execution Plan 2

RID Lookup
SQL Server - RID Lookup Execution Plan 2

Para evitar o KeyLookup basta utilizar a técnica de cobrir o índice (Covering index), que consiste em adicionar ao índice NonClustered (INCLUDE) as principais colunas que são utilizadas nas consultas à tabela. Isso faz com que o otimizador de consulta consiga obter todas as informações lendo apenas o índice escolhido, sem precisar ler também o índice clustered.

Entretanto, deve-se tomar muita atenção na modelagem dos índices. Não é recomendável adicionar todas as colunas da tabela no índice não cluster, uma vez que ele ficará tão grande que ele não será mais efetivo e o otimizador de consulta poderá até mesmo decidir em não utilizá-lo e preferir o operador Index Scan, que faz a leitura sequencial de todo o índice, prejudicando a performance das consultas.

Para evitar o RID Lookup, basta criar o índice clustered na tabela e prestar atenção aos eventos de Key Lookup que possam vir a surgir.

Importante: Recomendo fortemente a leitura do artigo Entendendo o funcionamento dos índices no SQL Server e também dos artigos que fazem parte da série Performance Tuning.

Como Identificar/Encontrar ocorrências de Key Lookup através da plancache

Agora que você entendeu o que é um Key Lookup e como corrigir, vamos agora aprender como identificar esses eventos de forma fácil e rápida.

Método 1: Utilizando um simples LIKE
Essa consulta abaixo é relativamente simples, fazendo apenas uma consulta em algumas DMV’s e uma consulta textual usando LIKE no plano de execução para procurar pela expressão Lookup=”1″ dentro do XML. O resultado costuma ser bem preciso, mas falso positivos podem aparecer.

Resultado:

Método 2: Utilizando XPath no XML do plano de execução
Esse segundo método é bem mais completo, retornando mais informações e utilizando fórmulas XPath para navegar dentro do XML do plano de execução e retornar os dados de forma mais precisa e correta.

Resultado:

Utilizando uma das consultas acima, você irá facilmente identificar as consultas que estão apresentando o operador de Key Lookup. Agora que você já sabe o que é e como resolver, já pode analisar se vale a pena criar um índice para cobrir essa consulta ou não (Aumento de Espaço em Disco + Aumento no Tempo de Escrita vs Melhoria da Leitura).

Não se esqueça de analisar também os Índices ausentes do seu ambiente. Para saber mais sobre isso, leia o artigo SQL Server – Como identificar todos os índices ausentes (Missing indexes) de um banco de dados.

Um grande abraço e até a próxima!