O que são índices de cobertura e consultas abrangidas no SQL Server?

Você pode explicar os conceitos e os relacionamentos entre os Índices de Cobertura e as Consultas Cobertas no SQL Server da Microsoft?

Um índice de cobertura é aquele que pode satisfazer todas as colunas solicitadas em uma consulta sem executar uma pesquisa adicional no índice clusterizado.

Não existe uma consulta de cobertura.

Dê uma olhada neste artigo do Simple-Talk: Usando índices de cobertura para melhorar o desempenho da consulta .

Se todas as colunas solicitadas na lista de select de consulta estiverem disponíveis no índice , o mecanismo de consulta não precisará procurar a tabela novamente, o que pode aumentar significativamente o desempenho da consulta. Como todas as colunas solicitadas estão disponíveis no índice, o índice está cobrindo a consulta. Portanto, a consulta é chamada de consulta de cobertura e o índice é um índice de cobertura.

Um índice clusterizado sempre pode cobrir uma consulta, se as colunas na lista de seleção forem da mesma tabela.

Os links a seguir podem ser úteis se você for novo nos conceitos de índice:

  • Excelente vídeo sobre vantagens e desvantagens do índice e abrangendo consultas e índices.
  • Índices no SQL Server

Um índice de cobertura é um índice não agrupado. Os índices Clustered e Non-Clustered usam B-Trees para melhorar a pesquisa de dados, a diferença é que nas folhas de um Clustered Index um registro inteiro (ou seja, linha) é armazenado fisicamente ali mesmo !, mas esse não é o caso de Índices não agrupados.

Exemplo: Eu tenho uma tabela com três colunas: ID, Fname e Lname.

insira a descrição da imagem aqui

No entanto, para um índice não agrupado, há duas possibilidades: a tabela já possui um índice de cluster ou não:

insira a descrição da imagem aqui

Como os dois diagramas mostram, esses índices não-agrupados não fornecem um bom desempenho, porque eles não podem encontrar o valor favorito (ou seja, Lname) somente da tree B. Em vez disso, eles precisam fazer uma etapa extra de pesquisa (procurar chave ou RID) para encontrar o valor de Lname. E é aí que o índice coberto chega à canvas. Aqui, o índice Non-Clustered no ID cobre o valor de Lname próximo a ele nas folhas da B-Tree e não há mais necessidade de nenhum tipo de consulta.

insira a descrição da imagem aqui

Uma consulta coberta é uma consulta em que todas as colunas no conjunto de resultados da consulta são extraídas de índices não agrupados.

Uma consulta é feita em uma consulta coberta pelo arranjo criterioso de índices.

Uma consulta coberta geralmente tem mais desempenho do que uma consulta não coberta, em parte porque os índices não agrupados têm mais linhas por página do que índices clusterizados ou índices de heap, portanto, menos páginas precisam ser colocadas na memory para satisfazer a consulta. Eles têm mais linhas por página porque apenas parte da linha da tabela faz parte da linha do índice.

Um índice de cobertura é um índice usado em uma consulta coberta. Não existe um índice que, por si só, é um índice de cobertura. Um índice pode ser um índice de cobertura com relação à consulta A, enquanto ao mesmo tempo não é um índice de cobertura com relação à consulta B.

Aqui está um artigo no devx.com que diz:

Criando um índice não clusterizado que contém todas as colunas usadas em uma consulta SQL, uma técnica chamada cobertura de índice

Eu só posso supor que uma consulta coberta é uma consulta que tem um índice que abrange todas as colunas em seu conjunto de registros retornado. Uma ressalva – o índice e a consulta teriam que ser criados para permitir que o SQL Server inferisse realmente da consulta que o índice é útil.

Por exemplo, uma junit de uma tabela em si pode não se beneficiar de tal índice (dependendo da inteligência do planejador de execução de consulta SQL):

 PersonID ParentID Name 1 NULL Abe 2 NULL Bob 3 1 Carl 4 2 Dave 

Vamos supor que haja um índice em PersonID,ParentID,Name – esse seria um índice de cobertura para uma consulta como:

 SELECT PersonID, ParentID, Name FROM MyTable 

Mas uma consulta como esta:

 SELECT PersonID, Name FROM MyTable LEFT JOIN MyTable T ON T.PersonID=MyTable.ParentID 

Provavelmente não se beneficiaria muito, apesar de todas as colunas estarem no índice. Por quê? Porque você não está realmente dizendo que você quer usar o índice triplo de PersonID,ParentID,Name .

Em vez disso, você está construindo uma condição com base em duas colunas – PersonID e ParentID (que exclui Name ) e, em seguida, solicita todos os registros, com as colunas PersonID, Name . Na verdade, dependendo da implementação, o índice pode ajudar a última parte. Mas para a primeira parte, é melhor você ter outros índices.

Uma consulta de cobertura é sobre onde todos os predicados podem ser correspondidos usando os índices nas tabelas subjacentes.

Este é o primeiro passo para melhorar o desempenho do SQL em consideração.

Um índice de cobertura é aquele que fornece todas as colunas obrigatórias e em que o servidor SQL não tem o salto de volta ao índice clusterizado para localizar qualquer coluna. Isso é obtido usando o índice não agrupado e usando a opção INCLUDE para cobrir colunas. Colunas não chave podem ser incluídas somente em índices não clusterizados. As colunas não podem ser definidas na coluna-chave e na lista INCLUDE. Os nomes das colunas não podem ser repetidos na lista INCLUDE. As colunas não chave podem ser eliminadas de uma tabela somente depois que o índice não chave é descartado primeiro. Por favor, veja os detalhes aqui

Quando eu simplesmente recordei que um índice clusterizado consistia em uma lista não-montada ordenada por chave de todas as colunas da tabela definida, as luzes se acenderam para mim. A palavra “cluster”, então, refere-se ao fato de que há um “cluster” de todas as colunas, como um grupo de peixes naquele “ponto quente”. Se não houver nenhum índice cobrindo a coluna que contém o valor procurado (o lado direito da equação), o plano de execução usa um índice de cluster buscado na representação do índice de cluster da coluna solicitada porque não encontra a coluna solicitada em qualquer outro índice “cobrindo”. A falta causará um operador Clustered Index Seek no Plano de Execução proposto, onde o valor procurado está dentro de uma coluna dentro da lista ordenada representada pelo Índice de Cluster.

Portanto, uma solução é criar um índice não clusterizado que tenha a coluna contendo o valor solicitado dentro do índice. Dessa forma, não há necessidade de referenciar o Índice em Cluster e o Otimizador deve conseguir ligar esse índice no Plano de Execução sem nenhuma dica. Se, no entanto, houver um predicado nomeando a chave de cluster de coluna única e um argumento para um valor escalar na chave de cluster, o operador de busca de índice clusterizado ainda será usado, mesmo se já houver um índice de cobertura em uma segunda coluna no tabela sem um índice.