What SQL Statements are currently running?

Whenever the server is running slow, my initial move is to check what query is running. Using the Activity Monitor shows information about the different connections and the t-sql running. However, in a batch, it will not display what specific sql statement is running within the batch. I found this code snippet that's very helpful to identify the specific SQL Statement that are currently running.

I forgot where I grab this, but I'm very thankful for this code.

SELECT 
[Server] = @@servername
, [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] =
  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)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
, elapsed_hours = datediff(hh, start_time, getdate())
, elapsed_mins =
  datediff(mi, dateadd(hh,datediff(hh, start_time, getdate()),start_time), getdate())
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 session_Id > 50             
AND session_Id NOT IN (@@SPID)    
ORDER BY 1, 2

I would thank the origin of this code, but I forgot where I got it from... Thank you, nevertheless...


~~ CK