如何动态读取多个存储过程的查询执行计划并保存至数据表

原查询失效原因 #

你的原有写法无法返回结果通常是以下几个原因导致：

• 目标存储过程从未被执行过，或执行计划已从缓存中老化、被手动清理
• 未限定当前数据库，跨库重名存储过程导致匹配错误
• 缺少VIEW SERVER STATE服务器级权限，无法读取动态管理视图内容
• 缓存条目存在无效的空计划内容

方案1：从计划缓存获取已缓存的预估执行计划 #

修正后的查询可以直接将匹配到的计划批量插入你的SPPlans表：

INSERT INTO SPPlans (SPNAME, QUERYPLAN)
SELECT
    OBJECT_NAME(QP.objectid, QP.dbid) AS SPNAME,
    qp.query_plan AS QUERYPLAN
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS SQLText
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) AS QP
WHERE 
    CP.objtype = 'Proc'
    AND OBJECT_NAME(QP.objectid, QP.dbid) IN ('Proc1', 'Proc2') -- 替换为你的存储过程名称列表
    AND QP.dbid = DB_ID() -- 限定当前数据库，避免跨库重名匹配
    AND qp.query_plan IS NOT NULL; -- 过滤无有效计划的缓存条目

该方案局限：仅能拿到已被缓存的计划，从未执行过、或缓存已失效的存储过程无法返回结果。

方案2：直接生成指定存储过程的预估执行计划（不依赖缓存） #

不需要依赖计划缓存，可主动生成任意存储过程的预估执行计划：

-- 定义要批量处理的存储过程列表
DECLARE @TargetSPs TABLE (ID INT IDENTITY(1,1), SPName VARCHAR(100))
INSERT INTO @TargetSPs (SPName) VALUES ('Proc1'),('Proc2'),('Proc3') -- 替换为你的存储过程名

DECLARE @LoopIdx INT = 1, @CurrSP VARCHAR(100), @ExecSQL NVARCHAR(MAX)
WHILE @LoopIdx <= (SELECT MAX(ID) FROM @TargetSPs)
BEGIN
    SELECT @CurrSP = SPName FROM @TargetSPs WHERE ID = @LoopIdx
    -- 开启预估计划输出
    SET @ExecSQL = N'
    SET SHOWPLAN_XML ON;
    EXEC ' + QUOTENAME(@CurrSP) + N';
    SET SHOWPLAN_XML OFF;
    '
    -- 执行并捕获返回的计划XML入库
    INSERT INTO SPPlans (SPNAME, QUERYPLAN)
    EXEC sp_executesql @ExecSQL

    SET @LoopIdx = @LoopIdx + 1
END

方案3：获取存储过程的实际执行计划（需真实执行存储过程） #

如果需要带实际运行行数、耗时、资源消耗的真实执行计划，使用以下方法：

-- 开启实际执行计划输出
SET STATISTICS XML ON;
GO
-- 执行存储过程，传入真实业务参数（有参数的存储过程需替换为实际参数值）
EXEC Proc1 @Param1 = 1, @Param2 = 'test';
GO
SET STATISTICS XML OFF;
GO

-- 将返回的实际计划XML插入目标表
INSERT INTO SPPlans (SPNAME, QUERYPLAN)
VALUES ('Proc1', /* 粘贴执行后返回的XML计划内容 */)

批量获取实际执行计划可以扩展上述循环逻辑，自动捕获STATISTICS XML返回的结果集插入SPPlans表即可。

注意事项 #

• 所有读取动态管理视图的操作都需要账号持有VIEW SERVER STATE服务器级权限
• 存储过程内包含的动态SQL片段的计划会单独缓存，上述方案均可捕获完整的批处理执行计划
• 同一个存储过程如果因为参数不同生成了多套缓存计划，方案1会返回所有匹配的计划版本

内容的提问来源于stack exchange，提问作者Jigar Parekh