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 CONTENT HAS BEEN MOVED HERE.

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

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
New Comments to this post are disabled

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

Privacy Statement