CODEWITHSUNDEEP
asp.nett-sqlsql-server-2014
CaptainMarvel
CaptainMarvel

Reputation: 427

How can I get a list of recent queries with parameter values, not names?

I am aware of some ways for seeing the recent queries for the current database, such as:

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID()
ORDER BY deqs.last_execution_time DESC

My problem is that the results aren't showing the values being passed to the PROC, but are instead showing the names of the parameters. I already know the name of the PROC and I have the script. I need to know what a particular ASP app is passing in.

For now I just have the PROC INSERTing the param values into a logging table, but I was wondering if there was a better way.

Upvotes: 0

Views: 355

Answers (2)

Lopofsky
Lopofsky

Reputation: 528

I'm using this one, but recently I've found queries that despite having parameters, the ParameterList column is empty:

WITH XMLNAMESPACES(DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    t.[text] AS OriginalQueryText,
    s.last_execution_time,
    SUBSTRING(t.[text], (s.statement_start_offset/2)+1,
        ((CASE s.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.[text])
            ELSE s.statement_end_offset
        END - s.statement_start_offset)/2) + 1) AS StatementText,
    (
        SELECT 
            ColumnReference.value('@Column', 'nvarchar(128)') as param_name,
            ColumnReference.value('@ParameterCompiledValue', 'nvarchar(max)') as param_value
        FROM plan_xml.query_plan.nodes('//ParameterList/ColumnReference') as nodes(ColumnReference)
        FOR XML PATH('param'), ROOT('params')
    ) as ParameterList
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
    ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp
OUTER APPLY (SELECT CAST(qp.query_plan AS XML)) as plan_xml(query_plan)
WHERE t.[text] IS NOT NULL
ORDER BY s.last_execution_time DESC

Upvotes: 0

Mahendra
Mahendra

Reputation: 51

Try to use custom logging in the stored procedure. Whenever the stored procedure is invoked, log an entry into the log table with the passed in params.

Upvotes: 0

Related Questions