Monitor Blocking/Parallelis

The embedded sql code installs a SQL Server job that can be used to monitor blocking and parallelism issues. This job can only be installed on SQL Server 2000 Service Pack 3 or greater.

The job runs on a configurable 1 minute frequency and checks the master sysprocesses table for blocking issues. If blocking is found, it then records the blocking process, all blocking victim processes and any processes that are currently running queries with parallelism. It can also optionally record the associated locks but it is not recommended to enable that option and leave the job unattended.

The following 4 tables are created and populated by the job:

Table Name	Description
Local_Blocking_Log	List of the blocking processes.
Local_Blocking_Victims	List of the blocking victims processes.
Local_Blocking_Parallelism	List of processes with multiple threads at the time of the blocking.
Local_Blocking_Locks	List of the blocking process locks.

The following query is an example of how to look at the data in the table:


SELECT TOP 10	Start_Time,
                             Duration	= datediff(s, start_time, end_Time),
                             BlockingSPID   = bl.spid, 
                             BlockingProgram	= bl.program_name, 
                             BlockingObject		= so.name, 
                             BlockingInputBuffer = bl.Event_Info, 
                             BlockingText		            = bl.Text, 
                             BlockingEncrypted   = bl.Encrypted,
                             VictimSPID	                    = bv.spid,      
                             VictimProgram	= bv.program_name,
                             VictimObject	= vso.name, 
                             VictimInputBuffer  = bv.Event_Info,
                             VictimText	   = bv.Text,
                             VictimEncrypted	= bl.Encrypted
FROM dbo.local_blocking_log bl WITH (NOLOCK)
     LEFT JOIN sysobjects so WITH (NOLOCK) ON bl.Object_id = so.id
     LEFT JOIN dbo.local_blocking_victims bv WITH (NOLOCK) ON bl.bl_id = bv.bl_id 
          LEFT JOIN sysobjects vso WITH (NOLOCK) ON bv.Object_id = vso.id
ORDER BY bl.BL_Id DESC
SELECT top 10	TimeStamp,
                                SPID,
                                Program_Name, 
                                Host_Name, 
                                Text,
                                Encrypted
 FROM dbo.Local_Blocking_Parallelism bp WITH (NOLOCK)
       LEFT JOIN sysobjects so WITH (NOLOCK) ON bp.Object_id = so.id 
ORDER BY bp.Timestamp DESC, bp.ECId ASC

The job utilizes the fn_get_sql() function to retrieve the current running text of the processes (stored in the ‘Text’ field in all the tables). This usually points to a particular query, which can then be addressed to resolve the blocking.