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.

  • is the stored procedure signed, or executed as a particular user? – Xingzhou Liu Aug 9 '17 at 17:30
  • @XingzhouLiu: it's not. – Nate Anderson Aug 9 '17 at 17:36
  • @NateAnderson - you have dm_exec_procedure_stats mentioned twice in the list of things you've looked at. Presumably that's an oversight? – Max Vernon Aug 9 '17 at 17:41

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.

| improve this answer | |
  • This looks like it will work out. The error I'm getting is low-frequency/low-impact enough to just wait for the next occurrence and check the logs. – Nate Anderson Aug 9 '17 at 19:29

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_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 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
| improve this answer | |
  • This is a great answer - unfortunately the data I needed was not there. Thanks for the tip though. – Nate Anderson Aug 9 '17 at 19:27

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.