Troubleshootoing performance problems in sql server with sys.dm_os_wait_stats

27 February,2017 by Jack Vamvas

The SQL Server DMV sys.dm_os_wait_stats is a well established DMV , used heavily in troubleshooting performance problems in SQL Server.

The DMV is an aggregated view on all the sql server wait types . When a thread is executed part of the runtime are waits. sys.dm_os_wait_stats presents the wait type aggregates. It is also possible to break down the waits at a query level.

It’s useful to understand the meaning of the columns.

According to the MSDN web site

Wait_time_ms Total =  wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms

signal_wait_time_ms = Difference between the time that the waiting thread was signaled and when it started running

It is useful to split the wait_time_ms into sub components.     By subtracting signal_wait_time_ms  from Wait_time_ms it is possible to differentiate between when a thread is waiting for a resource to become available allowing the thread to progress versus when a thread has the resources available and is waiting for CPU.

To understand the meaning behind these concepts read SQL Server RUNNABLE – SUSPENDED – RUNNING status model ...

Some more useful reading on wait type analysis

Find sql queries matching wait types (SQL Server DBA)

Troubleshooting BACKUPTHREAD wait type (SQL Server DBA)

SQL WAIT TYPE – OLEDB and how to reduce it (SQL Server DBA)

SQL Server – IO_COMPLETION and how to reduce it

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Troubleshootoing performance problems in sql server with sys.dm_os_wait_stats


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer