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.

Who is Active: Default Columns (A Month of Activity Monitoring, Part 7 of 30)

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

Part of the battle of writing Who is Active is achieving the proper level of balance between enough information and too much information.

It’s important to return sufficient data to help debug the most common problems without users having to tweak the parameters. And it’s important to restrict the amount of data sent back so that the default output is not overwhelming, nor is the performance of the procedure sacrificed.

Following are the current default columns, broken into four basic categories:

Time and Status

[dd hh:mm:ss.mss]



Things Slowing Down Your Query


Things Your Session is Doing



Each set of columns deserves some description, and we’ll start with Time and Status. These columns tell you how long your query has been running ([start_time] and its cousin, the “convenience column” [dd hh:mm:ss.mss]), how much longer things might be running ([percent_complete]), whether anything is running at all ([status]), and a record of when you asked ([collection_time]).

The Identifiers are ways of telling one session—or class of sessions—apart from another. The [session_id] and [request_id] columns are, of course, SQL Server’s way of doing this, while the rest of the columns are more human-readable. Note that [request_id] will almost always have a value of 0 for active requests (those where the [status] column has any value other than “sleeping”), and NULL for sleeping sessions. This is not quite the same as the way the data is represented in the sysprocesses DMV, but I don’t think it makes sense to have any [request_id] when there is no request. To see a value greater than 0, you’ll have to use MARS in your application. Not a common thing, which is why this column shows up on the far righthand side of the output.

The Things Slowing Down Your Query columns describe wait states and information about blocking. I’ll get into these in detail in a later post.

Finally, the Things Your Session is Doing columns give information about what is happening, or has happened, on behalf of your session. At this point in the series it’s worth sharing further information about a few of the less obvious of these columns:

  • The most confusing of these columns are those related to tempdb. Each of the columns reports a number of 8 KB pages. The [tempdb_allocations] column is collected directly from the tempdb-related DMVs, and indicates how many pages were allocated in tempdb due to temporary tables, LOB types, spools, or other consumers. The [tempdb_current] column is computed by subtracting the deallocated pages information reported by the tempdb DMVs from the number of allocations. Seeing a high number of allocations with a small amount of current pages means that your query may be slamming tempdb, but is not causing it to grow. Seeing a large number of current pages means that your query may be responsible for all of those auto-grows you keep noticing.
  • The [used_memory] column is also reported based on a number of 8 KB pages. The number a combination of both procedure cache memory and workspace memory grant.
  • [open_tran_count] is by far the most useful column that Who is Active pulls from the deprecated sysprocesses view. And only from that view, since Microsoft has not bothered replicating it elsewhere. It can be used not only to tell whether the session has an active transaction, but also to find out how deeply nested the transaction is. This is invaluable information when debugging situations where applications open several nested transactions and don’t send enough commits to seal the deal.



Yesterday I asked you to use the DMVs to write a query to find the prior statement executed in your session’s current batch. This is not something that Who is Active does, but it’s a fun challenge, and hopefully forced you to look around at some of the less-commonly-used DMVs. The problem is solved by using the sys.dm_exec_query_stats DMV, which has a statement_start_offset and statement_end_offset for every statement in a given batch. By asking for the statement_start_offset less than the current statement_start_offset (available in sys.dm_exec_requests), you can figure out what ran previously:

FROM sys.tables

FROM sys.databases

        (x.statement_end_offset - x.statement_start_offset)/2
    ) AS statement_text
    FROM sys.dm_exec_requests AS r
    INNER JOIN sys.dm_exec_query_stats AS s ON
        s.sql_handle = r.sql_handle
        ANd s.statement_start_offset < r.statement_start_offset
        r.session_id = @@SPID
        s.statement_start_offset DESC
) AS x
CROSS APPLY sys.dm_exec_sql_text
) AS t

Today’s task is to find your current request’s kernel processes identifier (a.k.a. OS thread ID) by using the newer DMVs. You can validate your results by using sysprocesses:

FROM sys.sysprocesses
    spid = @@SPID

As usual, post your query in the comments below.

Published Thursday, April 07, 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



Michael Zilberstein said:

You say that only sysprocesses shows number of open transactions. What about open_transaction_count column in sys.dm_exec_requests? I guess, we'll see the difference only for idle session which keeps open transaction(s) - dm_exec_requests would show nothing while sysprocesses would contain row.

April 11, 2011 7:27 PM

Adam Machanic said:

Michael, exactly. sysprocesses will show the value for a sleeping session. And that's really where it's interesting. Imagine that an app starts a bunch of nested transactions, then doesn't commit them all, but is done sending requests. This column is invaluable for debugging in that situation.

April 11, 2011 9:35 PM

Michael Zilberstein said:


I actually don't have to imagine - two of my clients had exactly this problem when connection from application isn't closed properly after failing on timeout, thus leaving open transaction and locked resources. I don't use your procedure (strictly speaking, I can't according to the license terms - or at least that's questionable) but it was so bad (some of the main tables locked for quite long) that I've build my own monitoring for it 2 years ago that automatically killed stuck sessions and notified application developers. Here is first time I found somebody mentioning this issue:

(comment from mz1313 is mine)

April 12, 2011 9:57 AM

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

Guest said:

Hi Adam,

I would have a question please.

You mentioned we should expect to see 0 on request_id almost always for active sessions. However, when I run the sproc, I receive 0,1 and 2 even when they are not asleep. The status is various for all of them (dormant,runnable etc.). I was just wondering what would that mean and whether it might point out to a problem?

Thanks a lot.

September 4, 2014 4:53 AM

Adam Machanic said:

Hi Guest,

Sounds like some app in your environment is using MARS. This is not a common feature, which is why I mentioned that request_id would almost always be 0. But it's certainly not indicative of a problem.


September 4, 2014 9:40 AM

Guest said:

Hi Adam,

Thanks for your time, appreciated.

September 4, 2014 10:38 AM

SreeniJ said:

Hi Adam .I was wondering that there is a way to grab plan_handle using whoisactive stored procedure.I have encountered today a sitauation where whoisactive shows issue with execution plan but I cannot map it back to the statement (Adhoc).My plan was to clear existing plan from buffercache.

if we can add this functionality would be helpful in case of adhoc queries or Stored procedure.

February 17, 2015 6:19 PM

SreeniJ said:

The execution plans captured using whoisactive are actual execution plans or estimated? I believe they are actual but just making sure not missing important information

February 17, 2015 6:24 PM

Adam Machanic said:


A) No, but that is a very common request. I will definitely add it to the next version.

B) They are "estimated" plans in that they don't include actual numbers for row counts, spool metrics, or batches. That's pretty much the only difference between "estimated" and "actual" plans -- the naming is confusing. What you need to know is that the plan you'll see from the requests DMV (which is where the proc sources its data) is what is actually running. So in that case, yes, it is "actual."


February 18, 2015 10:22 AM

Lucullan said:

I usually have 40 to 100+ of these open_trans on my Database. The status on all of them is sleeping. Right now I have 13 over 10 minutes and 5 almost at 40 minutes. Is there an option where the application may begin a connection again? Is the kill the spid and let the developer know approach right?

Adam this proc is great!

September 9, 2015 10:33 AM

Adam Machanic said:


What connection library is being used here? I'm not aware of anything like that in either Java or .NET but it's a big world outside of those two.

It sounds, on the face of it, like the developers are holding open connections and not properly utilizing connection pooling -- assuming that all of the connections are coming from the same login. If Windows authentication is being used then there could be some decent reasons for holding them open. But rarely are there decent reasons for holding open TRANSACTIONS. I think you need to have a chat with whomever is writing the code.

Glad the proc is helpful :-)


September 10, 2015 9:13 AM

Muhammad Asam said:

Hi Adam,


(x.statement_end_offset - x.statement_start_offset)/2

Whats the rational behind divided by 2. How you figured out this number 2?



October 6, 2015 2:23 AM

Adam Machanic said:


The offsets are output from the DMVs based on number of bytes. And all of the string data is encoded as Unicode -- two bytes. The various string functions work with character positions, not byte offsets. So in order to use those string functions, we must convert byte offsets into character position offsets by dividing by by two.

Make sense? No magic numbers :-)


October 6, 2015 11:26 AM

Lucullan said:

I've switched companies and the "sleeping" open transactions is over 150. I saw this at my old company and now here?  THE BIGGIE ISSUE is that...I have TWO sessions in the runnable state with 2 open transactions each. I think this is why that report won't run. I show no blocking or deadlocking? Any ideas?

I've upgraded to SQL Server 2014 standard from SQL Server 2008 r2  two weeks ago.

Old company was on SQL Server 2012 standard. Is isolation levels an issue?

December 15, 2015 10:53 AM

Adam Machanic said:


Actually it's typical for a running query to show 2 open transactions, especially if it's doing something involving temp tables. There will be some implicit transactions involved in that processing. The "runnable" state actually means that the query is actively running; queries will go in and out of that state every 4ms or so as the query processor is working.

So is that the reason your report won't run? I guess by "won't run" you mean that it's slow. Sounds like CPU contention if you don't see any interesting waits. How are the procs looking on the box? Have you already made sure that the report is using the correct indexes?

As for the "sleeping" open connections, that is super annoying. Tell the devs to clean up after themselves :-)


December 15, 2015 4:57 PM

Tom Moore said:

I know the tutorial above states all columns are in 8K pages, but a colleague and I have differing opinions on this. If our disk block sizes are formatted with 64K blocks (as per Microsofts recommendation) doesn't a one in the physical_reads column mean 64K was read from disk?

March 7, 2016 10:56 PM

Leave a Comment


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


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