Blocking statistics
Job details
| Name: |
Blocking statistics |
| Platform: |
Sqlserver |
| Category: |
Performance |
| Description: |
Checks whether there exists any blocked sessions. |
| Long description: |
|
| Version: |
1.9 |
| Default schedule: |
0,5,10,15,20,25,30,35,40,45,50,55 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name |
Default value |
Description |
| 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. |
Job Summary
Purpose
- Monitors and reports on blocked session statistics in SQL Server databases.
- Detects any sessions that may be causing blocks and gathers information to aid in diagnosing issues that can lead to application slowness or deadlocks.
- Helps in proactive management by providing alerts on critical thresholds being reached, specifically warning and alarm levels based on the duration a session has been blocked.
Importance
- Ensures high availability and performance of database operations by minimizing disruptions caused by blocked sessions.
- Alerts database administrators to potential problems before they impact user operations, allowing for quicker resolution and less downtime.
- Enhances the ability of database professionals to maintain smooth operational continuity.
Manual Checking
- Database administrators can manually check for blocked sessions by executing the following SQL command:
SELECT * FROM master.dbo.sysprocesses WHERE blocked != 0;
Implementation Details
| Table Name |
Description |
| dbw_blocked_tab |
Temporary storage for current blocked sessions |
| dbw_blocked_histr_tab |
History storage for blocked sessions |
| dbw_blocking_tab |
Temporary storage for sessions that are causing the blocking |
| dbw_blocking_histr_tab |
History storage for sessions that have caused blocking |
Procedures Used
- The main stored procedure for this task is dbw_blocked_proc. It handles session checking, logging in tables, and alerting.
- Supports both warning thresholds (indicating a potential issue) and alarm thresholds (indicating a serious issue needing immediate attention).
Dependencies and Maintenance
- Depends on several system objects including master.dbo.sysprocesses and master.dbo.syslockinfo for session and lock information.
Upgrade Notes
- Version upgrades include schema checks and adjustments to ensure functionality remains aligned with system and software updates.
- Continuous monitoring script ensures all blocked session data is properly logged and aged out to maintain system performance and data relevance.
Reporting Features
- The system generates reports showing blocked session details including session ID, the session it is blocked by, wait times, and other crucial diagnostic information.
- Reports aim at simplicity and clarity to assist quick decision-making.
Example Report Query for Blocked Sessions:
SELECT t1.spid as "Session ID", t1.blocked as "Blocked by", t2.name as "Database", t1.waittime as "Wait Time" FROM master.dbo.sysprocesses t1 JOIN master.dbo.sysdatabases t2 ON t1.dbid = t2.dbid WHERE t1.blocked != 0 ORDER BY t1.waittime DESC;
Scheduling
- Runs at a default schedule of every 5 minutes.
- Configurable based on database activity and administrator preferences to either more frequent checks during high traffic periods or less during known low usage times.
Acknowledgements
- dbWatch Control Center utilizes a comprehensive approach to monitoring, with a focus on reliability, scalability, and minimal overhead on the target systems.