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.

Leader of the Block (A Month of Activity Monitoring, Part 23 of 30)

This post is part 23 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.


Oftentimes blocking situations are a bit more complex than one session blocking some other request. In busy systems blocking chains can build up that include dozens or even hundreds of requests, all waiting on one another. And all are waiting as a result of one top-level blocker: the block leader. In many cases fixing the blocking problem means fixing whatever it is that the block leader is doing, so identifying it quickly is a key part of debugging blocking situations.

Finding the block leader is a fairly simple process once you realize that blocking is effectively a hierarchy. The process involves starting with all of the requests that aren’t blocked and walking down the blocking chain until the leaf level is reached—blockees that lack any more downstream blockees. At each level, a number is recorded to figure out the blocking depth. As an added benefit, a second pass can be made to reverse the number at the end of the process—this shows the total number of downstream blockees for each blocker.

While this is relatively easy to implement using a Common Table Expression, it’s certainly not something that users should have to reinvent each time it’s needed. So Who is Active exposes an option, @find_block_leaders, that does the work for you. This option adds a new column to the output, called [blocked_session_count], which reflects the total blockee count. Higher numbers mean more sessions blocked downstream; the sessions with the highest numbers are your block leaders, and these are the ones you want to focus on.

To see this in a bit more detail, run the following batch in four sessions:

USE AdventureWorks
GO

BEGIN TRAN
GO

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

Assuming that nothing else is running, the first session will complete. The other three will block. Who is Active reports this, of course, even in its default mode:

F23_01_blocked

The initial update was run on session 53, which is blocking session 54. Both 55 and 56 are reported as blocked by 54, although in reality they’re being blocked indirectly by 53. This case, while more complex than most of the blocking examples used in this series, is still simpler than many of the things seen on average production systems. None the less, it’s enough to show the power of the Who is Active option that this post is about...

EXEC sp_WhoIsActive
    @find_block_leaders = 1

F23_02_leaders

The [blocked_session_count] clearly shows which session is causing the issue in this case: 53 has three downstream blockees, whereas 54 has only two, and the other two sessions have none.

In this case, because I happened to run the batches in the correct order, the data is returned by default with the block leader sorted on top. That may or may not always be the case in a real system, but it’s quite a useful thing when there are numerous active requests and you want the most important ones right at the top. To accomplish that, use the output ordering feature—which will be covered in detail in tomorrow’s post.

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC'

 

Homework

Today’s “homework” is a question for you to consider about the behavior of Who is Active with regard to sleeping sessions: Today the [start_time] and [dd hh:mm:ss.mss] columns, for sleeping sessions, correspond to the login time for the session. But recently I’ve been thinking that it might make more sense to instead show the amount of time since the last request completed—the amount of time that the session has been sleeping. This seems to me to be more useful information and more in line with the goal of the stored procedure.

Which would you rather see, and why? I would appreciate any input and will carefully consider it. This would be a fairly major change, and it is an important decision either way.

Published Saturday, April 23, 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

 

AaronBertrand said:

Personally I think sleep time since the last request completed is more relevant and interesting than the time since they logged in. Though I wonder if it makes sense to have an option to include both, so you could see sessions that have done a lot of work, or have been logged on for a long time, and compare that to how long they have been logged in since doing work.

April 23, 2011 12:34 PM
 

Adam Machanic said:

How about instead of an option if I just add login_time as a new column? Another way to think about this: Would you ever want to see the login_time expressed in the [dd hh:mm:ss.mss] column, were I to change that column to express the amount of time since the last batch (for sleeping sessions)?

Another question is whether this new behavior will be more or less confusing than the existing behavior. It's tough to know what the right move is.

April 23, 2011 6:02 PM
 

Paul White said:

Hey Adam,

An excellent post today.  As far as the homework is concerned:

"... I've been thinking that it might make more sense to instead show the amount of time since the last request completed—the amount of time that the session has been sleeping. This seems to me to be more useful information and more in line with the goal of the stored procedure."

I agree!

Paul

April 25, 2011 1:59 AM
 

LeoPasta said:

I too agree that showing the time since the last batch will be more useful (and less confusing) than showing the time since login.

April 26, 2011 5:36 AM
 

Erin Stellatou said:

Adam-

One thing I remember you telling me: even if you filter out sleeping SPIDs, if you're doing @find_block_leaders = 1, it WILL show a sleeping SPID if that's the lead blocker.  And I just proved it last week with the issue I discussed with Paul :)  I didn't see you mention that tidbit in the post, thought it was worth noting.

Also, as someone who works on systems with a lot of sleeping SPIDs, I would be very interested in seeing the amount of time the session has been sleeping.

E

April 26, 2011 7:43 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
 

Como analisar o desempenho do SQL Server - Parte 06 - said:

October 8, 2014 2:02 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