0

I have been trying to figure out how to scan a stored procs parameters from the execution plan. I often see most people suggest using the ParameterList within the execution plan xml. This works fine when there is just one statement that uses all of the parameters. However this is often not the case. The stored procedure parameters could be distributed over several queries or not used in a query at all.

In my examples below, I would like to know how to get the distinct parameters and their values from the example using sql xml functions. Also, you will notice that not all of the parameters are output into the ParameterLists.

/***********************************************************************/
/*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
  • What do you mean, " and their values "? Default values? Passed in parameter values? – Sean Gallardy Aug 22 '17 at 19:20
  • So you are wanting to identify each T-SQL statement that uses a specific parameter, and what that specific parameter's value is? Your question is not very specific. You might want to add the expected output. – Max Vernon Aug 22 '17 at 21:40
  • 1
    What everyone is saying is that the actual execution plan is different then the cached execution plan. Only SQL server 2016 and above will show you the actual values passed through via DMV views .so you might also wish to specify the Version of SQL server. Any other value see is the parameters sniffing value at the cached plan is based on – clifton_h Aug 22 '17 at 22:59
  • Look at these answers, might help you depending on what exactly you are looking for. dba.stackexchange.com/questions/183106/… – SqlWorldWide Aug 23 '17 at 1:46
  • I am trying to extract the parameters and the values that were originally cached into the plan at execution time. However it appears that there is no global ParameterList for a stored procedure. The ParameterLists are maintained on each statement in the stored procedure. Also, if the parameter is not used in a query, then that value passed in is lost. All I am wanting is to get the unique parameters passed into the plan at creation time, and their cached values. However after a lot of digging into this I do not believe that is 100% achievable. – SpaceGhost440 Aug 23 '17 at 15:58
0

Ok, after much digging into this I have determined this is not often possible. I will detail the following scenarios that break down the ability to get the original parameters from the execution plan.

  • If the parameter is not used in a where clause. Scenarios:
    • The parameter is placed into a local variable as a way to work around parameter sniffing
    • The parameter is used in a calculation in a local variable
  • If the parameter is used in a where clause but a function is used upon it
  • If the parameter is used in multiple sql statements in the stored proc it will appear in multiple parameter lists.

I just wanted a list of all of the original parameters passed into the stored proc and their values from the original plan creation so that I could recreate the call if need be. I know that I could obtain this information using extended events or profiler but I was trying to extract it from the query plans.

0

This is as close as I got to extracting the parameters and their values out of the plan. However, this query fails to get the parameters 100% of the time because of the reasons listed above.

EDIT: Removed previous posted query. This version groups all the parameters, and finds them at any various level in the plan. This is as close to what I originally wanted. Also ignores whether the parameters are in a StmtSimple or a StmtCond:

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qs.[sql_handle], qt.[text], qp.[query_plan], [ParamXml] = CAST(fnParams.[ParamXml] AS xml)
FROM sys.[dm_exec_procedure_stats] qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
OUTER APPLY (
    SELECT [ParamXml] = (
        SELECT [@Parameter] = t1.[Column], [@Value] = t1.[Value] 
        FROM (
            SELECT [Column] =  CAST(stmts.stmt.value('@Column', 'varchar(8000)') AS varchar(8000)),  
                [Value] = CAST(stmts.stmt.value('@ParameterCompiledValue', 'varchar(8000)') AS varchar(8000))
            FROM qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//ParameterList/ColumnReference') AS stmts(stmt)
            WHERE stmts.stmt.value('count(.)', 'int') > 0 
        ) t1 
        GROUP BY t1.[Column], t1.[Value]
        FOR XML PATH('Parameter'), ROOT('Parameters')
    )
) fnParams

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.