Click here to Skip to main content
Click here to Skip to main content

SQL Server PERFORMANCE with Wait Types and Queues

By , 20 Apr 2011
Rate this:
Please Sign up or sign in to vote.

Read all the posts in the Wait Types and Queue series.

I have been working a lot on Wait Stats and Wait Types recently. Last Year, I requested blog readers to send me their respective server’s wait stats. I appreciate their kind response as I have received Wait stats from my readers. I took each of the results and carefully analyzed them. I provided necessary feedback to the person who sent me his wait stats and wait types. Based on the feedback I got, many of the readers have tuned their server.

After a while, I got further feedback on my recommendations and again, I collected wait stats. I recorded the wait stats and my recommendations and did further research. At some point in time, there were more than 10 different round trips of the recommendations and suggestions. Finally, after six month of working my hands on performance tuning, I collected some real world wisdom because of this.

Now, I plan to share my findings with all of you over here.

Before anything else, please note that all of these are based on my personal observations and opinions. They may or may not match the theory available at other places. Some of the suggestions may not match your situation. Remember, every server is different and consequently, there is more than one solution to a particular problem. However, this series is written with kept wait stats in mind. While I was working on various performance tuning consultations, I did many more things than just tuning wait stats.

Today, we will discuss how to capture the wait stats. I use the script diagnostic script created by my friend and SQL Server Expert Glenn Berry to collect wait stats.

Here is the script to collect the wait stats.

This script uses Dynamic Management View sys.dm_os_wait_stats to collect the wait stats. It omits the system-related wait stats which are not useful to diagnose performance-related bottleneck. Additionally, note OPTION (RECOMPILE) at the end of the DMV will ensure that every time the query runs, it retrieves new data and not the cached data.

This dynamic management view collects all the information since the time when the SQL Server services have been restarted. You can also manually clear the wait stats using the following command:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Once the wait stats are collected, we can start analysing them and try to see what is causing any particular wait stats to achieve higher percentages than the others.

Many waits stats are related to one another. When the CPU pressure is high, all the CPU-related wait stats show up on top. But when that is fixed, all the wait stats related to the CPU start showing reasonable percentages. It is difficult to have a sure solution, but there are good indications and good suggestions on how to solve this.

I will keep this blog post updated as I will post more details about wait stats and how I reduce them. The reference to Book On Line is over here.

Of course, I have selected February to run this Wait Stats series. I am already cheating by having the smallest month to run this series. Smile | :)

Read all the posts in the Wait Types and Queue series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 20 Apr 2011
Article Copyright 2011 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid