How to identify blocking in SQL Server 2005 and 2008

By: Nitansh Agarwal | Read Comments (7) | Print Nitansh is a lead with 4+ years of extensive experience in database administration where he handles large critical databases. Related Tips: More

Problem

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:

• SQL Server Command Line Tools To Manage Your Server
• Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
• Killing a SPID and Checking Rollback progress in SQL Server

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:

• How to isolate the current running commands in SQL Server
• Identifying the input buffer in SQL Server 2000 vs SQL Server 2005
• Finding a SQL Server process percentage complete with dynamic management views

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 Tempdb Usage and Bottlenecks tracked with Extended Events

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:

• Performance Analysis Using SQL Server 2008 Activity Monitor Tool

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:

• Built-In Performance Reports in SQL Server 2005

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 

Additional resources:

• Tip category: Profiler and Trace
• Capturing SQL Server Deadlock Information in XML Format
• Finding and troubleshooting SQL Server deadlocks
• SQL Server Profiler Graphical Deadlock Chain

Next steps

• If you are faced with a blocking situation, be sure to consider all of your options in the short and long term. To resolve the immediate issue, you may need to KILL some spids, but to resolve the issue you may need to change your database design, change your data access, add NOLOCK hints to particular queries, etc.
• Check out these tips to learn more about locking and blocking
  • Understanding SQL Server Locking
  • Understanding SQL Server Blocking
  • Tip category: Locking and Blocking Tips

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Friday, July 15, 2011 - 2:37:57 AM - Jay
Few questions!!! Can you please explain what kind of locks responsible for blocking? What should we have to do for the minimum blocking? Does NoLock hint help in reduing blocking? What are pros and cons of that? Any idea when blocking becomes deadlock?

Friday, July 15, 2011 - 2:40:18 AM - Jay
1 more question... Can we use blocking trace with database tuning advisor? Is DTA will help us in reducing blocking?

Sunday, July 17, 2011 - 4:19:48 AM - Arya Hafshejani

Thanks for your article, full of good info there. I often find that blocking happens in a series of SPIDs & that you need to identify the root blocker. Killing this process usually resolves the issue but before I kill the process I always do a DBCC INPUTBUFFER(SPID) to see the query or stored proc that is causing the issue. In this way I can have a look at the offending stored procedure or query & get back to the vendor or developers with possible improvements.

Sunday, July 17, 2011 - 11:21:54 PM - Nitansh

Hi Jay,   Please find answers below:   Can you please explain what kind of locks responsible for blocking? A blocking lock occurs when one processes causes another process to wait. As soon as blocking process is completes, it releases locks and then the blocked processes can resume. So any lock that is holding resources can be responsible for blocking. Some common types are update locks, schema modification locks, exclusive locks, a shared lock is compatible with another shared lock but not with and update or exclusive lock.   What should we have to do for the minimum blocking? A infrequent blocking is ok but if the blocking is very frequent than you have to modify the application design or changing the parallel execution of queries to sequential will help.   Does NoLock hint help in reduing blocking? What are pros and cons of that? No lock definitely helps to reduce blocking but you are at risk of reading uncommitted data since no locks ignores any locks and reads directly from tables.   Any idea when blocking becomes deadlock? The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock.   Can we use blocking trace with database tuning advisor? Is DTA will help us in reducing blocking? Yes you can and DTA recommendations will definitely help.

Sunday, July 17, 2011 - 11:22:30 PM - Nitansh
Thanks Ary.

Sunday, December 11, 2011 - 4:29:05 PM - James
Jay,   I don't see the Activity Monitor option in my SSMS 2005 on a 2005 server, but I do see it when running SSMS 2008. Is there a ''plugin'' I have to install on my SSMS 2005 in order for the option to show up?

Friday, January 27, 2012 - 9:37:24 AM - Ankit Shah
you can find also from.. select * from sys.sysprocesses where blocked =1

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code