THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)

This post is part 19 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.


Debugging a blocking situation can be a complex process. First you need to figure out who’s doing the blocking, and who’s getting blocked. Next—before you can actually debug things—you need to figure out what the lock is that’s actually causing the blocking to occur.

This second phase has been known to cause many a DBA to rip out numerous hairs. First comes a visit to the locks DMV, followed by an extended period of research. What is a [resource_associated_entity_id]? Does that have any bearing on the [request_owner_lockspace_id]? And do you even care? If you’re anything like me, most of the time you just want to move on with life.

Who is Active solves this problem in two distinct ways, the first of which I’ll cover in today’s post, and the second a few posts down the road. Which method you choose depends on how much context you need to solve the problem at hand, and how much pressure you’re willing to put on your SQL Server instance. Today’s method is much lighter weight in terms of resource consumption and, in many cases, elapsed time.

Creating a blocking situation is easy enough... In one window do:

USE AdventureWorks
GO

BEGIN TRAN
GO

UPDATE TOP(10) Sales.SalesOrderDetail
SET
    OrderQty += 7
GO

And in a second:

USE AdventureWorks
GO

SELECT *
FROM Sales.SalesOrderDetail
GO

Who is Active figures things out easily enough in its default mode:

F19_01_blocked

...but what if things weren’t quite so cut-and-dry and you needed a bit more information? In this case, you could enable two options that have been covered in recent posts: @get_task_info = 2 and @get_additional_info = 1. Who is Active uses these two options together to populate the [additional_info] column with information about what object is actually causing the blocking situation to occur:

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_additional_info = 1

F19_02_results

Clicking on the [additional_info] column for the blocked session reveals...

F19_02_additional_info

...full information about the blocked object. This particular lock is a page lock on the Sales.SalesOrderDetail table (go figure). Information can be resolved for virtually all types of locks, and generally speaking this mode does the work extremely quickly, so it should not add a lot of overhead to your monitoring sessions.

 

Homework

Like other features in Who is Active that need to visit various user databases, this mode uses an exception handler in case things don’t go as planned. This will display an error message in a child node of <block_info>. Can you figure out how to make an error message appear using the shortest possible script?

Published Tuesday, April 19, 2011 11:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

This post is part 20 of a 30-part series about the Who is Active stored procedure. A new post will run

April 20, 2011 10:32 AM
 

Adam Machanic said:

This post is part 22 of a 30-part series about the Who is Active stored procedure. A new post will run

April 22, 2011 4:31 PM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

IJeb Reitsma said:

Thanks for these series of high quality posts.

This very usefull feature is somewhat hidden, although it is very well documented. I tried to find out why the first query of post 17 was blocked by the following query of post 14 and the first one was not.

UPDATE TOP(10) Sales.SalesOrderDetail SET OrderQty += 7

I expected it to see together with the locking information, but this only shows the GRANTED locks, not the locks with status WAIT.

In the case of a keylock I also miss information about the actual record(s) that cause the blocking.

May 31, 2011 1:24 PM
 

IJeb Reitsma said:

Sorry for my previous post. After going to post 22 I noticed that the locks xml also shows locks with request status "WAIT".

May 31, 2011 5:15 PM
 

Adam Machanic said:

Hi Ijeb,

Glad to hear that it worked out! Enjoy using Who is Active :-)

--Adam

May 31, 2011 5:32 PM
 

Gilles said:

Hi,

Have used the dbo.sp_WhoIsActive

               @get_locks=1,

               @get_additional_info = 1

to find out why one of my session was blocked by another one.

i actually run the following:

WHILE (1=1)

BEGIN

EXEC dbo.sp_WhoIsActive

               @get_plans=1,

               @get_locks=1,

               @get_additional_info = 1

WAITFOR DELAY '00:00:01'

END

and as expected i obtained the following results:

session 52 has a blocking session 53 (for multiple 10 of seconds) BUT when i look at the additional_info XML column for the blocked session 52 i do not see any 'block_info' node. It just tells me that the transaction_isolation_level is 'Serializable' and the command_type is 'UPDATE'.

If i look at the locks XML column for the blocked session 52, i see only 'GRANT' locks, not a single 'WAIT' one...

I was expecting to have at least one WAIT lock to explain why I am blocking for multiple seconds.

I should add that if i change the application code to not require the session 52 to be using the Serializable isolation mode and downgrade it to 'ReadCommitted', no long blocking is experienced.

Am sure am missing or not understanding something here. Do you have a suggestion?

Thank you.

Gilles.

July 19, 2012 7:52 PM
 

Gilles said:

actually i had forgotten to use the @get_task_info=2, it now shows a <block_info> XML node with a keylock lock_type. Should i conclude that because the blocked session 52 is using a Serializable isolation mode, it is blocking as a single row lock has been put by session 53...then still why can't we see some WAIT locks in the lock column?

Thanks again.

Gilles

July 19, 2012 8:06 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement