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 (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – What are Wait Types, Wait Stats and its Importance

  1. Can you explain when you think that CXPACKET wait type becomes a problem that should be looked at? BOL says “You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.” but I see different opinions on when it’s a good idea do so.

  2. Dear Jason,

    CXPACKET issue arise when processor switching fastly and dividing query on multiple logical processors,you can disable your HT of processor to remove this problem

    regards
    Jahanzaib

  3. Sir,

    We do need some good blog posts from you on waits as they are generally not very well explained.
    Hoping for more articles on this topic from your end.

    Thanks,
    Amit

  4. Pingback: SQLAuthority News – SQL Health Check and SQL Seminars Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s