Eu tenho uma instância do SQL Server 2014 (12.0.2000.8) e uma instrução SELECT bastante complexa com cerca de 20 junções. Essa consulta funciona bem com o mesmo conjunto de dados no PostgreSQL, Oracle e outros bancos de dados, e a execução inteira leva cerca de 1 minuto.
Mas no SQL Server leva cerca de 40 minutos. Tentei ver o plano de execução e comecei a esperar... Tentei obter o plano de execução executando uma consulta de uma sessão do aplicativo, mas não havia plano de execução.
Então recebi a consulta e pedi no SQL Server Management Studio por "Exibir plano de execução estimado", e também comecei a esperar. Então, parece que leva muito tempo apenas para construir o plano de execução. Todas as estatísticas são reunidas com "exec sp_updatestats", e eu verifiquei em sys.stats - tudo parece bem. Todos os índices estão em vigor.
Comentei todas as junções e comecei a descomentá-las uma a uma, e
SET STATISTICS TIME ON
mostra que com cada junção não comentada leva mais tempo para analisar, por exemplo, tempo para 13 junções:
SQL Server parse and compile time:
CPU time = 32250 ms, elapsed time = 32729 ms.
então, é definitivamente um problema de análise.
select count(*) from sys.index_columns
where object_id in (OBJECT_ID('tables_names'),...')
diz que há 128 colunas, quando
select * from sys.indexes
where object_id in (OBJECT_ID('tables_names'),...')
retorna 43 linhas com índices HEAP, CLUSTERED, NONCLUSTERED.
Você poderia recomendar o que olhar? Por que analisa tanto?
Atualização: Obrigado por "Quebrar a consulta" e "Usar uma dica FORCE ORDER", mas esse SQL é gerado pelo nosso aplicativo, portanto, pode ser muito trabalhoso torná-lo possível com a lógica do aplicativo, mas em casos gerais devem ser uma ótima solução.
Segunda atualização: a aplicação do SP3 fez tudo - toda a consulta de execução leva menos de um segundo. O desempenho melhorou duas mil vezes :)
Versão RTM? Lembro-me de ter uma duração excessiva de compilação (minutos) para algumas consultas. O problema foi corrigido após o RTM. Sugiro que você corrija seu servidor para um nível de patch compatível (SP3+).
Espero que a maior parte do tempo de compilação seja gasto pelo otimizador considerando diferentes abordagens para reordenar as muitas junções.
Duas opções para corrigir isso são:
Divida a consulta
Uma abordagem que pode funcionar, dependendo da sua consulta, é separar a parte relacional da informativa da sua consulta. Isso envolve dividir a consulta em duas partes, para que cada parte tenha menos junções (e, portanto, menos complexidade para o otimizador explorar).
A abordagem é essencialmente pegar as coisas que filtram linhas ou fornecer a lógica de sua consulta (colunas envolvidas em cláusulas where, junções internas, etc.) e executar apenas essa consulta, inserindo-a em uma tabela temporária.
Em seguida, adicione o restante das coisas "informativas" ou relacionadas à exibição em uma consulta separada que está apenas se juntando à tabela temporária.
Eu ouvi sobre isso pela primeira vez de Erik Darling, veja aqui um ótimo exemplo: Informativo vs. Relacional
Observe que isso se trata principalmente de evitar resultados e índices amplos, mas pode ser eficaz para compilação se você conseguir eliminar completamente algumas das junções na primeira consulta.
Use uma dica FORCE ORDER
Adicionar
OPTION (FORCE ORDER)ao final da consulta deve limitar o tempo de compilação, embora você possa ter que experimentar a ordem escrita das junções para obter um plano de execução razoável (e isso pode mudar com o tempo à medida que seus dados ou esquema mudam).Correndo o risco de parecer um fã de Erik Darling Data, este post é relevante para sua situação e vale a pena ler: Longos tempos de compilação estão deixando você para baixo?
Essa postagem discute a
FORCE ORDERabordagem para resolver isso (em particular, usando um guia de plano, já que a consulta do problema foi gerada pelo EF e, portanto, a dica não pôde ser adicionada facilmente na fonte).