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…

SELECT [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	,
p.query_plan,
Program = program_name	,
Hostname	,
nt_domain	,
start_time
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
cross apply sys.dm_exec_query_plan(er.plan_handle) p
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID)
 ORDER BY 1, 2