T-SQL Tuesday: What’s Currently Running?

My good friend Allen White is hosting this months installment of #TSQL2sDay so I am motivated to jump in. #TSQL2sDay is the creation of Adam Machanic. The concept is simple, about a week before the second Tuesday of the month a theme will be posted.  Any blogger that wishes to participate is invited to write a post on the chosen topic and any post that is related to both SQL Server and the theme is fair game.The challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier?

What’s Currently Running?

One of my favorite tricks is actually just a little script I have in my toolbox to find out what queries are currently running right now. In fact I have had quite a few people ask me the for this script so I am glad to share it in this blog post.  With SQL Server 2005 and above  SQL Server provides Database Management Views that give you direct access to executing requests and running process. The following query uses sys.dm_exec_request, sys.sysprocesses. We will also use cross apply to get the query text from sys.dm_exec_sql_text and the execution plan from sys.dm_exec_query_plan.

The Good Stuff…

{UPDATE: 1/1/2012 – Replaced sysprocesses with sys.dm_exec_sessions as recommended by Phil in the comments

UPDATE: 6/5/2013 – Changed CROSS APPLY’s to OUTER APPLY’s so we capture statements without execution plan or SQL Text.}

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
[Spid] = sp.session_Id
,er.request_id
,er.command
,[Database] = DB_NAME(er.database_id)
,[User] = login_name
,er.blocking_session_id
,[Status] = er.status
,[Wait] = wait_type
,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,
(er.statement_start_offset / 2)+1,     ((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS xml) as sql_statement
,[Parent Query] = qt.text
,p.query_plan
,er.cpu_time
, er.reads
, er.writes
, er.Logical_reads
, er.row_count
, Program = program_name
,Host_name
,start_time
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions sp ON er.session_id = sp.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) p
WHERE sp.is_user_process = 1
/* sp.session_Id > 50
-- Ignore system spids. -- */
AND sp.session_Id NOT IN (@@SPID)
ORDER BY 1, 2