I'm trying to diagnose a slow running stored procedure in SQL Server.

The procedure is quite long and complicated. I have been trying to figure out which statement is causing the issue.

I have run a SQL Server Profiler trace with the SP:StmtCompleted and RPC:Completed events set to capture. This looks like it should give me the information I am looking for, the Duration column is showing 0 for all the events. Is this normal? I don't believe that the events are taking less than a microsecond to execute.

On the PRC:Completed event both Start and End time are both set to the same value.

I have made sure that the Duration column is selected in the Trace settings, I have also set the Tools->Options->Show values in Duration column to microseconds.

Note: I don't want people to fix my procedure, this question is about the technique for capturing diagnostic information.

  • 4
    I've never had any issues with the Duration not being reported correctly. If your start and end times are the same sounds like it is just executing quickly. Why not chuck some guaranteed long running statements in there like waitfor delay '00:00:05' or SELECT COUNT_BIG(*) FROM master..spt_values v1, master..spt_values v2, master..spt_values v3, master..spt_values v4 just to sanity check? – Martin Smith Jan 16 '12 at 10:50

Your Answer

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

Browse other questions tagged or ask your own question.