SQL SERVER – What are Wait Types, Wait Stats and its Importance

Earlier last month Solid Quality India announced SQL Server Health Check Service and since then, it has got very good response from the industry. However, the only question we are be asked all the time is: “What is “SQL Server Resource Wait Stats Analysis” and how can it be useful?”What caught my attention is that it seems everyone understood what the other details on the page mean, but most of them have a query regarding Wait Stats and their importance.

For such a long time, even I wasn’t sure what Wait Stats are. Later on, I learned Wait Stats from Andrew Kelly of Solid Quality Mentors and now I feel very comfortable using them. Basically, wait types are nothing but wait performed to execute any task created by SQL Server. As per BOL, there are three types of wait types, namely:

• Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
• Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
• External Waits. External waits occur when an SQL Server worker is waiting for an external event.

To check the wait types for any server, just run the following simple statistics:

SELECT *
FROM sys.dm_os_wait_stats


You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.

The link to Book On Line where you can read all the Wait Stats is over here: SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

There are few system wait types which are always present and there are a few wait types which can really cause a heavy bottleneck for your system. The science of Wait Types is very tricky. If you have ever attended a session of Andrew Kelly focusing on this subject, you would know that you will not always find what you will see. There are times when their conclusion can be different. Personally, I like to compare them with horoscope. Just like every expert interprets them differently, the same way Wait Stats are. Experts who have real world performance tuning experience can analyze them and come to a proper conclusion.

During the health check service, I am usually pretty good at coming to a conclusion; however, there have been cases when I have to refer the issues to my fellow experts to seek help. Let me know if you are interested to learn more about this subject, and I will do further blog posts on about Wait Stats.

Reference : Pinal Dave (https://blog.sqlauthority.com)