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.
TopAnswers
Databases
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