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.