Blocking detector
Job details
| Name: | Blocking detector |
| Platform: | Sqlserver |
| Category: | Performance |
| Description: | Checks whether there exists blocked session. |
| Long description: | |
| Version: | 1.2 |
| Default schedule: | 0,5,10,15,20,25,30,35,40,45,50,55 7-17 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version < ‘2012′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| warning threshold | 5 | The maximum number of minutes a session can be blocked before a warning is triggered. |
Job Summary
- Purpose: The purpose of this job is to monitor SQL Server instances specifically before the version 2012 for blocked database sessions. It aims to identify and alert on sessions that are being blocked in real time, preventing transactions from proceeding and potentially impacting database performance and user experience.
- Why: This job is important because it helps maintain optimal performance and availability in SQL Server Databases by identifying sessions that are blocked beyond a configurable threshold. If such conditions persist without intervention, it can lead to significant performance degradation or even result in database outages, affecting business operations.
- Manual checking: You can check for blocked sessions manually in the database by issuing the following SQL commands:
SELECT spid, blocked, waittype, waittime, dbname = DB_NAME(dbid), loginame
FROM master.dbo.sysprocesses
WHERE blocked != 0Deployment Details
This monitoring job targets SQL Server instances of specific older versions (below SQL Server 2012), which might require more frequent checks due to lack of advanced deadlock resolution in earlier SQL Server versions.
- “default-schedule” sets the frequency of this check to every 5 minutes during typical office hours (7 AM to 5 PM) suggesting a business use case where peak hours are of specific interest.
Technical Implementation
The primary stored procedure used by this job, dbw_blocker_check, implements a blocking detection mechanism where:
- A temporary table captures all currently blocked sessions.
- Cursors and loop constructs help in examining each blocked session to determine if the session’s blocked wait time exceeds a predefined threshold.
- If the number of sessions exceeds a predefined count during a given interval, an alert status would be populated by the system.
- Based on the outcome, the value is updated in dbWatch Control Center which can trigger alerts based on the severity of the blockages detected.
Tables and Dependencies
The job uses several system tables and custom procedures, including:
| Object Name | Type | Description |
|---|---|---|
| dbw_blocker_check | Stored Procedure | Main logic for the blocking detection |
| sysprocesses | System Table | Contains information about current processes and their state |
| syslockinfo | System Table | Contains information about locks, including owner and object |
| sysdatabases | System Table | Contains information about databases, referenced to obtain database names |
This information is crucial as these elements work together to ascertain which sessions are blocked and need immediate attention.
Reports and Presentation
The job generates several reports and visual presentations, such as:
- “Blocked session(s)” table displays all key details of blocked sessions like Session ID, the Session it is blocked by, Database Name, Wait Type, Wait Time, and more.
- “Lock statistics” displays detailed lock information on the blocked sessions.
- Historical Warning Report that shows all past occurrences of blocking issues raised by this job, helping in trend analysis and historical performance assessment.
Overall, this monitoring job is pivotal for maintaining high performance in legacy SQL Server environments by timely addressing the blocking issues in database sessions.