Blocking detector

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and identify processes that are blocking other processes in a Sybase database environment. It specifically checks the “time_blocked” column in the system table master..sysprocesses for instances where the status is “lock sleep.”
• Why: This job is essential for maintaining the performance and stability of database operations. Prolonged blocking can lead to delays in transaction processing and can adversely affect the application performance using the database. By identifying blocking issues early, it helps in taking timely corrective actions to prevent critical performance bottlenecks.
• Manual checking: You can manually check for blocking sessions in the database by issuing the following SQL commands:

select spid, status, suser_name(suid) as login, hostname, program_name, cmd, blocked, dbid, uid, tran_name, time_blocked, priority, loggedindatetime, ipaddr from master..sysprocesses where suid>0 and status='lock sleep' order by time_blocked desc

Details of Implementation

Monitoring Scope

• This job targets instances of type ‘Sybase’ specifically where the major version is greater than 11 and the instance has an engine.

Frequency of Execution

• The job runs at many intervals throughout an hour – specifically every 5 minutes as indicated by the schedule ‘1,6,11,16,21,26,31,36,41,46,51,56 * * *’.

Dependency and Cleanup

• The job is self-dependent as it includes a clean-up mechanism for the procedure ‘dbw_blocker_check’ and the table ‘dbw_blocker_check_histr’ related to this monitoring task.

Parameters and Threshold

• A warning is triggered if a session is found to be blocked for more than 3 minutes. This job checks for and counts sessions where the blocking time has exceeded this threshold.

Result Presentation and Reporting

• A report titled “Blocking Detector” provides statistics in a tabular format, showing information from ‘master..sysprocesses’ for processes with status “lock sleep” and additional details from ‘master..syslocks’ for identified blocking sessions. Here is an outline of the data points displayed in the report:

• Additionally, the job captures and details out each blocking occurrence, particularly for the top five longest blocked processes per run cycle, providing insights into the blocked and blocking sessions.

Additional Output

• The job also includes a transformer setting to capture and log detailed messages about identified blocking issues, which can be especially useful for troubleshooting and detailed historical analysis.

On-Failure Handling

• The job includes scripts to execute on failure, such as dropping procedures and tables associated with ‘dbw_blocker_check’, ensuring clean failure handling.

Comprehensive Impact

• Overall, this job is crucial for operational continuity and efficiency of the Sybase databases, helping database administrators manage and mitigate potential disruptions due to blocking processes effectively.