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.

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]
[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:

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

 
 

Homework

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:

SELECT *
FROM sys.tables

SELECT *
FROM sys.databases

SELECT
    SUBSTRING
    (
        t.text,
        x.statement_start_offset/2,
        (x.statement_end_offset - x.statement_start_offset)/2
    ) AS statement_text
FROM
(
    SELECT TOP(1)
        s.sql_handle,
        s.statement_start_offset,
        s.statement_end_offset
    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
    WHERE
        r.session_id = @@SPID
    ORDER BY
        s.statement_start_offset DESC
) AS x
CROSS APPLY sys.dm_exec_sql_text
(
    x.sql_handle
) AS t
GO

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:

SELECT
    kpid
FROM sys.sysprocesses
WHERE
    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

Comments

 

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:

Adam,

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:

http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx

(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

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