BlitzWaits custom module available for Inspector V2

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.

Watched waits table , you can set a Threshold value in here and a Direction i.e < or > (less than or greater than) which is used to determine whether these wait types should show if the BlitzWaitsAlwaysShowBreached setting is on whether they feature in the Top X or not.

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.

One thought on “BlitzWaits custom module available for Inspector V2

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: