Como removo um plano inválido específico do cache de consultas do SQL Server?

Question

Temos uma consulta específica do SQL Server 2008 (não um procedimento armazenado, mas a mesma string SQL - executada a cada 5 minutos) que armazena de forma intermitente um plano de consulta muito ruim.

Essa consulta normalmente é executada em alguns milissegundos, mas, com esse plano de consulta incorreto, leva mais de 30 segundos.

Como faço para cirurgicamente remover apenas o plano de consulta ruim em cache do SQL Server 2008, sem explodir todo o cache de consulta no servidor de banco de dados de produção?

    

Answer 1

Eu descobri algumas coisas

select * from sys.dm_exec_query_stats

mostrará todos os planos de consulta em cache. Infelizmente, nenhum texto SQL é mostrado lá.

No entanto, você pode unir o texto SQL aos planos da seguinte forma:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

A partir daqui é bastante trivial adicionar uma cláusula WHERE para encontrar o SQL que eu sei que está na consulta e, em seguida, posso executar:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

para remover cada plano de consulta do cache do plano de consulta. Não é exatamente fácil ou conveniente, mas aparece para funcionar ..

edit: o cache de consultas inteiro também funciona e é menos perigoso do que parece, pelo menos na minha experiência:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

    

Answer 2

Se você souber como é o bom plano, use uma dica de plano .

Você não pode remover uma entrada de cache específica, mas pode limpar um pool de cache inteiro com DBCC FREESYSTEMCACHE(cachename/poolname) .

Você pode obter o nome do cache de um plano de consulta incorreto se tiver o identificador de plano (de sys. dm_exec_requests.plan_handle para o session_id com problemas durante a execução, ou de sys.dm_exec_query_stats pós-execução):

select ce.name
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce on cp.memory_object_address = ce.memory_object_address
where cp.plan_handle = @bad_plan

No entanto, todos os planos de SQL têm o nome 'SQL Plans', o que torna a escolha certa para a DBCC FREESYSTEMCACHE uma ... escolha difícil.

Atualizar

Deixa pra lá, esqueceu o DBCC FREEPROCCACHE(plan_handle) , sim que vai funcionar.

    

Answer 3

A solução FREEPROCCACHE é boa, mas uma maneira mais direta de fazer isso é usar OPTION (RECOMPILE) na sua String SQL (você mencionou que não era uma SP), isso diz ao Engine seu plano de Uso Único, porque provavelmente você suspeita que há o Parameter Sniffing ou suas Estatísticas são drasticamente diferentes de executar para rodar e você suspeita que é um problema com o Plano em Cache Ruim.

DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = 'SELECT * FROM Table WHERE Column LIKE @NAME OPTION (RECOMPILE)'
EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName'