question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

SQL Server Profiler

I have a SQL 2008 R2 database and am trying to find the slow parts of a particular stored procedure. I'm using profiler, I know I should make the move to extended events but need to get fully up to speed with it first! Anyway I've selected the Event Class SP:stmtCompleted which is giving me the durations, however the total of all the durations is a lot less than the duration of the stored procedure, but I'm not sure what information is missing. Can anyone advise please. If someone can tell me which events to use to set up Extended Events I'd happily make the move.
profilerextended-events
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
It could be because SP:stmtCompleted is much granular and has the details only about the currently executing query in case of RPC:Completed they have the aggregates of time including Complilation\Recompilation, Work table time and other Statement level details. Coming to extendedEvents, considering the fact that generally waits and locks are the main performance problems, I will start looking at the wait stats this gives you a good starting point to start troubleshooting the perf problems. Consider the following columns sqlserver.sql_statement_completed.duration,locks_lock_waits,wait_info,wait_info_external,lock_acquired etc. Once you have the query analyze its execution plan and fine tune.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Maybe add all the SP:????? events to see where the time is going - it could be in cache access or compile/recompile.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
I would look at the query plan for the stored procedure. This might be a place to start: http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.