Database blocking monitoring

Features

• Blocking session Identification - Instantly view sessions that are causing blocking in the SQL Server environment.

• SPID Hierarchy analysis - View relationships between blocking and blocked sessions using SPID and Parent SPID.

• Wait Resource tracking - See the exact resource (e.g., locks) that the session is waiting on.

• SQL Text visibility - Analyze the SQL statement currently being executed, to diagnose blocking causes.

• Wait time and Duration insight - Measure how long the blocking has been occurring, helping prioritize resolution.

• Visual overview - Easy-to-read tabular format highlights blocking chains and durations.

Database blocking sessions

Form: System administration \ Inquiries \ Database \ Database blocking sessions

SPID	Session Process ID in SQL Server. Identifies the session causing or experiencing blocking.
Program name	Name of the client program or context associated with the SPID (e.g., axBatch, axOnline).
Parent SPID	SPID of the parent session, if the session was spawned by another process.
Host name	Name of the client machine or server initiating the session.
Hierarchy	Indicates session blocking hierarchy or chain.
Wait time	Time in milliseconds the session has been waiting due to blocking.
Wait resource	The specific SQL Server resource (e.g., page, lock) being waited on.
SQL text	The exact SQL query being executed during the block.
Duration (ms)	Total duration of the blocking situation in milliseconds.

Steps to identify blocking chains and resolve issues

Step 1: Monitor SPIDs Look for sessions listed under SPID that appear at the top of the blocking chain.

Step 2: Check Wait time and Duration Identify sessions with long wait times to prioritize resolution efforts.

Step 3: Examine SQL text Review the SQL query being run by the blocking SPID to assess efficiency or indexing issues.

Step 4: Evaluate wait resource Use the 'Wait resource' field to determine what type of lock or block is occurring (e.g., page locks, table locks).

Step 5: Analyze Parent SPID and Hierarchy Understand whether the block is caused by a background process, user session, or another batch.

Step 6: Use Host name Trace the session back to a specific user or machine to coordinate investigation or intervention.

Step 7: Clear or kill blocking sessions (if necessary) Use SQL tools or LCS to terminate sessions that are unnecessarily blocking others.