sql-server - 是什么不断清除我的 SQL Server 计划缓存？

Question

我们最近遇到了一些性能问题，因此我一直在更密切地监控我们的事件。我一直在运行 AskBrent 脚本并得到报告“有人运行 DBCC FREEPROCCACHE”，因为计划缓存非常年轻。使用 dm_exec_query_stats 进一步深入研究，我发现计划缓存每隔几秒就会频繁清空。我在服务器上运行了跟踪，并且在同一时间段内没有运行任何 DBCC 命令。

有人知道还有什么可能导致计划缓存如此频繁地清空吗？

该数据库支持生产中的许多繁忙网站。在我们的暂存环境(运行相同的代码)中，计划缓存已经有几天了。在我们的开发环境中，缓存存在几分钟或几个小时的时间，这并不奇怪。我们在集群中运行 SQL Server 2008 R2 Standard。

Answer 1

@Raspin:一种可能性是服务器内存有限，并且有很多“不同的”SQL 计划(未参数化或无法自动参数化的查询的执行)。

可能有太多"new"计划，而 SQL Server 正在刷新“旧”计划。 (除 DBCC FREEPROCCACHE 之外，还有其他操作会导致计划被刷新。如果存在内存压力，则未重用的临时计划将是第一个从缓存中逐出的计划。)

我推荐此 Microsoft 白皮书进行相关讨论:

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

我不确定“集群”如何影响计划缓存；我相信每个 SQL Server 实例都有自己的计划缓存。

<小时/>

无法“自动参数化”的语句在白皮书的附录A中列出。

我的猜测(只是猜测)是“不同”SQL 语句的数量淹没了计划缓存。我敢说很多语句永远不会被重复使用。

在我的服务器上，我会使用 dm_exec_cached_plans 来收集缓存中计划的一些快照 [ http://technet.microsoft.com/en-us/library/ms187404(v=sql.105).aspx]在很短的时间间隔内，并比较快照。

条目数量是否保持相对恒定，缓存是否已满，或者是否在某个时刻真正被“清除”。计划缓存中的任何语句是否被重用？ (我认为输出包含一个“usecounts”列，表明语句正在重新执行。)

我还会收集短时间间隔内执行的 SQL 语句的跟踪信息，并进行检查。大多数语句是参数化的还是自动参数化的？或者他们中的大多数都是带有文字的单例。

SELECT *
  FROM sys.dm_exec_cached_plans
 WHERE cacheobjtype = 'Compiled Plan'
 ORDER BY objtype

大多数计划都是 objtype = 'Adhoc' 吗？如果计划不被重用，那么当存在内存压力时，这些计划最有可能被驱逐。

<小时/>

性能问题真的是计划被从缓存中“清除”了，还是真的有其他问题，比如 SQL 语句的绝对数量、SQL 语句本身的性能、锁的争用、闩锁、空闲缓冲区等。

<小时/>