We have been working on a new custom module for Inspector V2, in fact this module is part of a new project where we will be producing two other new modules for Perfmon data and BlitzWho data collected by the First Responder kit data collection. It’s all open source so if you have improvement ideas you find a bug or want to get involved we would love to hear from you just create a new issue on GitHub , drop us an email or join us in the #sqlundercovertools slack channel on slack sql community.
What does it do?
The BlitzWaits custom module has been created to produce a html report of the SQL server waits collected by the First responder kit. The waits information is grouped into hourly buckets and represented as the top X waits per bucket with X being configurable and hour bucket size also.
For example, I may want a daily digest of the waits for my SQL server for the last 24 hours showing me the TOP 5 wait types grouped by 2 hourly buckets, which will flag any Poison wait types even if they are not present in my TOP 5 , that would look something like this.
Configurable Options:
The Inspector.Settings table contains 5 settings for BlitzWaits:
BlitzWaitsTopXRows – gives you control on how many top waits you want to see per bucket.
BlitzWaitsHourlyBucketSize – allows you to specify a size between 1 and 24 to represent the size of the rollup of data into the buckets.
BlitzWaitsAlwaysShowBreached – Force visibility of Poison wait types or any other wait types you are tracking from the Inspector.BlitzWaits_WatchedWaitTypes table where the threshold has been breached (for poison waits that’s just greater than 0) – you may find that the thresholds are too sensitive as defaults so please alter them to your requirements.
BlitzWaitsBucketColourOdd and BlitzWaitsBucketColourEven – Specify hex colours here to replace the alternating bucket colours with colours of your choice.
Prerequisites:
- Brent Ozar First Responder kit sp_BlitzFirst collection logging database to a database.
- SQLUndercover Inspector V2 installed in the same database as your FRK sp_BlitzFirst collection
Installation Instructions:
- Install Inspector V2 if you do not already have it.
- Download Inspector_BlitzWaits_CustomModule.sql from Github
- Use your logging database where you have the Inspector Installed Review the Parameters and execute the code.
Installation Parameters:
These Parameters control the report size TOP X wait stats and the size of the buckets for summing of waits deltas. Can be changed in the Settings table at any time
DECLARE @BlitzWaitsTopXRows TINYINT = 3;
DECLARE @BlitzWaitsHourlyBucketSize TINYINT = 2;
Alternating bucket colours specify a html color as hex, can be changed later in the settings table
DECLARE @BlitzWaitsBucketColourOdd VARCHAR(7) = ‘#E6F5FF’
DECLARE @BlitzWaitsBucketColourEven VARCHAR(7) = ‘#CCEBFF’
Set the ModuleConfig to use, specify an existing one or a new one to if you want this to report independently, Append to the Default ModuleConfig or specify a new ModuleConfig i.e ‘BlitzWaits
DECLARE @ModuleConfig VARCHAR(20) = ‘Default’; ‘
Frequency ,Start and End times only apply if the @ModuleConfig does not exist as we do not want to update your existing schedules.
DECLARE @ReportFrequencyMins SMALLINT = 1440; Maximum supported value is 1440 (once a day)
DECLARE @ReportStartTime TIME(0) = ’09:00′;
DECLARE @ReportEndTime TIME(0) = ’18:00′;
Enable or Disable the module following installation
DECLARE @EnableModule BIT = 1;
See waits from your watched list IF the threshold was breached (this will increase rows returned in the bucket if breaches are present this includes Poison wait types), can be changed in the Settings table at any time
DECLARE @BlitzWaitsAlwaysShowBreached BIT = 1;
Note:
It is likely that the first time you run the report you will see poison waits if you have the show breached option = 1 because we set the threshold really low! so you may want to adjust these in the Inspector.BlitzWaits_WatchedWaitTypes table.
SQL Undercover 