How to identify blocking in SQL Server

By:   |   Updated: 2022-12-19   |   Comments (26)   |   Related: 1 | 2 | 3 | 4 | > Locking and Blocking


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? Is there only one way to find out which spids are blocking? Are there commands 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 process looks hung or a process is not proceeding, checking for database blocking makes a great deal of sense. Blocking happens when one database connection holds a lock and a second connection needs a lock on the same obect. 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
  • SQL Server Extended Events

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 stored procedure can be filtered to return only the active processes by using the 'active' parameter.

Below is sample code and a screen shot showing process 55 is being blocked by process 54.

USE master
GO
EXEC sp_who2
GO
sp_who2 system stored procedure in sql server

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
sys.dm_os_tasks dmv in sql server

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 clicking on the instance name and selecting 'Activity Monitor'. To view the Activity Monitor in SQL Server, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

ssms activity monitor
using ssms to review locking and blocking

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 clicking on the instance name and selecting Reports > Standard Reports > Activity - All Blocking Transactions. Once again, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

ssms reports
sql server 2008 activity-all blocking transactions

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. Launch Profiler and connect to the SQL Server instance. On the Events Selection tab, click on Show all events. Navigate to the Errors and Warnings section, check the Blocked process report and any columns you want to return and then click Run. In addition, be sure to configure the 'blocked process threshold' before you start Profiler using the code below.

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

Additional resources:

Extended Events Session

You can do something similar to Profiler using Extended Events. The following screen shows you the event to capture.

sql server extended events

Here is a sample of the output you will get from the blocked_process_report_filtered event.

sql server extended events
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 if absolutely necessary, etc.
  • Check out these tips to learn more about locking and blocking


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nitansh Agarwal Nitansh Agarwal is a lead with 4+ years of extensive experience in database administration where he handles large critical databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-12-19

Comments For This Article




Tuesday, September 17, 2019 - 10:46:32 AM - Greg Robidoux Back To Top (82482)

Hi Viswanath,

You can add this to your WHERE clause 

AND database_id NOT IN (1,2,X,X)

Change the values in the NOT IN to the databases you want to exclude.  You can get a list using 

sp_helpdb

Tuesday, September 17, 2019 - 9:48:19 AM - Viswanath Back To Top (82479)

I am using below code to find blocking session id but I want to exclude some databases from checking the blocking alerts. Could someone help how to filter on that ?

SELECT session_id AS Blocked_Session,  

       blocking_session_id,  

       start_time,  

       wait_time,  

       floor(wait_time / (1000 * 60)) % 60 AS Blocking_Time_Min,  

       floor(wait_time / (1000 * 60 * 60)) % 24 AS Blocking_Time_Hour,  

       wait_type,  

       last_wait_type,  

       wait_resource,  

       lock_timeout  

  FROM [master].[sys].[dm_exec_requests]  

 WHERE blocking_session_id <> 0

Thursday, July 30, 2015 - 11:22:28 AM - sathya Back To Top (38323)

Yes it was useful.

Friday, July 18, 2014 - 7:35:54 AM - katukuri Back To Top (32771)

hi.

thanks for the article.

I have a requirement on deadlock issue.

how is it possible to send an alert if deadlock arises [SQLSERVER.LOCKS >0] for all instances and send all detail information of the possible lock to my mail even if the windows OS reboots.

what could be approach after creating an alert for the locks.

what jobs can be added for the alert.

thanks in advance.

Tuesday, July 23, 2013 - 8:54:20 AM - Teja Back To Top (25961)

Hi,

Thanks for the article which is very helpful for the beginners like me. I was struggling with some blockings. Can you please help me in that.

There is a small update query which updates all the columns of a particular ROW in a table(Table will have ~20 rows). But that query is blocking. I got the below query from the blockinglogs from a DBA.

curstmt:  UPDATE SystemJobTable SET Name = @p0, Description = @p1, Recurrence = @p2, LastStartDate = @p3, LastEndDate = @p4, NextStartDate = @p5, TypeName = @p6, Status = @p7, Enabled = @p8, Server = @p9, ErrorDetails = @p10, Parameters = @p11, CategoryId = @p12, Created = @p13, CreatedBy = @p14, Modified = @p15, ModifiedBy = @p16 WHERE Id = @p17

inputbuffer: (@p0 nvarchar(19),@p1 nvarchar(43),@p2 int,@p3 datetime,@p4 datetime,@p5 datetime,@p6 nvarchar(85),@p7 int,@p8 bit,@p9 nvarchar(14),@p10 nvarchar(4000),@p11 nvarchar(4),@p12 int,@p13 datetime,@p14 int,@p15 datetime,@p16 int,@p17 int)UPDATE SystemJobTable SET Name = @p0, Description = @p1, Recurrence = @p2, LastStartDate = @p3, LastEndDate = @p4, NextStartDate = @p5, TypeName = @p6, Status = @p7, Enabled = @p8, Server = @p9, ErrorDetails = @p10, Parameters = @p11, CategoryId = @p12, Created = @p13, CreatedBy = @p14, Modified = @p15, ModifiedBy = @p16 WHERE Id = @p17

Can you please look into this.

Thanks,

Teja

Wednesday, July 17, 2013 - 6:01:47 AM - slapshot Back To Top (25874)

Do you know how I can identify the actual windows username of the person causing the blocking (assuming it is a person and not some automated process)?  It would be hand to be able to identify the person and ask them what is happening on their screen or what they have done to cause the lock (ask them to click on the ok button on the error message flashing on their screen?).

Wednesday, June 19, 2013 - 11:02:00 AM - Ed - sqlscripter Back To Top (25487)

/*

Here is a old way to find the blocking longer then 6 min. 

Pulled from a news group this code only works if there is 1 block, returns error 512

IF    (select datediff(mi,last_batch,getdate()) 
  from master..sysprocesses
 where blocked <> 0)>6

*/

--New re-written code works regardless of how many blocks tested

Declare @test int
 select @test = count(*) 
  from master..sysprocesses
 where blocked <> 0 and datediff(mi,last_batch,getdate()) > 6
 if (@test = 0)
begin
print 'no blocking'
return
  end
 else
--Pager block code
Begin

Pager code here or buffer dump code to dump lead blockers info

End

 

Saturday, May 18, 2013 - 3:39:15 AM - AJAY Back To Top (24026)

 

 

VERY CLEAR EXPLANATION THANKS A LOT TO THAT TEAM,

 

 

 

Monday, April 1, 2013 - 2:20:42 AM - Vijay Back To Top (23095)

Very help ful for primary info about blocking

thankyou

Monday, March 25, 2013 - 6:18:27 AM - Johan Back To Top (22997)

Hi Everyone

 

First of all thank you for the great article!!

I am also struggling with locks all of a sudden and I found this article very helpful:

http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx

 

Good luck to one and all!

Thanks

Johan

Wednesday, January 16, 2013 - 9:04:31 PM - NItansh Back To Top (21500)

@Kalpesh: As a long term solution application code has to be looked upon. As a short term solution identify the proccesses involved in deadlock and do not run them simultaneously, identify and add missing indexes, add query hints like 'NOLOCK' with Select queries.

 

Wednesday, January 16, 2013 - 6:18:49 PM - Kalpesh Back To Top (21497)

We have windows application with WCF services. We are using SQL 2008, now a days we are frequently facing dead lock issue,after restarting the services we able to resovle issue. But deadlock occuring frequently which stops the application. Can you suggest how resolve the dead lock issue. Thanks.

Thursday, December 27, 2012 - 9:53:41 AM - Greg Robidoux Back To Top (21158)

@Raghu - the blocking should get resolved by itself.  The other processes have to just wait until the first process completes.  If the first process never completes you would have to kill that process in order for the other processes to continue.

Thursday, December 27, 2012 - 9:30:16 AM - Raghu Back To Top (21157)

how will resolve the user without killing the spid


Friday, September 7, 2012 - 2:15:18 PM - Rajesh Back To Top (19436)

Hi Nitansh,

I am facing a block in our Database.

In the Profiler i am getting several lock:escalation. Mode : 5 - X. Told the Dev about it.

They want me to pin point le query causing it. Is there a way i can pin point the query using the Object ID givin by the Lock:escalation ?

What is very peculiar about this lock that cause a hang for exactly 10 min eveytime. The Semaphore is released evytime after ten minutes. that wat the dev team tells me.

Do you have any suggestions ?

 

Monday, July 16, 2012 - 11:40:32 PM - Nitansh Back To Top (18554)

@Ramesh

 

You have to wait for one processes to complete and release the locks.

Monday, July 16, 2012 - 3:12:39 AM - ramesh jeshwar Back To Top (18536)

 

If there are two important processess / requests which are blocked by each other. I don't want to kill any of the two. What is the solution to fix this issue. Thanks.

Thursday, March 29, 2012 - 9:44:34 AM - Ankit Shah Back To Top (16702)

@ananda

KILL is the best option to reduce or clear blocking 

Thursday, March 29, 2012 - 1:52:39 AM - ananda Back To Top (16692)

Can you please tell, how to reduce or clear the blocking session? Does using kill command with SPID or other way ?

 

Friday, January 27, 2012 - 9:37:24 AM - Ankit Shah Back To Top (15804)

you can find also from..

select * from sys.sysprocesses where blocked =1

Sunday, December 11, 2011 - 4:29:05 PM - James Back To Top (15332)

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?

 

Sunday, July 17, 2011 - 11:22:30 PM - Nitansh Back To Top (14179)

Thanks Ary.

Sunday, July 17, 2011 - 11:21:54 PM - Nitansh Back To Top (14178)

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 - 4:19:48 AM - Arya Hafshejani Back To Top (14176)

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.

Friday, July 15, 2011 - 2:40:18 AM - Jay Back To Top (14174)

1 more question... Can we use blocking trace with database tuning advisor? Is DTA will help us in reducing blocking?

Friday, July 15, 2011 - 2:37:57 AM - Jay Back To Top (14173)

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?














get free sql tips
agree to terms