Fundo
Eu tenho uma consulta em execução no SQL Server 2008 R2 que une e/ou une à esquerda cerca de 12 "tabelas" diferentes. O banco de dados é bastante grande, com muitas tabelas com mais de 50 milhões de linhas e cerca de 300 tabelas diferentes. É para uma grande empresa que tem 10 armazéns em todo o país. Todos os armazéns lêem e gravam no banco de dados. Então é bem grande e bem movimentado.
A consulta com a qual estou tendo problemas é algo assim:
select t1.something, t2.something, etc.
from Table1 t1
inner join Table2 t2 on t1.id = t2.t1id
left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
[etc]...
where t1.something = 123
Observe que uma das junções está em uma subconsulta não correlacionada.
O problema é que a partir desta manhã, sem nenhuma alteração (que eu ou alguém da minha equipe saiba) no sistema, a consulta que normalmente leva cerca de 2 minutos para ser executada, começou a levar uma hora e meia para ser executada - quando correu em tudo. O resto do banco de dados está funcionando bem. Eu tirei essa consulta do sproc em que ela geralmente é executada e a executei no SSMS com variáveis de parâmetro codificadas com a mesma lentidão.
A estranheza é que, quando eu pego a subconsulta não correlacionada e a coloco em uma tabela temporária e, em seguida, uso isso em vez da subconsulta, a consulta funciona bem. Além disso (e isso é o mais estranho para mim) se eu adicionar este pedaço de código ao final da consulta, a consulta funciona muito bem:
and t.name like '%'
Concluí (talvez incorretamente) com esses pequenos experimentos que o motivo da lentidão é devido à forma como o plano de execução em cache do SQL é configurado - quando a consulta é um pouco diferente, é necessário criar um novo plano de execução.
Minha pergunta é esta: quando uma consulta que costumava ser rápida de repente começa a ser executada lentamente no meio da noite e nada mais é afetado, exceto esta consulta, como faço para solucioná-la e como evitar que isso aconteça no futuro ? Como sei o que o SQL está fazendo internamente para torná-lo tão lento (se a consulta incorreta fosse executada, eu poderia obter seu plano de execução, mas não será executado - talvez o plano de execução esperado me dê algo?)? Se esse problema for com o plano de execução, como evitar que o SQL pense que planos de execução realmente ruins são uma boa ideia?
Além disso, isso não é um problema com o sniffing de parâmetros. Eu já vi isso antes, e não é isso, pois mesmo quando codifico as variáveis no SSMS, ainda obtenho um desempenho lento.
Você pode começar verificando se o plano de execução ainda está no cache. Verifique
sys.dm_exec_query_stats,sys.dm_exec_procedure_statsesys.dm_exec_cached_plans. Se o plano de execução ruim ainda estiver armazenado em cache, você poderá analisá-lo e também verificar as estatísticas de execução. As estatísticas de execução conterão informações como leituras lógicas, tempo de CPU e tempo de execução. Estes podem fornecer fortes indicações de qual é o problema (por exemplo, varredura grande versus bloqueio). Consulte Identificando consultas de problemas para obter uma explicação de como interpretar os dados.Não estou convencido. Variáveis codificadas no SSMS não provam que o plano de execução ruim anterior não foi compilado em uma entrada distorcida. Por favor, leia Parameter Sniffing, Embedding e as opções RECOMPILE para um artigo muito bom sobre o assunto. Lento no aplicativo, rápido no SSMS? Compreender os mistérios do desempenho é outra excelente referência.
Isso pode ser facilmente testado.
SET STATISTICS TIME ONirá mostrar-lhe o tempo de compilação vs. execução. Os contadores de desempenho do SQL Server:Statistics também revelarão se a compilação é um problema (francamente, acho improvável).No entanto, há algo semelhante que você pode encontrar: o portão de concessão de consulta. Leia Entendendo a concessão de memória do SQL Server para obter detalhes. Se sua consulta solicitar uma grande concessão em um momento em que nenhuma memória estiver disponível, ela terá que esperar e tudo parecerá 'execução lenta' para o aplicativo. A análise das estatísticas de informações de espera revelará se esse for o caso.
Para uma discussão mais geral sobre o que medir e o que procurar, consulte Como analisar o desempenho do SQL Server
Esta é uma ruína de executar consultas complexas no SQL Server. Felizmente, isso não acontece com tanta frequência.
Observe o plano de consulta para a consulta (quando estiver lento). Eu estou supondo que você encontrará uma junção de loop aninhada ocorrendo uma ou mais vezes em tabelas sem índices para a junção. Isso realmente retarda as coisas. Para avançar, a maneira de corrigir isso é com uma dica. Adicione o seguinte no final da consulta:
Isso geralmente corrigiu esse problema para mim no passado.
O que pode estar acontecendo é que mudanças sutis na tabela (ou na disponibilidade de espaço temporário) fazem com que a otimização SQL prefira um algoritmo de junção mais lento. Isso pode ser bastante sutil e bastante repentino. Quando você cria uma tabela temporária, o otimizador tem mais informações sobre a tabela (como seu tamanho), para que possa gerar um plano melhor.
Normalmente, é um índice ausente que causa esse tipo de problema.
O que costumo fazer é executar a consulta usando o SQL Management Studio e habilitar 'Incluir Plano de Execução Real (CTRL+M)' e descobrir qual junção está tendo a maior porcentagem.
O aplicativo não foca no gargalo, mas você pode encontrá-lo "rapidamente" apenas olhando o resultado.
exemplo aqui:
Recentemente, experimentei esse mesmo problema que me trouxe a esta página.
@MartinSmith estava certo quando recomendou atualizar suas estatísticas e explicar o plano. Gostaria de acrescentar que você também deve tentar garantir que dê uma olhada nos trabalhos/consultas em execução que podem criar bloqueios e, assim, diminuir o tempo de resposta.
No meu caso, o culpado foi o trabalho de coleta de estatísticas da tabela. Por algum motivo, ele não foi concluído na janela que deveria e continuou em execução quando os usuários retomaram. Encontrei o processo, matei-o e as consultas começaram a responder novamente.
Espero que isto ajude alguém
Você também precisa verificar se algum backup de servidor ou qualquer trabalho de arquivamento\indexação está em execução quando você vê um problema de desempenho em T-SQL\Procedure.