Estou revisando nosso banco de dados e notei que em uma determinada tabela temos os seguintes índices:
Tabela :
Col1 INT IDENTITY(1,1) Primary Key
Col2 INT
...
about 15 more columns
....
ColN VARCHAR(50)
....
another 10 more columns
Além do índice clusterizado de chave primária padrão em Col1, também temos o seguinte índice:
create nonclustered index [iTable-Col1] ON [dbo].[Table1]
(
Col1
)
include ( ColN )
Pesquisamos regularmente Col1e queremos apenas recuperar arquivos ColN. Esse índice é usado porque é o menor índice que contém todos os dados necessários para satisfazer a consulta.
Minha pergunta é, esse índice agrega algum benefício ao SQL Server? Seria melhor abandoná-lo e apenas fazer uma pesquisa no índice clusterizado?
Meu único pensamento é que esse índice é muito menor do que o índice clusterizado (25 MB x 300 MB), o que pode tornar a pesquisa e/ou o armazenamento em cache mais rápido.
O servidor é o SQL Server 2012, se isso fizer alguma diferença.
Na maioria das vezes, esse tipo de índice só é útil para varreduras de índice nessas colunas.
A estrutura da árvore de índice acima do nível folha geralmente será muito semelhante em termos de níveis de árvore; portanto, se for esse o caso, há pouca ou nenhuma diferença de desempenho na navegação na árvore para operações de busca (que também não verificam). Se você quiser aprender o básico sobre índices internos, tenho um vídeo aqui que irá ajudá-lo.
De qualquer forma, de volta aos exames. As razões pelas quais esse índice seria útil são muito menos sobre o índice em si e mais sobre por que não varrer o índice clusterizado (ou manter essas páginas de dados na memória). Se as outras colunas tornam a tabela relativamente larga (com base nos tamanhos que você deu, eu diria que sim, embora não esteja claro o tamanho dessa tabela no contexto de todo o banco de dados), o índice estreito pode ser benéfico em alguns casos . Varreduras frequentes (ou buscas em muitos valores de chave diferentes) tenderão a manter todo o índice na memória, portanto, quanto maior o índice, menos espaço de memória haverá para outros objetos e, é claro, isso incorrerá em mais E/S se o o buffer pool está frio.
Embora seja um caso de uso um tanto raro (eu fiz isso uma ou talvez duas vezes, pelo que me lembro), o maior benefício desse tipo de índice é que ele ajudará muito as consultas que desejam usar uma junção de mesclagem para ingressar nesta tabela. (Observação: otimize a consulta primeiro se uma varredura de índice não for apropriada!) No plano de consulta, uma varredura do índice clusterizado ou uma varredura e classificação de um índice não clusterizado antes de uma junção de mesclagem pode fornecer uma indicação de quando isso pode seja uma boa ideia. Mas, novamente, isso é raro. A consulta deve ser executada com frequência suficiente ou a tabela base cara o suficiente para ficar presa na memória (ou seja, você não precisa dela o tempo todo), para justificar o armazenamento extra e a manutenção do índice.
Se você não tiver certeza de como esse índice está sendo usado agora, verifique o DMV
sys.dm_db_index_usage_stats. Lembre-se de que as contagens são redefinidas na reinicialização do servidor; portanto, se você estiver pensando em descartar o índice, certifique-se de que ele não seja útil durante todo o ciclo de negócios .Deixe o SQL Server responder à sua pergunta, se o índice for útil, o Query Optimizer o usará, caso contrário, não.