How to Log Wait Stats to Table with sp_BlitzFirst

When you’re analyzing SQL Server performance, one of the best places to start is wait stats. What’s your SQL Server waiting on?

sp_BlitzFirst makes it really easy to trend this data over time for free with all currently supported versions of SQL Server (2008+). Just set up an Agent job to run this every 15 minutes:

sp_BlitzFirst logging tables

sp_BlitzFirst will create those tables if they don’t already exist. (The database & schema have to already exist, though.) It logs cumulative data in those tables just like the DMVs they’re sourced from:

  • sys.dm_os_wait_stats
  • sys.dm_os_performance_counters
  • sys.dm_io_virtual_file_stats

The tables are designed to be combined centrally from all of your servers, too – they include columns for ServerName and CheckDate (which is stored in datetimeoffset format, so you can report on data from servers in different time zones.)

But since that data is cumulative, it can be a little painful to query – so sp_BlitzFirst also creates a set of delta views for you. They’re named exactly the same as the table names you picked, but with _Deltas added at the end, like BlitzFirst_WaitStats_Deltas. These join the current sample with the prior sample to figure out how much time elapsed between samples, and how much wait time you had per minute during that sample.

Throw that into Microsoft’s free Power BI tool, and presto:

sp_BlitzFirst data in Power BI

From here, you can drill down into the other tables to see why the SQL Server was slow during that time range. Heck, armed with the other First Responder Kit scripts and their abilities to log data to table, you can even find out what queries were causing your high wait stats.

And it’s all totally free.

Wanna learn how? We’ll teach you in the very first module of our Expert Performance Tuning for 2016 & 2017 class (and yes, that includes those of you in our PASS Summit pre-con!)

Previous Post
How to Do a SQL Server Architecture Review
Next Post
[Video] Office Hours 2017/10/11 (With Transcriptions)

29 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.