In a SQL Server environment, it is not uncommon to encounter locking and blocking issues. When a process appears to be hung or not progressing, it is important to identify if blocking is the cause. Blocking occurs when one database connection holds a lock on an object, preventing another connection from acquiring the same lock and causing it to be blocked until the first connection completes.

There are several ways to find out which SPIDs (System Process IDs) are involved in blocking. Here are some options:

1. sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes, including the associated users, application, database, and CPU time. By using the ‘active’ parameter, you can filter the results to show only active processes. Here is an example:

USE master
GO
EXEC sp_who2
GO

This will display a list of processes, and you can identify blocking by checking the “BlkBy” column.

2. sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV (Dynamic Management View) provides details on all processes running in SQL Server. By filtering the results to show only blocked processes (where the blocking_session_id is not 0), you can identify the blocking SPIDs. Here is an example:

USE master
GO
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

3. sys.dm_os_waiting_tasks DMV

The sys.dm_os_waiting_tasks DMV returns information about tasks that are waiting on resources. By checking the “blocking_session_id” column, you can identify the blocking SPIDs. Here is an example:

USE master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0;
GO

4. SQL Server Management Studio Activity Monitor

If you prefer a graphical interface, you can use the Activity Monitor in SQL Server Management Studio. Simply navigate to the instance name, right-click, and select “Activity Monitor”. This tool provides a visual representation of blocking processes.

5. SQL Server Management Studio Reports

Another option in SQL Server Management Studio is to use the standard reports. Navigate to the instance name, right-click, select “Reports”, then “Standard Reports”, and finally “Activity – All Blocking Transactions”. These reports provide detailed information about blocking transactions.

6. SQL Server Profiler

To capture blocking-related data continuously, you can use SQL Server Profiler. Launch Profiler, connect to the SQL Server instance, select the desired events (such as the Blocked process report), and run the trace. Make sure to configure the “blocked process threshold” before starting Profiler. Here is an example:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO 
sp_configure 'blocked process threshold', 20
GO 
RECONFIGURE 
GO

7. Extended Events

Extended Events can be used as an alternative to Profiler. You can capture blocking-related events and analyze the output. Here is an example:

[Screenshot of Extended Events configuration]

By using these methods, you can easily identify blocking and blocked SPIDs in your SQL Server environment, allowing you to take appropriate actions to resolve the issue.

Remember, understanding locking and blocking is crucial for maintaining data integrity and ensuring optimal performance in SQL Server.

Article Last Updated: 2022-12-19