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)
- Default Schedule: Runs every hour at 5 minutes past the hour.
- Acknowledgments: True – Indicates that acknowledgments are part of the job’s setup.
- 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:
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.