Eu estava testando no banco de dados Stackoverflow para descobrir possíveis casos em que o SQL Server não recomenda o índice no plano de execução, mas se introduzirmos um, ajudaria muito!
Fiz isso facilmente para Group by, Order by Clause e para função de agregação (função de contagem - menor cópia da tabela). Eu escrevi uma consulta aleatória em que eu sabia que a introdução do índice de suporte certamente ajudará, no entanto, a recomendação de índice ausente será apenas na condição de junção e não na cláusula order by.
A consulta é como abaixo:
select top 100 Location from Users U join Badges B
on B.UserId = U.Id
order by Location desc
Os índices abaixo foram introduzidos para melhorar o desempenho:
create index Location on Users(Location)
go
create index UsersId on Badges(UserId)
go
Índices usados pelo otimizador conforme esperado para a consulta acima:
As leituras lógicas e as estatísticas de tempo são as seguintes:
Agora, eu queria testar o desempenho apenas com o índice na tabela Users na coluna Location e sem Index on Badges (UserId), aqui o desempenho fica terrível (leva quase 7 minutos):
As leituras lógicas e as estatísticas de tempo são as seguintes:
As tabelas de índice em usuários são muito usadas, como é evidente no plano de execução e nas leituras lógicas, no entanto, fazer a varredura de índice clusterizado e o Spool de tabela (Spool preguiçoso) causam a maior parte do problema.
Todos os testes acima são realizados no SQL Server 2019 no modo de compatibilidade do SQL Server 2016(130).
Se alguém puder aconselhar sobre o problema subjacente, seria de grande ajuda.
Mais uma coisa a ser observada aqui, quando não há índice de suporte não clusterizado em nenhuma dessas duas tabelas, a mesma consulta termina em 9 segundos. Segue abaixo o plano de execução:
Estatísticas lógicas de leitura e tempo:
Para fins de teste, alterei o nível de compatibilidade para 2019(150) e para minha surpresa - mesma consulta anterior que tinha índice apenas na tabela Users (Location) e não na tabela Badges, finalizada em 2 segundos que estava demorando 7 minutos no SQL Server 2016 modo de compatibilidade(130):
Estatísticas lógicas e estatísticas de tempo:
No modo de compatibilidade de 2019, todos os operadores antes do Paralelismo estão em modo de lote .
Qualquer entrada a esse respeito me ajudaria a entender esse comportamento.
