BlitzFileStats is the latest addition to our custom module collection for reporting on data logged to tables using the First Responder kit, We created a new project where we plan on producing two other new modules for Perfmon data and BlitzWho data collected by the First Responder kit data collection alongside the previously released BlitzWaits custom module . 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 BlitzFileStats custom module has been created to produce a html report of the SQL server IO stats collected by the First responder kit when the Average IO stall for Read or Write exceeds thresholds you set in a config table. For example, you are logging Filestats information using the FRK collection but want to be informed via email when the average IO stall for reads exceeds 100ms we can achieve this by setting the threshold accordingly in the table Inspector.BlitzFileStatsConfig and if you want to run it on its own schedule to poll the collected data on a frequency you choose you can do that too all via the same tables you use for other Inspector modules.
The Module will look for IO stall for the last (Frequency you set for the report) minutes ago where the Datetime Hours are between your “Monitor Hours” and the stall in ms breaches your threshold, by default Monitor hours are set to 0 and 23 (all day long).
Configurable Options:
Thresholds are set in the table Inspector.BlitzFileStatsConfig, in the example above we will only see a report for this module if IO Stall average for Read or Write exceeds 100ms for the reporting period, lets say I have a new report schedule setup which runs once an hour, this will check the FileStats collected data for the last hour where the IO stall breaches the thresholds.
The new Inspector.MonitorHours table Introduced in V2.3 or with installation of the latest revision of CPU, BlitzWaits or BlitzFileStats custom modules is now a central table which controls when you see data for, for example you may be collecting data all the time but perhaps you are only interested in seeing reports where breaches occurred during specific hours. Above shows the default config but but lets say you are only interesting seeing IO stall problems during the hours of 9am to 5pm, that would look like the below image:
Prerequisites:
- Brent Ozar First Responder kit sp_BlitzFirst collection logging data to a database.
- SQLUndercover Inspector V2.2+ 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_BlitzFileStats_CustomModule.sql from Github
- Use your logging database where you have the Inspector Installed Review the Parameters and execute the code.
Installation Parameters:
DECLARE @ModuleConfig VARCHAR(20) = ‘Default’; — Append to the Default ModuleConfig or specify a new ModuleConfig i.e ‘BlitzFileStats’
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';
DECLARE @MonitorHourStart INT = 0 Values accepted: 0 to 23
DECLARE @MonitorHourEnd INT = 23 Values accepted: 0 to 23
Enable or Disable the module following installation
DECLARE @EnableModule BIT = 1;
Note:
You are only going to see this module show on the report IF your thresholds are exceeded, if you want to be sure that the module is running on schedule (The schedule from ModuleConfig which the module is subscribed to) then double check the ExecutionLog to see that the report proc has ran successfully.
SELECT * FROM Inspector.ExecutionLog WHERE Procname = 'BlitzFileStatsReport' ORDER BY ID DESC;
