在数据库日常运转中，存储过程本应凭借预编译的查询计划提升执行效率，但不少系统跑久了反而变慢，根子常常出在计划缓存没有被有效复用。所谓存储过程缓存，是指数据库引擎把编译后的执行计划暂存在内存里，下次用相同签名调用时直接取用，跳过解析与编译阶段。如果缓存命中率低，CPU就会反复做无用功。本文围绕如何优化SQL存储过程缓存，以及怎样分析缓存命中率给出可落地的策略。
一、理解存储过程缓存的基本机制
以 SQL Server 为例，当首次执行一个存储过程时，引擎会进行语法检查、语义解析、生成执行计划，并把该计划存入计划缓存（Plan Cache）。后续调用若签名一致，便直接复用内存中的计划，这便是缓存命中。签名主要包含存储过程名、参数类型和调用上下文，哪怕传入的具体值不同，只要参数结构相同，通常仍能命中。
可以通过系统动态管理视图观察缓存内容。下面的查询列出当前缓存中类型为存储过程的条目，以及它们被复用的情况：
SELECT
objtype,
usecounts,
size_in_bytes,
text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Proc'
ORDER BY usecounts DESC;
在结果里，usecounts 代表该计划被复用的次数。如果某个核心存储过程的 usecounts 长期为 1，说明它几乎每次都在重编译，缓存优化空间很大。与之相对，高 usecounts 且 size_in_bytes 合理的项，才是缓存健康的表现。
二、分析缓存命中率的实操策略
单纯看 usecounts 还不够直观，我们需要从整体视角计算命中率。一种常见做法是基于 sys.dm_exec_query_stats 汇总编译与执行次数，估算计划复用比例。下面的示例统计最近阶段内存储过程相关的编译与执行规模：
SELECT
SUM(qs.execution_count) AS total_executions,
COUNT(DISTINCT qs.plan_handle) AS distinct_plans,
CAST(
SUM(qs.execution_count) * 1.0 /
NULLIF(COUNT(DISTINCT qs.plan_handle), 0)
AS DECIMAL(10,2)
) AS avg_reuse_ratio
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle = cp.plan_handle
WHERE cp.objtype = 'Proc';
上述 avg_reuse_ratio 近似反映每个计划被平均执行多少次，数值越高代表命中情况越好。若比值接近 1，意味着几乎每次执行都伴随新计划生成。此时应排查是否频繁使用了非参数化调用，或在过程内拼接动态 SQL 导致签名漂移。
另一个实用策略是定位低命中率的单点过程。我们可以按执行次数与计划复用次数反差排序，把那些被大量调用却没积累复用的过程揪出来，作为优化候选：
SELECT
OBJECT_NAME(qt.objectid) AS proc_name,
qs.execution_count,
cp.usecounts,
qt.text
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE cp.objtype = 'Proc'
AND cp.usecounts < 5
AND qs.execution_count > 100
ORDER BY qs.execution_count DESC;
这类查询能直接暴露问题过程。比如一个每天调用上万次但 usecounts 小于 5 的报表过程，显然没有享受缓存红利。拿到名单后，再结合过程定义做参数化改造即可。
三、优化存储过程缓存的具体手段
第一，统一参数类型与长度。很多开发者在 ADO.NET 或 JDBC 调用时，未显式指定参数长度，导致数据库收到 nvarchar(4000) 之类的默认类型，与过程定义的 nvarchar(50) 不匹配，从而无法复用计划。应在客户端明确指定参数规格，例如：
using (var cmd = new SqlCommand("dbo.GetUser", conn)) {
cmd.CommandType = CommandType.StoredProcedure;
var p = new SqlParameter("@name", SqlDbType.NVarChar, 50);
p.Value = "test";
cmd.Parameters.Add(p);
cmd.ExecuteReader();
}
第二，避免过程内拼接带字面量的动态 SQL。如下写法每次字符串不同都会产生新签名，缓存必然失效：
CREATE PROCEDURE dbo.BadProc @id INT
AS
BEGIN
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Orders WHERE Id = ' + CAST(@id AS NVARCHAR);
EXEC(@sql);
END
应改为参数化动态 SQL，或使用静态语句加条件判断，让计划保持稳定。改写后：
CREATE PROCEDURE dbo.GoodProc @id INT
AS
BEGIN
SELECT * FROM Orders WHERE Id = @id;
END
第三，定期清理老化缓存。虽然 SQL Server 会自行淘汰，但在测试或批量发布后，可手动释放被污染的计划：使用 DBCC FREEPROCCACHE 需谨慎，生产环境建议仅清理特定计划句柄。日常更推荐通过更新统计信息、规范发布流程来减少计划碎片。
四、监控与长期维护建议
把缓存命中率分析做成定时任务，每周输出低复用过程清单，能防止问题堆积。可把前述查询包装进一个监控存储过程，由作业调度执行并记录到日志表。同时，开启查询存储（Query Store）功能，能在计划回归时快速定位并强制良好计划。
此外，应用层应尽量使用同一连接池与统一参数约定，减少跨会话的计划不匹配。当发现某过程命中率突降，优先检查近期是否改过表结构、索引或参数默认值，这些都会让旧计划失效。把缓存策略纳入发布检查单，系统才能长期保持轻快。
综上，优化 SQL 存储过程缓存的核心不在于建了多少过程，而在于计划是否真的被复用。从命中率量化入手，消除参数不一致与动态拼接，再辅以持续监控，便能把编译开销压到最低，让数据库资源花在真正的数据处理上。