Yesterday I published a Wait Stats for Performance – SQL in Sixty Seconds #157. During the video, I mentioned that I will be happy to give feedback on the SQL Wait Stats if I receive emails from users. I received over 300 emails in just the first hours of publishing the video. It took me more than 9 hours to respond back every single email, I had received on this topic. Here are the Top 3 Wait Stats from Real-World.
If you want to analyze your wait statistics, here are the Wait Stats Collection Scripts: Updated March 2021. , you can send these results via excel file to me and I will be happy to get back to you with my feedback.
3 Real-World Wait Stats
Now let us see the top 3 wait statistics which I have collected from this real-world experiment. Here is the SQL in Sixty Seconds video on the same topic.
CXPACKET
I must say this must be the #1 wait with most of the users who sent me their server’s wait statistics. I must say that while this is on the top of the list, it is not necessary that is bad all the time. However, if your workload is transactional, it is a good idea to keep this one in check and make sure it is not the main wait on your server.
Here is some good reading on this topic and how you can resolve this one.
- SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28
- SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28
SOS_SCHEDULER_YIELD
There have been cases where this particular wait statistic has been the top wait statistic and CXPCAKET is down in the list. This is because SQL Server has multiple threads, and the basic working methodology for SQL Server is that SQL Server does not let any “runnable” thread starve.
Here is some good reading on this topic and how you can resolve this one.
ASYNC_IO_COMPLETION
As the modern apps are getting more sophisticated, I was not expecting this wait stat to be in the top 3 wait statistics. However, it was still there and the sad part is that there is no single solution to this problem. This wait indeed slows down the performance of the application and database server, so it is essential to discuss this one. I have seen this wait quite a lot of time when the application is using some kind of curser to hold data while keeping the connection to the server alive.
Here is some good reading on this topic and how you can resolve this one.
Summary of Real-World Wait Stats
There are many different wait stats out there and each wait has its own purpose. Some are good and some are bad. I have written a blog series about major wait stats. Here is the link to the series where you can find more details about the various waits: SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28
If you have any questions, do reach out to me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
