Recover parameters for stored procedure from system logs

Question

I'm trying to debug an app, and I'd like to rule out database errors before tackling the code.

I know which stored procedure is responsible to handling the data I'm looking at, but there is no logging nor any traces set up on this DB. Is there a way to recover the parameters passed to that procedure, using existing system logs?

I've looked at: dm_exec_procedure_stats and fn_dblog but they don't seem to have what I need.

I'm setting up logging moving forward.

Answer 1

There is no built-in logging mechanism that runs by default out-of-the-box to capture stored procedure parameter values.

Depending on the version of SQL Server you're using, the best way to capture that detail is via Extended Events (actual execution plans and looking at parameter runtime values. Pretty expensive though, and a bear to parse out via XML).

I suppose "best" is very subjective. I don't think there is really a great, low-impact, way to get the actual parameter values for every execution of a stored proc through some SQL Server built-in mechanism. It's probably easier to log that through the client, or via logging built into the stored proc.

Answer 2

You mentioned:

I know which stored procedure is responsible to handling the data I'm looking at

If the plan for that stored procedure is still in the plan cache you can get the values with which stored procedure was compiled with (sniffed value). Plug the database name and stored procedure name into this query. Of course I do not know your problem occurred with the compiled value or some other value (that was called with).

This is taken from Slow in the Application, Fast in SSMS? by Erland Sommarskog.

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'databaseName',
       @procname = 'schema.storedProcedureName'

; 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

Answer 3

Quick Answer, there is no easy way to recover past executions if no logging was set up.

dm_exec_procedure_stats reports cumulative figures from the last server restart or explicit stats reset.

You could try to rebuild params by identifying transactions associated with the proc, named transactions would help in this case, and examining data associated with those transactions, but whether thats worth the time and effort is questionable.