In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

by user420667   Last Updated July 26, 2017 11:06 AM

One way to determine the executing stored procedure is to use "dynamic management" methods, like so:

SELECT 
    sqlText.Text, req.* 
FROM 
    sys.dm_exec_requests req
OUTER APPLY 
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

However, this only displays the text of the stored procedure's create statement. e.g.:

CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id

Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters.

Is there a way to do that? (In this question Aaron Bertrand mentions DBCC InputBuffer, but I don't think that's appropriate for this problem.)



Answers 3


You can turn on the actual execution plan and then look at the execution plan XML.

enter image description here

Or you can use sql sentry's plan explorer tool and see the parameters tab that will list the compiled value and run time value for actual execution plan.

If you cannot turn on the actual plan then you can look into plan cache as described below.

-- borrowed from  Erland Sommarskog
-- Link : http://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans
-- Remember that you are looking at the estimated plan so the actual no. of rows and actual executions wont be there ! <-- Important why a particular plan is bad.

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'Northwind',  -- Your DB name
       @procname = 'dbo.List_orders_11' -- The SP that you want to get parameters for !

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter
Kin
Kin
June 28, 2016 18:18 PM

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such as: RPC:Completed, SP:Completed, and SQL:BatchCompleted. You also need to select the "TextData" field as the values will be in there.

The difference between my answer and @Kin's answer on this Question is that @Kin's answer (unless I am mistaken, in which case I will remove this) focuses on getting either:

  • your own query plan (in which case it can have the runtime parameter info in it, but not for other Sessions/SPIDs), or
  • plans from the DMVs (in which case they should only have the compiled parameter values, which are not runtime values).

My answer focuses on getting the parameter values for other sessions that are currently running. When relying on the DMVs, there is no way to know if the runtime parameter value is the same as the compiled parameter value. And the context of this question is tracking down the runtime value of queries being submitted via other Sessions/SPIDs (and in SQL Server 2005, whereas Extended Events were introduced in SQL Server 2008).

Solomon Rutzky
Solomon Rutzky
June 28, 2016 19:02 PM

I do it like this:

EXEC sp_whoisactive @get_plans=1, @output_column_list='[login_name][dd%][session_id][program%][sql_com%][sql_text][block%][reads][writes][physical_reads][query_plan][used_memory][tempdb%][wait%][collection_time][host%][additional%]', @get_outer_command=1, @get_additional_info=1
--, @help=1
--, @filter_type='program' ,@filter='%myprogram%'

the column sql_command shows the parameters that was used. You can get sp_whoisactive here.

edit: This requires that the plan is not in cache and is being re-used for the current call. The ensure this is the case, you can call your sproc like this

exec MySproc @i=42 with (option recompile)
Henrik Staun Poulsen
Henrik Staun Poulsen
July 01, 2016 17:30 PM

Related Questions



Shrink my .mdf file in SQL Server?

Updated March 26, 2015 08:02 AM

Unable to open SQL Server database file (.mdf)

Updated April 24, 2015 21:02 PM


What transaction logs are for

Updated July 03, 2015 13:02 PM