SQL Server PERFORMANCE with Wait Types and Queues

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. :)

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