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
  • http://ssistalk.com Phil

    I would move away from the deprecated sys.sysprocesses. sys.dm_exec_requests JOIN sys.dm_exec_sessions, for instance.

  • Pingback: Allen White : T-SQL Tuesday #25 Followup - Just in Time for the Holidays

  • http://johnsterrett.com johnsterrett

    Phil,

    Thank you for pointing out that I was still using sys.sysprocesses. The code has been updated above. I also added a few helpful columns like cpu_time, reads, writes, logical reads and row_count.

    This also reminds me that many of us DBA’s still have several instances of SQL 2000 so I will throw out an how to blog post on getting whats running in SQL 2000.

    Regards,
    John

  • Scott Caldwell

    John,

    Nice query. I would use “where is_user_process = 1″ instead of “where session_id > 50″. There are cases, especially on large servers with soft numa, database mirroring (lots of db), service broker (lots of tasks), etc, where this won’t hold true. It used to be 100% reliable but not so much anymore.

    Thanks,

    Scott

  • http://johnsterrett.com johnsterrett

    Great catch Scott. I have updated the query above to include your update.

  • Pingback: Finding Top Offenders From Cache - SQL Server - SQL Server - Toad World