Eu tenho uma consulta que tem uma condição opcional where na coluna classID(not unique). Este mesmo classIDestá sendo exibido como uma coluna de seleção em várias outras consultas. Atualmente eu tenho classID como uma coluna incluída em um índice idx, então acho que o idx será usado quando classID estiver na instrução select. Eu tenho que criar um índice não clusterizado para o classID como uma coluna de chave quando a consulta tem onde condição para classID.?
Confuso sobre isso se deve criar separado non-clustered indexespara todas as colunas da cláusula where.?
Não, na verdade não. O índice selecionado no plano de consulta depende do otimizador de consulta decidir qual índice melhor satisfaz os requisitos de consulta. Se suas colunas de chave de índice corresponderem ou forem muito semelhantes aos seus predicados de consulta, ela provavelmente selecionará esse índice.
Ter ClassID nas colunas INCLUDE significa simplesmente que o plano de consulta não exigirá pesquisas de chave para recuperar esses dados adicionais sobre as linhas retornadas na consulta. Se ClassID for um predicado, tê-lo nas colunas INCLUDE de um índice não fará com que o otimizador selecione definitivamente esse índice.
Você precisa criar índices que melhor satisfaçam o acesso de dados mais comum. Por exemplo, se você criar vários índices com colunas de chave única e muitas colunas de inclusão, mas suas consultas tiverem muitos predicados e poucas colunas de seleção, é improvável que sua estratégia de indexação seja tão eficiente quanto deveria.
Um bom ponto de partida/regra geral enquanto você está começando com o ajuste de índice\design é não mais que 5 índices por tabela e não mais que 5 colunas por índice. Isso ajuda você a ser mais seletivo sobre quais índices você cria e como os projeta para satisfazer a maioria das consultas.
Não é uma regra rígida e rápida, apenas ajuda você a pensar sobre sua estratégia de índice em relação às suas consultas.
Você não cria índices separados para cada predicado porque o otimizador provavelmente usará apenas um deles e, em seguida, fará várias pesquisas de chave. Projete seus índices com esta diretriz básica:
Essas não são regras rígidas e rápidas e, novamente, não projetam seus índices para satisfazer uma única consulta. Você precisa traçar o perfil de suas características de acesso a dados e desenvolver uma estratégia de indexação para satisfazer o maior número de consultas com o menor número de índices para máxima eficiência.
Alguns bons recursos:
Noções básicas de índice do SQL Server
Guia de design e arquitetura de índice do SQL Server
Design Básico e Diretrizes do Índice do SQL Server
Projetando índices não agrupados eficazes