THE SQL Server Blog Spot on the Web

Welcome to - 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)


Published Saturday, April 23, 2011 11:00 AM by Adam Machanic



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!


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:


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.


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
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


Privacy Statement