How to get parameter values for dm_exec_sql_text

Question

I'm running the following statement to see what queries are executing in sql server:

select *
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.database_id = DB_ID('<dbname>')

The sql text that comes back is parameterized:

(@Parm0 int) select * from foo where foo_id = @Parm0

Is there any way to get the values for the parameters that the statement is using? Say by joining to another table perhaps?

Answer 1

If you are using a version prior to SQL Server 2012 or Amazon RDS where the TRY_CONVERT function does not exist, this is a variation of the Indigo query that will work.

-- cleanup
IF OBJECT_ID('tempdb..#compiledValue') IS NOT NULL
    DROP TABLE #compiledValue
GO

-- Prepare temp table #compiledValue
SELECT  
    OBJECT_NAME(est.objectid) ObjectName,
    DB_NAME(est.dbid) DBName,
    eqs.last_execution_time,
    est.text,
    (eqs.statement_start_offset / 2) + 1 AS statement_start_offset,
    (IIF(eqs.statement_end_offset = -1, DATALENGTH(est.text), eqs.statement_end_offset) - eqs.statement_start_offset) / 2 + 1 AS statement_end_offset,
    --TRY_CONVERT(XML, 
    --    SUBSTRING(etqp.query_plan,
    --        CHARINDEX('<ParameterList>',etqp.query_plan),
    --        CHARINDEX('</ParameterList>',etqp.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>',etqp.query_plan) )) AS statement_params,
    CASE
        WHEN CHARINDEX('<ParameterList>', etqp.query_plan) > 0
            THEN CONVERT(XML, 
                    SUBSTRING(etqp.query_plan, 
                        CHARINDEX('<ParameterList>', etqp.query_plan), 
                        CHARINDEX('</ParameterList>', etqp.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>', etqp.query_plan) )) 
        ELSE NULL
    END AS statement_params
INTO #compiledValue
FROM sys.dm_exec_query_stats eqs
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, eqs.statement_start_offset, eqs.statement_end_offset) etqp

SELECT 
    cvalue.last_execution_time,
    cvalue.DBName,
    cvalue.ObjectName,
    SUBSTRING(cvalue.text,cvalue.statement_start_offset,cvalue.statement_end_offset) AS sql_text,
    pc.compiled.value('@Column', 'nvarchar(128)') AS Parameterlist,
    pc.compiled.value('@ParameterCompiledValue', 'nvarchar(128)') AS [compiled Value]
FROM #compiledValue cvalue
OUTER APPLY cvalue.statement_params.nodes('//ParameterList/ColumnReference') AS pc(compiled)
WHERE cvalue.text NOT LIKE '%#compiledValue%' -- ignore these queries based on temp table name
ORDER BY cvalue.last_execution_time DESC
GO

-- cleanup
DROP TABLE #compiledValue
GO