Sunday, July 31, 2011

Identifying the blockers in SQL Server 2005 and 2008

Problem

In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID’s in SQL Server 2005 and later versions?  Is there only one way to find out which spids are blocking?  Are there any commands that I can run against multiple SQL Server versions?  Check out this tip to learn more about locking and blocking.

Solution

Whenever a user contacts the DBA team indicating a processes looks hung or a process is not proceeding checking the applicable database blocking makes a great deal of sense. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. This forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.  For more information about locking and blocking review these tips: Understanding SQL Server Locking and Understanding SQL Server Blocking.
There are number of ways to find out the details of the system processes IDs (spids) involved in blocking. I have tried to cover some of the options in this tip to include:
  • sp_who2 System Stored Procedure
  • sys.dm_exec_requests DMV
  • Sys.dm_os_waiting_tasks
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler

sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active’ parameter.  Below is some sample code and a screen shot with showing process 55 being blocked by process 54.
USE Master
GO
EXEC sp_who2
GO
Additional resources:

sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.
USE Master
GO
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO
Additional resources:

sys.dm_os_waiting_tasks DMV

The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
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
Additional resources:

SQL Server Management Studio Activity Monitor

If you are more comfortable using SQL Server Management Studio to review locking and blocking as opposed to querying system objects or executing stored procedures, you are in luck.  There are even a few different tools in SQL Server Management Studio you can use.  The first option is the Activity Monitor, which can be accessed by navigating to the instance name | right click | select 'Activity Monitor'.  To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.
Additional resources:

SQL Server Management Studio Reports

The second option in SQL Server Management Studio to monitor blocking is with the standard reports, which can be accessed by navigating to the instance name | right click | Reports | Standard Reports | Activity - All Blocking Transactions.  Once again, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

Additional resources:

SQL Server Profiler

To capture blocking related data on a continuous basis, one option is to run SQL Server Profiler and save the data to a table or file for analysis purposes.  In order to configure Profiler to capture blocking related data, execute Profiler, configure the general properties then navigate to Event Selection tab | select Show all events | Errors and Warnings | check the Blocked process report and then run the application.  In addition, be sure to configure the 'blocked process threshold' before you start Profiler using this code:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO 
sp_configure 'blocked process threshold', 20
GO 
RECONFIGURE 
GO
Ref: http://www.mssqltips.com/tip.asp?tip=2429
Posted by at
Labels: ,