几乎为空的计划缓存 [英] Almost empty plan cache

问题描述

我遇到了一个奇怪的情况 - 我的计划缓存几乎是空的.我使用以下查询来查看里面的内容:

SELECT dec.plan_handle,qs.sql_handle, dec.usecounts, dec.refcounts, dec.objtype, dec.cacheobjtype, des.dbid, des.text,deq.query_planFROM sys.dm_exec_cached_plans AS dec在 dec.plan_handle=qs.plan_handle 上加入 sys.dm_exec_query_stats AS qs交叉应用 sys.dm_exec_sql_text(dec.plan_handle) AS des交叉申请 sys.dm_exec_query_plan(dec.plan_handle) AS deqWHERE cacheobjtype = N'编译计划'AND objtype IN (N'Adhoc', N'Prepared')

它向我显示了 82 行，接下来是 50 行，然后是 40 行，然后是 55 行，依此类推，而在我无法到达计划缓存末尾的一个小时前，我发出了相同的命令.关键是 SQL Server 将计划缓存保持得非常小.我调查的主要原因是与我们的基线相比 CPU 高，没有任何高负载，在正常的白天工作负载下 - 持续 65-80%

Perfmon 计数器显示计划缓存命中率的低值 - 大约 30-50%，高编译 - 每秒 2000 个批处理请求中的 400 个和高 CPU - 平均 73.什么可能导致这种行为?

问题的主要目的是了解计划缓存为空的可能原因.

内存正常 - 最小值:0 最大值:245000.

我也没有注意到任何内存压力的迹象 - PLE、懒惰写入、空闲列表停止磁盘活动都很好，日志没有告诉我任何事情.我来这里是为了寻找可能的原因，以便继续调查.

我也考虑过这个线程:

I am experiencing a strange situation - my plan cache is almost empty. I use the following query to see what's inside:

SELECT dec.plan_handle,qs.sql_handle, dec.usecounts, dec.refcounts, dec.objtype
  , dec.cacheobjtype, des.dbid, des.text,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
join sys.dm_exec_query_stats AS qs on dec.plan_handle=qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE cacheobjtype = N'Compiled Plan'
AND objtype IN (N'Adhoc', N'Prepared')

One moment it shows me 82 rows, the next one 50, then 40 then 55 and so on while an hour before I couldn't reach the end of the plan cache issuing the same command. The point is that SQL Server keeps the plan cache very-very small. The main reason of my investigation is high CPU compared to our baselines without any high loads, under normal during-the day workload - constantly 65-80%

Perfmon counters show low values for Plan Cache Hit Ratio - around 30-50%, high compilations - 400 out of 2000 batch requests per second and high CPU - 73 avg. What could cause this behaviour?

The main purpose of the question is to learn the possible reasons for an empty plan cache.

Memory is OK - min: 0 max: 245000.

I also didn't notice any signs of memory pressure - PLE, lazy writes, free list stalls disk activity were just ok, logs did not tell me a thing. I came here for possible causes of this so I could proceed with investigation.

EDIT: I have also considered this thread:

SQL Server 2008 plan cache is almost always empty

But none of the recommendations/possible reasons are relevant.

解决方案

The main purpose of the question is to learn the possible reasons for an empty plan cache.

If it is to learn,the answer from Martin Smith,in the thread you referred will help you

If you want to know in particular,why plan is getting emptied,i recommend using extended events and try below extended event

这篇关于几乎为空的计划缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！