Tenho notado o que considero problemas incomuns com nosso cache de plano de consulta, em que os planos no cache nunca tiveram mais de um dia.
Através da execução da consulta a seguir (cortesia de Kimberly Tripp ), ele mostrou que a maioria dos planos (4,5 Gb de planos em cache de 6 Gb ou 44813 de ~ 50000) eram consultas Ad-hoc que tinham uma contagem de uso de 1.
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
Eu determinei a consulta do problema (dinâmica, com um EXEC, é claro...), que é bastante hedionda, mas também complicada de apenas 'consertar', então eu estava procurando melhorias imediatas que poderiam ser feitas.
A instância já está configurada para usar o Optimize for Ad-hoc Workloads, no entanto, todos os CacheObjTypede sys.dm_exec_cached_planssão Plano Compilado em vez de Stub de Plano Compilado .
Ao usar o modo Ad-hoc Workload, os planos não devem ser Compiled Plan Stub até usecountsque sejam maiores que 1? Ou não é assim que funciona?
Há também um refcountscampo ao qual ninguém parece se referir ao falar sobre consultas Adhoc. O refcounts é sempre 1 quando o tipo é Compiled Plan Stub e 2 quando o tipo é Compiled Plan . Mesmo lendo BOL , não tenho certeza do que esse campo significa. Alguém pode esclarecer?
De acordo com isso , a segunda execução de um lote ad hoc remove o stub (que foi usado apenas uma vez) e cria e armazena em cache o plano (usando-o pela primeira vez).
Também não vi muitas referências
refcountsalém de ser uma contagem de referências por objetos de cache. Os objetos Adhoc Compiled Plan ainda podem ter umrefcountde 1, portanto, não é causado exclusivamente pela persistência do plano.