我一直在试图弄清楚如何从执行计划中扫描存储的过程参数。我经常看到大多数人建议在执行计划 xml 中使用 ParameterList。当只有一个语句使用所有参数时,这很好用。然而,通常情况并非如此。存储过程参数可以分布在多个查询中,或者根本不在查询中使用。
在下面的示例中,我想知道如何使用 sql xml 函数从示例中获取不同的参数及其值。此外,您会注意到并非所有参数都输出到参数列表中。
/***********************************************************************/
/*EXAMPLE: you have to scan all of the selects parameter lists to match up the parameter names to the proc parameters */
/***********************************************************************/
IF OBJECT_ID (N'dbo.TestFooProc') IS NULL
EXEC('CREATE PROCEDURE dbo.TestFooProc AS SET NOCOUNT ON;');
GO
ALTER PROCEDURE dbo.TestFooProc (
@p1 int,
@p2 int,
@p3 int = 8675309
) AS
BEGIN
--distribute the parameters over multiple selects
SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p2;
-- other simulated queries ...
SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p1;
END
GO
EXEC dbo.TestFooProc @p1 = 5, @p2 = 22
SELECT [db_name] = DB_NAME([eps].[database_id]), qt.[text], qp.[query_plan]
FROM sys.[dm_exec_procedure_stats] eps
CROSS APPLY sys.[dm_exec_sql_text](eps.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) AS qp
WHERE [eps].[database_id] = DB_ID()
AND qt.[text] LIKE '%dbo.TestFooProc%'
RETURN -- CLEANUP
IF OBJECT_ID (N'dbo.TestFooProc') IS NOT NULL
DROP PROCEDURE dbo.TestFooProc;
GO
好吧,在深入研究之后,我确定这通常是不可能的。我将详细介绍以下场景,这些场景分解了从执行计划中获取原始参数的能力。
我只想要一个传递给存储过程的所有原始参数的列表,以及它们在原始计划创建中的值,以便我可以在需要时重新创建调用。我知道我可以使用扩展事件或探查器获取此信息,但我试图从查询计划中提取它。
这与我从计划中提取参数及其值一样接近。但是,由于上面列出的原因,此查询在 100% 的时间内无法获取参数。
编辑:删除了以前发布的查询。此版本将所有参数分组,并在计划中的任何不同级别找到它们。这和我最初想要的很接近。也忽略参数是在 StmtSimple 还是 StmtCond 中: