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]
[start_time]
[percent_complete]
[collection_time]
[status]
Identifiers
[session_id]
[request_id]
[login_name]
[host_name]
[database_name]
[program_name]
Things Slowing Down Your Query
[wait_info]
[blocking_session_id]
Things Your Session is Doing
[sql_text]
[CPU]
[tempdb_allocations]
[tempdb_current]
[reads]
[writes]
[physical_reads]
[used_memory]
[open_tran_count]
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: