Estou analisando um pequeno problema de desempenho, em que uma ferramenta otimizadora está basicamente dizendo: "ei, vá em frente e inclua todas as colunas desta tabela neste índice", o que é uma solução horrível na minha opinião.
Meu processo de pensamento me levou a pensar "por que não vou em frente e dou tudo neste índice existente para fazer sua classificação e operação TOP(N) e cortar essa enorme operação de pesquisa de chave de 100k, certamente o SQL Server pode fazer isso e mude de pesquisas de chave de 100 mil para N."
Não foi isso que eu vi, o que vi foi que nada mudou, ele ainda fazia todas as principais pesquisas e classificava depois disso. Como abaixo
Simplesmente remover a seleção das outras colunas que não estão no índice faz com que ele não precise de nenhuma pesquisa de chave, é claro.
Já vi muitas soluções alternativas usando CTE para contornar isso, mas estou usando o Entity Framework para esta consulta e simplesmente brincar com a consulta não é tão fácil assim.
Eu gostaria que o objetivo principal desta pergunta fosse POR QUE isso acontece? Parece uma operação trivial fazer a cláusula sort e top antes do loop de pesquisa de chave, se você puder. Não fazer isso parece uma fraqueza gritante na plataforma.
Estou perguntando por que esse comportamento existe, e não como posso melhorar o desempenho dessa consulta.