sp_BlitzIndex

Job details

Name:	sp_BlitzIndex
Platform:	Sqlserver
Category:	Performance extension (Brent Ozar)
Description:	Runs the sp_BlitzIndex procedure (Brent Ozar) which analyzes the design and performance of your indexes.
Long description:	Runs the sp_BlitzIndex procedure (Brent Ozar) which analyzes the design and performance of your indexes.
Version:	1.2
Default schedule:	5 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]/blitz_obj_id/obj_name[id=‘sp_BlitzIndex’]/instance

Parameters

Name	Default value	Description
location of dbo.sp_BlitzIndex procedure	master	Name of the database where the dbo.sp_BlitzIndex procedure is located.
delete old output	YES	If set to “YES“ (default) old statistics from previous executions of sp_BlitzIndex procedure will be deleted.
return status when missing indexes	0	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when there are missing indexes.
@Mode	2	0=Diagnose, 1=Summarize, 2=Index Usage Detail, 3=Missing Index Detail, 4=Diagnose Details
@DatabaseName		If you want to filter to a specific database. Defaults to current DB if not specified
@SchemaName		Requires table_name as well.
@TableName		Requires schema_name as well.
@Filter	0	0=no filter (default). 1=No low-usage warnings for objects with 0 reads. 2=Only warn for objects >= 500MB.
@SkipPartitions	0
@SkipStatistics	1
@GetAllDatabases	0
@BringThePain		Bring the pain.
@OutputDatabaseName		If you want to output information to a specific database. This is input parameter to the sp_BlitzIndex procedure.
@OutputSchemaName	dbo	Specify a schema name to output information to a specific Schema. This is input parameter to the sp_BlitzIndex procedure.
@OutputTableName	BlitzIndex	Specify table name to output information to a specific table. This is input parameter to the sp_BlitzIndex procedure.
@IgnoreDatabases		Comma-delimited list of databases you want to skip.
@ThresholdMB	250	Number of megabytes that an object must be before we include it in basic results.
@OutputType	TABLE
@IncludeInactiveIndexes	0	Will skip indexes with no reads or writes.
@ShowAllMissingIndexRequests	0	Will make all missing index requests show up.
@SortOrder		Only affects @Mode = 2.
@SortDirection	DESC	Only affects @Mode = 2.
max history records	1000	Maximum number of records in the history table tab_histr_sp_blitzIndex.

Job Summary

Purpose: The purpose of this job is to analyze the design and performance of indexes on SQL Server databases by running the sp_BlitzIndex procedure provided by Brent Ozar.
Why: This job is important as it provides crucial insights into index usage and performance problems which can help in optimizing query speeds and overall database performance.
Manual checking: You can check the performance and design of indexes manually by issuing the following SQL command:

EXEC sp_BlitzIndex @Mode = 2;

Job Details

The job is categorized under “Performance extension (Brent Ozar)” and specifically uses the “sp_BlitzIndex” stored procedure.
The default schedule for the job is to run every hour as indicated by the cron-like schedule format “5 * * * *”.
It doesn’t require any manual acknowledgments post-execution as per the “acknowledgements” setting.

Dependencies

The main dependencies for the job include:
- The stored procedure “proc_run_sp_blitzIndex”.
- History tables “tab_histr_sp_blitzIndex” and “sp_blitzIndex_exec_tab_histr” to maintain execution history and statistics.

Implementation Overview

The implementation involves:
- Creating necessary tables for storing historical data and execution details.
- A stored procedure “proc_run_sp_blitzIndex” which prepares and executes the sp_BlitzIndex command using dynamic SQL and parameter values drawn from “DBW_PARAMETERS“.
- The stored procedure manages data cleanup from the history table based on predefined criteria and logs execution details.

Presentation of Data

The system will generate reports including:
- Index statistics history showcasing the last 50 indexes analyzed.
- Execution details revealing commands run parameters used, and responses received for the analysis tasks.

Upgrade and Installation Considerations

During upgrades, additional features or fields might be added to the tables involved in capturing historical data.
Specific checks are in place to ensure the dbo.sp_BlitzIndex exists and is operational before the job attempts to use it.
The job configuration slightly adjusts with upgrades to adapt to database schema changes or additional monitoring requirements.

Express Install and Compatibility Check

This job does not support “Express Install” meaning custom configurations might be necessary during setup.
Compatibility checks ensure that the job runs on instances of SQL Server version greater than 2005, Azure SQL Managed Instances, or systems where “sp_BlitzIndex” is present and operational.

Overall, this dbWatch job ensures comprehensive tracking and analysis of index performance, aiding in efficient database management and optimization.