Quer saber se os seus planos de execução que estão em cache no SQL Server estão sendo reutilizados?

Para visualizar a quantidade e porcentagem de planos de execução que estão sendo reutilizados basta executar a consulta abaixo:

DECLARE @singleExec BIGINT;
DECLARE @multipleExec BIGINT;
DECLARE @totalExec BIGINT;

SELECT @singleExec = COUNT(*)
FROM sys.dm_exec_query_stats s
WHERE execution_count = 1
SELECT @totalExec = COUNT(*)
FROM sys.dm_exec_query_stats s

SET @multipleExec = @totalExec - @singleExec

SELECT @totalExec AS totalExec, @multipleExec AS multipleExec, @singleExec AS singleExec
UNION ALL
SELECT 100 AS totalExec, ((@multipleExec * 100) / @totalExec) AS multipleExec, ((@singleExec * 100) / @totalExec) AS singleExec

Resultado:

Primeira linha: #

Segunda linha: %

totalExec: Total de planos de execução em cache.

multipleExec: Total de planos de execução em cache que foram reutilizados.

singleExec: Total de planos de execução em cache que foram utilizados apenas uma única vez.

Se este script foi útil para você avalie na galeria de scripts da TechNet por gentileza: https://gallery.technet.microsoft.com/SQL-Server-Get-Plan-Reuse-412de376

Obrigado! Espero que tenham gostado. Até a próxima!

Tudo está indo bem, o seu servidor de SQL está com baixa utilização de CPU, PLE alto, poucos índices fragmentados e as estatísticas atualizadas, quando de repente a utilização de CPU sobe para 100% e você nota que existem várias sessões de uma mesma aplicação executando a mesma consulta, que até então nunca tinha apresentado problema, demorando demais para retornar o resultado e estão bombardeando o SQL Server.

Respire fundo. Muita calma. Há uma saída.

Primeiramente você precisa do conteúdo do campo sql_handle ou plan_handle da consulta que está com problemas para remover o seu plano de execução do cache.

Para capturar o sql_handle ou plan_handle da consulta você pode utilizar apenas uma DMV e uma DMF: sys.dm_exec_query_stats e sys.dm_exec_sql_text. Você pode filtrar utilizando um trecho da consulta que você sabe que está com problemas e/ou até mesmo ordenar por um dos campos de total de utilização de CPU ou de tempo de duração, conforme exemplo abaixo:

SELECT TOP 10
execution_count,
total_elapsed_time / 1000 as totalDurationms,
total_worker_time / 1000 as totalCPUms,
total_logical_reads,
total_physical_reads,
t.text,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
WHERE t.text LIKE '%linq%'
ORDER BY total_elapsed_time DESC

Copie o conteúdo do campo plan_handle ou sql_handle da consulta exata que está com problemas.

Insira o conteúdo copiado dentro dos parênteses do comando DBCC FREEPROCCACHE, conforme exemplo abaixo:

DBCC FREEPROCCACHE (0x0600060020527003B0928B53CC01000001000000000000000000000000000000000000000000000000000000)

Pronto! O plano de execução da sua consulta foi removido com sucesso do cache do SQL Server e provavelmente o seu servidor irá diminuir a alta utilização de recursos e voltar a responder normalmente. Depois deste susto procure melhorar o desempenho da sua consulta e entender o que fez com que o plano de execução tenha sofrido alteração.

Observações:

1. Utilize este procedimento como uma solução de contorno e não como uma solução definitiva para o problema. Procure melhorar o desempenho da consulta e entender o que está fazendo com que o plano de execução sofra alteração ou tenha um desempenho ruim.
2. Nunca utilize o comando DBCC FREEPROCCACHE sem nenhum conteúdo dentro dos parênteses, principalmente em ambientes de produção, pois isto irá remover TODOS os planos de execução do cache.

É isso galera! Espero que tenham gostado. Até a próxima!