故障现象：数据库响应突然变慢
在企业日常运维中，DBA或IT管理员经常遇到这样一种情况：原本运行流畅的SQL Server数据库，在业务高峰期或经过一段时间的运行后，某些特定查询突然变得极慢，甚至导致前端应用程序超时。此时，服务器的CPU使用率可能居高不下，但并非因为计算量巨大，而是因为大量的资源消耗在“编译”而非“执行”上。
这种现象的根本原因通常指向执行计划缓存溢出（Plan Cache Overflow）。当SQL Server实例的可用内存不足，或者缓存中存储了大量碎片化、低效的执行计划时，数据库引擎不得不频繁地将旧的计划从缓存中移除，并为新查询重新生成计划。这个过程被称为“重编译”，它会显著增加CPU负载并延长查询响应时间。
第一步：诊断执行计划缓存状态
要确认是否发生了缓存溢出，我们需要查看SQL Server当前的内存分布情况。请登录到SQL Server Management Studio (SSMS)，打开一个新的查询窗口，执行以下T-SQL脚本来获取缓存对象的统计信息：
诊断脚本示例：
- SELECT
count(*) AS [Total Plans],
sum(cast(size_in_bytes as bigint))/1024/1024 AS [Cache Size (MB)]
FROM sys.dm_exec_cached_plants;
上述脚本虽然简单，但更关键的诊断需要深入查看缓存中各类对象的占比。如果 objtype 为 Adhoc（即席查询）或 Prepared（预编译但未重用）的对象占据了大量内存，且总缓存大小接近服务器分配的内存上限，这通常是缓存溢出的强烈信号。
第二步：识别低效率的缓存对象
为了找出“罪魁祸首”，我们需要查询那些只执行一次却被缓存起来占用资源的SQL语句。执行以下查询可以列出缓存中大小最大且使用次数最少的执行计划：
- SELECT TOP 20
qs.execution_count,
(qs.total_worker_time/qs.execution_count) / 1000000.0 AS [Avg CPU Time (s)],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
如果列表中某条即席查询（Ad-hoc query）的使用次数仅为1，但占用的内存非常大，说明它是一次性编译后就被永久占用的“僵尸计划”。这不仅浪费了内存，还可能导致后续相似查询无法重用缓存，被迫再次编译。
第三步：立即缓解措施——清除特定缓存
在找到导致问题的特定SQL语句或存储过程后，如果需要立即恢复性能，可以尝试清除相关的缓存条目。请注意，DBCC FREEPROCCACHE 会清空整个服务器的计划缓存，导致所有查询暂时变慢直到重新编译，因此仅建议在紧急情况下或使用参数时谨慎操作。更安全的做法是清除特定计划的句柄。
操作指南：
- 获取Plan Handle：从上一步的查询结果中，复制出问题SQL对应的
plan_handle值。 - 执行清除命令：在SSMS中运行以下命令（将
[plan_handle]替换为实际值）：
DBCC FREEPROCCACHE ([plan_handle]);
此操作会将指定的执行计划从缓存中移除，迫使下一次相同查询重新编译。如果该查询被高频调用，建议立即进行优化，否则短期内容量会增加。但对于长期不再使用的即席查询，清除它们是释放内存的有效手段。
第四步：长期优化策略
解决缓存溢出不仅仅是清理缓存，更需要从架构和配置层面进行优化，以防止问题复发。
1. 启用“优化即席查询”选项
SQL Server提供了一个名为 optimize for ad hoc workloads 的服务器选项。启用后，当第一个即席查询到达时，SQL Server只会缓存一个轻量级的“存根”计划，而不是完整的执行计划。只有当相同的查询第二次被执行时，才会缓存完整的执行计划。这能显著减少即席查询对内存的浪费。
- 启用命令：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
2. 推广参数化查询
应用程序开发者应避免发送硬编码的SQL字符串。通过使用参数化查询（Parameterized Queries）或存储过程，确保相似的查询逻辑被视为同一个计划进行缓存。例如，将 SELECT * FROM Users WHERE ID = 1 改为使用参数 @ID，这样无论查询多少次不同的ID，都只使用一个执行计划。
3. 监控与维护
建立定期的监控机制，关注 sys.dm_os_memory_clerks 中 CACHESTORE_OBJCP 的大小变化。如果发现缓存持续增长且查询性能下降，应及时进行索引优化或重构低效SQL，而不是单纯依赖重启服务或清空缓存来维持表面正常。
总结
SQL Server执行计划缓存溢出是一个隐蔽但影响巨大的性能瓶颈。通过定期监控缓存使用情况、启用即席查询优化选项以及推动标准化的参数化编程实践，中小企业可以有效提升数据库的稳定性和响应速度，避免因内存资源争用导致的业务中断。
