I wanted to share a procedure that I wrote that I use every day for checking what is active on the server. This is not anywhere near as thorough as something like Adam Machanic’s fantastic sp_whoisactive or Brent Ozar’s sp_askBrent but to me it works better for me than sp_who or the undocumented sp_who2. I find it to be a very useful way to see what is running, what specific statement is running if it is a multistatement procedure. I also find this same procedure useful for capturing data on a regular basis (every minute or so) to get data on your server with regularity and without much overhead at all. Obviously you might be able to get some of the same things from the System Health Session or from a handful of other ways, but this will quickly show you processes that are hitting the system hard, how many threads they are using, what their wait type is, who they are blocking, and what specific statement is running.
I am betting that there are some enhancements that would make this better, so please share them if you have thoughts and feel free to use this wherever you would like.
USE master; GO IF OBJECT_ID('master..sp_who5') IS NOT NULL DROP PROCEDURE dbo.sp_who5; GO CREATE procedure sp_who5 as SELECT er.session_Id , blocked , DB_NAME(sp.dbid) as database_name , er.percent_complete , ecid as thread , nt_username as [user] , er.status as status , case when er.statement_start_offset > 1 and er.statement_end_offset> 1 THEN SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) ELSE qt.text end as CurrentQuery , er.command , qt.text as ParentQuery , program_name , Hostname , start_time , wait_type FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE er.session_id > 50 -- no system spids. AND er.session_Id NOT IN (@@SPID) -- remove current statements ORDER BY er.session_id , blocked;