j d (imported from SE)
Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored procedure.)

I know I can see the queries of the stored procedure that are currently being executed using the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs but they don't show the values of the parameters being used.
Top Answer
Martin Smith (imported from SE)
For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing `query_thread_profile` (and be on [at least  SQL Server 2016 SP1][1]) but then can use

    WITH XMLNAMESPACES
       (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    select query_plan.query('//ParameterList')
    from sys.dm_exec_query_statistics_xml(@session_id)

and see the `ParameterRuntimeValue` and `ParameterCompiledValue` from the execution plan of the statement being executed. 

For SQL Server 2019+ the infrastructure is enabled by default so the above will just work. 

This might not be all the parameters of the stored proc however as it will only include params used by the execution plan.


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-statistics-xml-transact-sql?view=sql-server-ver15

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.