sp_BlitzLock

Job details

Name:	sp_BlitzLock
Platform:	Sqlserver
Category:	Performance extension (Brent Ozar)
Description:	Runs the sp_BlitzLock procedure (Brent Ozar) which checks for and analyzes deadlocks from the system health session or a custom extended event path
Long description:	Runs the sp_BlitzLock procedure (Brent Ozar) which checks for and analyzes deadlocks from the system health session or a custom extended event path
Version:	1.4
Default schedule:	5 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]/blitz_obj_id/obj_name[id=‘sp_BlitzLock’]/instance

Parameters

Name	Default value	Description
location of dbo.sp_BlitzLock procedure	master	Name of the database where the dbo.sp_BlitzLock procedure is located.
@Top	20000	Use if you want to limit the number of deadlocks to return. This is ordered by event date ascending. This is input parameter to the sp_BlitzLock procedure.
return status when deadlocks found	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when new deadlocks found.
@DatabaseName		If you want to filter to a specific database. This is input parameter to the sp_BlitzLock procedure.
@StartDate	19000101	The date you want to start searching on. This is input parameter to the sp_BlitzLock procedure. During installation this value is set to current date – 7 days. After execution the value is set to current date.
@EndDate	99991231	The date you want to stop searching on. This is input parameter to the sp_BlitzLock procedure.
@ObjectName		If you want to filter to a specific object. The object name has to be fully qualified Database.Schema.Table. This is input parameter to the sp_BlitzLock procedure.
@StoredProcName		If you want to search for a single stored procedure. The procedure name has to be fully qualified Database.Schema.Sproc. This is input parameter to the sp_BlitzLock procedure.
@AppName		If you want to filter to a specific application. This is input parameter to the sp_BlitzLock procedure.
@HostName		If you want to filter to a specific host. This is input parameter to the sp_BlitzLock procedure.
@LoginName		If you want to filter to a specific login. This is input parameter to the sp_BlitzLock procedure.
@OutputDatabaseName		If you want to output information to a specific database. This is input parameter to the sp_BlitzLock procedure.
@OutputSchemaName	dbo	Specify a schema name to output information to a specific Schema. This is input parameter to the sp_BlitzLock procedure.
@OutputTableName	BlitzLock	Specify table name to output information to a specific table. This is input parameter to the sp_BlitzLock procedure.
delete old output	YES	If set to “YES“ (default) old statistics from previous executions of sp_BlitzLockp procedure will be deleted (including statistics from table BlitzLockFindings).
max history records	500	Maximum number of records in the history table tab_histr_sp_blitzLock

Job Summary

Purpose: The purpose of this job is to monitor and analyze deadlocks in SQL Server instances using the “sp_BlitzLock” procedure developed by Brent Ozar. It specifically targets SQL Server instances of version greater than 2008 and checks deadlocks either from system health sessions or a custom extended event path, cataloging events for reporting and analysis.

Why: This job is critical for maintaining database performance and integrity by quickly identifying and addressing deadlock issues, which, if unchecked, can significantly impact user experience and system stability.

Manual checking: You can check for deadlocks manually in the SQL Server databases by issuing the following command depending on your environment (system_health session or extended events):
bc. EXEC sp_BlitzLock @Top = 20000; (modify as necessary for specific filters)

Details of the Monitoring Job

Category: Performance Extension (Brent Ozar)

Version: 1.4

Default Schedule: Runs every hour at 5 minutes past the hour.

Acknowledgments: True – Indicates that acknowledgments are part of the job’s setup.

Company: dbwatch.no

Dependencies:
- Main Procedure: proc_run_sp_blitzlock

Parameters and Configurations:
- Purpose of each parameter is well defined, including locations (database/schema) of procedures and tables, various filters (date, object, app, etc.), and configuration settings for the job’s outputs and cleaning tasks.

Implementation Code Breakdown

Code Overview:
- Creation of necessary tables for storing deadlock history and execution details.

Database Modifications and Scripts Execution: The code block includes comprehensive SQL scripts to manage:
- Procedure execution

Upgrade and Maintenance

From Versions: 1.1, 1.2, 1.3

Upgrade Path: Includes validation checks and enhancements such as additional parameter setups and column additions in history tables to ensure proper recording and processing of historical deadlock information.

This job is vital for system administrators and database professionals wanting to keep a proactive watch on potential deadlock issues, offering insights and automated management directly within the dbWatch environment. The outputs are structured to provide clear investigative paths, thus reducing the resolution time of deadlock-related disruptions.