First published on MSDN on Dec 16, 2012
Have you wondered if there is a simple way to find out if a query is slowed down by bottlenecks?
Looking at the Duration (Elapsed Time) and CPU (Worker Time) reported by a completed query can reveal the answer.
See, Duration/Elapsed Time indicates the overall lifetime of a query, while CPU shows only the time the query actually executed on the CPU. What that means is that there is a simple formula to determine how long a query waited on bottlenecks (valid for serial query plans):
Formula:
Duration - CPU = Wait Time (time waiting for a resource).
So for example, if a query had an elapsed time of 10 seconds, and worker time of 3 seconds, this means the query spent 7 seconds waiting on bottlenecks. That is 70% of the query's lifetime was spent waiting. The best-case scenario is that a query spends no time waiting for resources, or Duration - CPU = 0.
What to do?
Once you know that a query spent most of its lifetime waiting, then to improve its performance considerably, you need to remove the bottleneck (be it blocking, slow I/O, etc.). Commonly a query that is waiting for a resource is a victim, not the culprit. Therefore, identify the culprit and focus on resolving that.
What NOT to do?
When you have a query that is spending most of its lifetime waiting, taking actions that will reduce its CPU/execution time is not the best investment of your time and effort. Typically this means that you don't need to analyze the query plan, or update statistics or improve indexing - all things that commonly reduce the number of reads and thus CPU time. Your efforts should focus on finding which bottleneck is slowing the query down and eliminating it. For example if blocking is the prime bottleneck, then identify the head blocker and improve its performance (fixing the culprit).
How to Identify “Waiters”?
Here is a script that will allow you to determine which queries on your system are underperforming due to waits. I call these Waiters. Once you find out whether there are waiters and who the waiters are, you can focus on troubleshooting Waits
select
text
,total_elapsed_time/1000/execution_count as AvgDuration_ms
,total_worker_time/1000/execution_count as AvgCPU_ms
,(total_elapsed_time-total_worker_time)/1000/execution_count as AvgWaitTime_ms
,case when
convert(decimal(10,1), 100*(total_elapsed_time-total_worker_time)/(total_elapsed_time)) >= 50
then'Waiter'
else'Runner'
end as ProblemType
,convert(decimal(10,1), 100*(total_elapsed_time-total_worker_time)/(total_elapsed_time)) as PercentWait
from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle)
where total_elapsed_time/1000 > 0
order by PercentWait desc
Namaste!
Joseph
Microsoft
