As I was waiting for the next TSQL Tuesday topic to be announced, we all know that when SQL Server performance is slow, and becomes a big issue in your company, sometimes we need to throw an army at the problem to resolve it. Other times, we can employ an army-of-one, a soldier who can troubleshoot almost any performance bottleneck in his sleep! His name, Robert L. Davis, aka SQLSoldier, is the man with the plan, and the #sqlhelp hashtag on twitter, who is the host of today’s TSQL Tuesday topic #49 – Wait For It….well, that’s it, what are you all waiting for? Yes, the topic is Wait Stats, a topic near and dear to my SQL Soul, as I have written and presented on this topic for some time now.
Therefore, art tho SQL readers, Mr. Davis wants us to write about our most interesting post involving waits (wait types, queues, DMV queries, etc). Now, there’s quite a lot out there, including my own blog posts, as well as an entire chapter, written by yours truly, in SQL Server Deep Dives, Volume 2, (Chapter 34), aptly entitled, “What are you waiting for? An introduction to wait and queues” In my chapter, I introduced the reader to one of the most underutilized performance troubleshooting methodologies at the time, as well as about the SQL Server execution model, running and runnable processes, signal vs. resource waits, and information on finding and resolving IO, CPU, and/or memory bottlenecks.
Now the secret is out, and if your SQL Server is experiencing technical difficulties, the first place you should be looking, is the SQL Server wait stats. By examining the wait statistics we can understand where SQL Server is waiting, and what resource it’s waiting for to become available. Once identified, you can set up relevant performance counters to further analyze the cause of resource contention, responsible for slowing down the server.
One early on blog that I started discussing this topic, was a precursor to my chapter and verse, was simply doing my part to evangelize Waits & Queues, and how I first utilized this performance tuning method at a client, for whom I did a performance review. You can see this here.
So, where does one find these wait stats that we speak of? The primary system views that expose these metrics are sys.dm_os_wait_stats, and sys.dm_os_waiting_tasks. The wait_stats DMV is historical aggregated data across all session ids since, SQL Server last started (or statistics cleared), and shows the time for waits that have completed.
The waiting_tasks dmv, shows the current waits at the moment in time it is queried, and shows which resources it is waiting on (the suspended queue) for all active sessions. It is what is happening on the server right now.
Just back from SQLSaturday#233 DC Circuit, I presented my HealthySQL™ session, and part of my roll your own health check strategy was to employ waits & queues. I recently blogged about SQLSaturday HealthySQL, (which you can read by clicking on the highlighted link)
So, glad I can participate in this December 2013 edition of T-SQL Tuesday. Don’t keep your end-users waiting, if SQL does, find out why using wait statistics. I look forward to reading the many other wonderful writings on waits. Thanks again to Robert Davis for hosting, and for picking such a great topic, and look forward to continuing news from the frontlines blog
And now, the obligatory credits and rules of the road, Started by our friend SQL MVP Adam Machanic blog|twitter, who came up with the idea of improving community involvement via blogging where bloggers around the world post their views on a same topic chosen by the host on the 2nd Tuesday of every month. If you are interested in participating in T-SQL Tuesday, please follow these guidelines.
- Your post must be published between 00:00 GMT Tuesday, October 8th, 2013, and 00:00 GMT Wednesday, October 9th, 2013
- Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
- Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work
And some optional (and highly encouraged) things to also do:
- Include a reference to T-SQL Tuesday in the title of your post
- Tweet about your post using the hash tag #TSQL2sDay
- Consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list. (Sign up now, because, you’ll be waiting until 2015, since it is booked from now until then!)
Please follow me on Twitter @Pearlknows,
and check out our web-site for all our available services at http://www.pearlknows.com.
Take our HealthySQL Challenge! Are you SQL Servers healthy? How do you know for sure? Please contact us about our 15-point Health Check report, which will identify areas for improvement, and allow for best practice recommendations for your SQL Server(s). If we find NOTHING wrong with your SQL Server, the report is FREE! Contact us as firstname.lastname@example.org