sp_BlitzWho

Job details

Name:	sp_BlitzWho
Platform:	Sqlserver
Category:	Performance extension (Brent Ozar)
Description:	Runs the sp_BlitzWho procedure (Brent Ozar) which, among other things, shows blocking sessions
Long description:	Runs the sp_BlitzWho procedure (Brent Ozar) which, among other things, shows blocking sessions.
Version:	1.3
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 6-18 * *
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_BlitzWho’]/instance

Parameters

Name	Default value	Description
location of dbo.sp_BlitzWho procedure	master	Name of the database where the dbo.sp_BlitzWho procedure is located.
warning threshold	5	The maximum number of minutes a session can be blocked before a warning is triggered.
alarm threshold	30	The maximum number of minutes a session can be blocked before an alarm is triggered.
@ShowSleepingSPIDs	0	@ShowSleepingSPIDs
@OutputDatabaseName	BlitzWho	If you want to output information to a specific database. This is input parameter to the sp_BlitzWho procedure.
@OutputSchemaName	dbo	Specify a schema name to output information to a specific Schema. This is input parameter to the sp_BlitzWho procedure.
@OutputTableName	BlitzWho	Specify table name to output information to a specific table. This is input parameter to the sp_BlitzWho procedure.
delete old output	YES	If set to “YES“ (default) old statistics from previous executions of sp_BlitzWho procedure will be deleted.
max history records	100	Maximum number of records in the history table tab_histr_sp_blitzwho.

Job Summary

Purpose: This monitoring job is designed to execute and monitor the sp_BlitzWho stored procedure by Brent Ozar to identify blocking sessions in a SQL Server environment, providing a deeper insight into various session-related details.
Why: Monitoring blocking sessions is crucial for maintaining database performance and ensuring efficient resource utilization. If not addressed, blocking sessions can lead to transaction delays and degrade overall system performance.
Manual checking: To manually check for blocking sessions, administrators can execute the stored procedure sp_BlitzWho directly on the SQL Server instance:

EXEC sp_BlitzWho;

Job Details

Procedure Execution: The job involves running the dbo.sp_BlitzWho stored procedure, which provides detailed information about each session, including any that are blocking others.
Frequency and Timing: It’s scheduled to run at 5-minute intervals between 6 AM and 6 PM, ensuring regular updates during typical working hours.
Dependencies and Cleanup: The job has dependencies on certain SQL objects like stored procedures and tables used in logging and tracking execution history, which are cleaned up upon installation failures.
Warning and Alarm Thresholds: The job uses thresholds for warnings (5 minutes) and alarms (30 minutes) to categorize the severity of blocking based on the duration.
History Management: Details about the blocking sessions are maintained in historical tables, with the old execution data optionally being cleared out based on configuration to manage space effectively.

Execution and Logging

History Table Creation: The job includes SQL scripts for creating tables to store historical data about previous runs and blocked sessions.
Detailed Execution Tracking: Implemented procedures capture all parameters and execution details, storing them in designated log tables.
Error Handling and Diagnostics: The job includes comprehensive error handling strategies and updates operational status logs to reflect execution details or errors encountered.
Parameter Configurations: Parameters such as database, schema, and table names are configurable, allowing flexibility depending on the deployment environment.

Reporting and Output

Purpose: The reports generated from dbWatch job help in identifying trends or recurring blocking issues, aiding DBAs in tuning and optimization efforts.
Blocked Sessions Report: Includes up-to-date information on the last 50 blocked sessions including session IDs, waiting times, and associated user details.
Execution History Reporting: Tracks and displays previous execution metrics such as run times and the volume of database activities logged, offering insights into the pattern and frequency of blocks recorded over time.

Upgrade Considerations

Version Compatibility: The job is designed to work with SQL Server instances where the version is greater than 2005, including Azure SQL Managed Instances.
Table Adjustments: Updates may include alteration or enhancement of tables like adding new columns to store additional metadata or historical data pruning strategies to manage table size and performance impact.

This comprehensive setup allows database administrators to monitor critical performance metrics effectively, and address potential issues proactively, ensuring stable and efficient database operations.