DaveWentzel.com            All Things Data



sys.dm_os_waiting_tasks shows the tasks currently in a wait state.  This will not just show you waits, but information on blocking.  Almost any row in the output of this DMV can be considered as signifying blocking.  But there are some common entries in the output that can be ignored.  On 2000 you have to use sysprocesses and it contains much more information that just blocking and waits.  Also sysprocesses only gives you information at the process level of granularity.  sys.dm_os_waiting_tasks gives you information at the task level.  One process may have multiple tasks, each with children tasks (think parallel execution plans). 
It has 3 groups of cols...those that pertain to the waiter (waiting_*), those that pertain to the blocker (blocking_*), and those that provide information about the wait that is occurring.  If the blocking task is not known then a null will appear. 
This DMV is preferred over dm_exec_requests b/c it provides much more detailed information. 
sys.dm_os_waiting_tasks shows you the current state of affairs.  While useful you may want to see historical aggregated information.  sys.dm_os_wait_stats shows the historicals.  Don't be concerned with the wait counts since these are aggregated since the last restart.  Be more concerned with high average and max values.  Also, taking occassional snapshots of the view and comparing them over a time period is useful (say over a load test).  This is how Oracle StatsPack works.  You can also clear the historicals by running DBCC SQLPERF ('sys.dm_os_wait_stats',CLEAR). 
In 2000 there is really no proactive method of blocking notification, unless you roll your own kludgey job which many have.  These work, but not as elegantly. 
After you have the wait type and description you need to determine what the call is using sys.dm_exec_requests (sql_handle).  sql_handle is a durable value meaning that you can query it after the fact (just don't restart in most cases). 
When doing analysis against sys.dm_os_waiting_tasks, patterns are your friend.  Patterns to look for:
  • long waits.  These may not affect server-wide throughput, but resolution will affect the given task's performance. 
  • a lot of waits for the same resource (throughput and response times are affected server-wide).  This may be a hotspot. 
  • blocking chains.  Look for the head blocker which is the blocking task(s) that is not blocked itself. Do a google search for many different types of queries that find the head blocker.  

Add new comment