Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
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
1  
Also, check SQL Server isn't being started with the -x option, or any -T trace flags. –  Paul White Jan 16 '12 at 13:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.