Active sessions and block duration

As a DBA, we all need the ability to take a quick glance at the currently active processes on our servers. A lot of times we do this during a problem such as transaction blocking. The first tool that normally comes to mind for this type of investigation is sp_who2. sp_who2 is undocumented by Microsoft which provides information regarding current sessions and their activity and is heavily utilized by the community. There is a documented version, sp_who, but this version provides even less information than sp_who2.

Arguably one of the most important purposes of this stored procedure is identifying block conditions. As you can see above, the SPID and BlkBy columns are used to identify who is blocking who but this is never enough information. DBCC INPUTBUFFER() is a common choice for my next query, to view the SQL text that the blocker is running and I would typically have to continue with my investigation from there.

But we can do better…

Here we can query a couple of DMVs to view the lock type, resource being locked, the SQL text, and the duration of the wait (blocking).

[sql]SELECT [w].[session_id],[w].[wait_duration_ms],[w].[wait_type]
,[w].[blocking_session_id],[w].[resource_description],[s].[program_name]
,[t].[text] [sql_text],[t].[dbid],[s].[cpu_time],[s].[memory_usage]
FROM [sys].[dm_os_waiting_tasks] [w]
INNER JOIN [sys].[dm_exec_sessions] [s] ON [w].[session_id] = [s].[session_id]
INNER JOIN [sys].[dm_exec_requests] [r] ON [s].[session_id] = [r].[session_id]
OUTER APPLY [sys].[dm_exec_sql_text] ( [r].[sql_handle] ) [t]
WHERE [s].[is_user_process] = 1;[/sql]

But I bet someone can do even better than that…

Adam Machanic (b | t) is a SQL Server Pro with incredible knowledge of SQL Server internals. He has developed, and provided free to use, sp_WhoIsActive. Which, in my opinion, is a complete replacement for sp_who2. sp_WhoIsActive has dozens of customization parameters and features but simply executing with all defaults provides a superior data set over the first two options discussed.

I highly recommend playing around with this stored procedure, and deploy it to all of your SQL Server instances. It is power and convenience all bottled up into one stored procedure.