THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 11 (sys.dm_os_wait_stats)

 

The sys.dm_os_wait_stats DMV collects instance wide aggregations of all waits that have occurred on your SQL Instance since the last time the DMV statistics were reset. This DMV is useful when needed to determine the top waits that are occurring on your server so that you know where you should focus your tuning efforts.

To help illustrate this DMV, let run a quick query against it:

SELECT * FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0

image

The first column, wait_type, displays each wait type that has occurred on your instance since the last time the DMV statistics were reset. The second column, waiting_tasks_count, give you the number of times that a particular wait type has occurred. The wait_time_ms column gives you the total time in milliseconds that a particular wait type has consumed. The max_wait_time_ms column gives you the maximum time in milliseconds that a wait type has waited for out of the X number of times it has waited. The final column returned by this DMV is the signal_wait_time_ms. This column represents the amount of time that a thread has spent on the Runnable queue. To help illustrate what this column represents, when a thread is executing, its status is listed as ‘Running’ and it is currently on a CPU executing. If the thread needs a resource that it cannot access yet, it will move to the Suspended Queue and have a status of ‘Suspended’. Once the thread acquires the resources it needs it moves to the Runnable queue and has a status of Runnable. This is when the signal_wait_time counter begins. The thread has all of the resources it needs and is simply waiting for its turn to get back onto the CPU. Once it moves back to the CPU, the status will change to ‘Running’.

**Note: Threads can also come off of the CPU even if they are not lacking any needed resources. This happens when there are other threads in a ‘Runnable’ state waiting to get on the CPU. In this case the thread currently on the CPU will yield so that other Runnable processes are not constantly waiting for their chance on the CPU. This is generally represented by a SOS_SCHEDULER_YIELD wait type and can often indicate CPU pressure. When threads yield, they go directly to the ‘Runnable’ queue.

As stated earlier, this DMV is very useful for tracking down the top waits on your server so you can formulate a plan for improving the server performance.

For more information about this DMV, please see the below Books Online link:

http://msdn.microsoft.com/en-us/library/ms179984.aspx

Follow me on Twitter @PrimeTimeDBA

Published Thursday, July 11, 2013 8:55 AM by Tamarick Hill

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement