Attendee: “Is SQL waiting Stats still relevant for performance tuning? If yes, where should we start learning about it.”
Pinal: “Absolutely. SQL Wait stats are always going to be there as a one of the most promising ways to know what is the real bottleneck of the server. You can run the diagnostic query on your server and immediately know what was causing your query to wait.”
Attendee: “Where do we start?”
I love this question as SQL Wait Statistics are my favorite topic. I have earlier written about SQL Wait Stats in my 28 day series over here: SQL SERVER – Summary of Month – Wait Type – Day 28 of 28. I strongly encourage to bookmark the page to read more about the wait stats.
Pinal: “Read my blog posts on this subject as they gives you quite a good start on this subject.”
Attendee: “Can you help us with an analysis of the query for our server?”
Pinal: “Sure, I can. Execute following query on your server and send me the output in Excel format. I will reply back to you very soon with my findings.”
ORDER BY wait_time_ms DESC
Attendee: “Thank you. However, we have more than one server, honestly we have over 100’s of server in our system and we can’t just manually do that.”
Pinal: “True, we can’t do that manually for you all of the 100’s of servers. If that is the case, I suggest you use professional performance tuning tool.”
Attendee: “Is it Free? Because…”
Pinal: “Sure it is FREE – You can download download Spotlight on SQL Server Freemium right away and check your various wait stats. It even sends alerts if the health of your server goes down.”
Attendee: “Good. I will try now.”
Pinal: “One more thing – if you want to learn more you can watch Paul Randal’s SQL Wait Stats course on Pluralsight as well. It is the most comprehensive video course available online from most renowned SQL Expert in the world.”
Well, this was my conversation earlier but if you are facing issues with Wait Stats, just run above query and do send it to me at my email address and I will make sure that I reply you faster. Meanwhile do try out Spotlight On SQL Server Freemium on your server and do let me know your findings.
Reference: Pinal Dave (https://blog.sqlauthority.com)