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.
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.
Maybe add all the SP:????? events to see where the time is going - it could be in cache access or compile/recompile.
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/
12 People are following this question.
