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.
O otimizador escolhe o plano com o spool lento devido à meta de linha definida usando a
TOPcláusula.Você está solicitando as primeiras 100 linhas - não o conjunto completo - para que o otimizador tente encontrar um plano que retorne 100 linhas rapidamente, em vez de um plano que retornaria todas as correspondências no menor tempo possível.
O raciocínio do otimizador é baseado em estatísticas e suposições de modelagem, que podem dar errado, como acontece aqui.
O carretel de mesa preguiçoso é um carretel de desempenho .
Tanto a meta de linha quanto o fator de spool de desempenho no custo estimado para o plano mostrado sendo calculado como mais barato.
Você pode desabilitar o spool de desempenho com a dica de consulta:
Você pode desabilitar a lógica de meta de linha com a dica de consulta:
Qualquer um fornecerá um plano mais parecido com o hash e top n sort que você mostrou por último, mas sem o modo de lote quando a compatibilidade estiver definida como 130.
A propósito, o desempenho do plano de spool lento depende muito do seu hardware, especialmente da memória e do desempenho do tempdb . Uma execução típica no meu laptop foi executada em 6,5 segundos:
Sem índices não clusterizados, ele é executado por 6,9 segundos:
Executado no Microsoft SQL Server 2019 CU15. Memória máxima definida para 4 GB, 12 processadores lógicos disponíveis.