Michael J. Swart

January 8, 2021

Collect Wait Stats Regularly in Order To Report On Them Over Time

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:48 pm

I find wait stats so useful that I’ve got Paul Randal’s SQL Server Wait Statistics (or please tell me where it hurts…) bookmarked and I visit it frequently.

But that gives the total amount of waits for each wait type accumulated since the server was started. And that isn’t ideal when I’m troubleshooting trouble that started recently. No worries, Paul also has another fantastic post Capturing wait statistics for a period of time.

Taking that idea further, I can collect data all the time and look at it historically, or just for a baseline. Lot’s of monitoring tools do this already, but here’s what I’ve written:

Mostly I’m creating these scripts for me. I’ve created a version of these a few times now and some reason, I can’t find them each time I need them again!

This stuff can be super useful, especially, if you combine it with a visualization tool (like PowerBI or even Excel).
For example, here’s a chart I made when we were experiencing the XVB_LIST spinlock issues I wrote about not too long ago. Good visualizations can really tell powerful stories.

A visualization of a spinlock graph

I’m talking here about spins and not waits of course, but the idea is the same and I’ve included the spinlock monitoring scripts in the same repo on github.

Also a quick reminder wait stats aren’t everything. Don’t neglect monitoring resources as Greg Gonzales pointed out last year.

3 Comments »

  1. […] Michael J. Swart has a repo for us: […]

    Pingback by Collecting Wait Stats Over Time – Curated SQL — January 11, 2021 @ 8:00 am

  2. Great post! I can’t wait to try this. Our shop is small so that a SQL Server monitoring tool is not cost-effective. I have to resort to manual methods.

    Can you point me to documentation that shows how to establish a PowerBI report for this data?

    Thanks!

    Comment by Robert Plata — January 15, 2021 @ 10:58 am

  3. Thanks Robert.
    I haven’t played around with PowerBI too much, but I have seen the things it can do.
    I know there are resources out there, but I can’t recommend any with confidence because I haven’t really tried them out.
    Maybe start with “What is PowerBI?”

    Comment by Michael J. Swart — January 15, 2021 @ 1:00 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress