Master of None

I want to start out by saying I am not sure about the origin of this query. I use it frequently to find activity on my servers. I left some of the code commented to show a few example items you can add and additional search criteria. I really encourage anyone to grab a query and alter it to show information useful to them.

It uses sys.dm_exec_query_plan() (via inline select forgive me) to grab the query plan when possible. Additional information to grab performance information and SQL code executing uses these DMVs sys.dm_exec_requests, sys.sysprocesses and CROSS APPLY sys.dm_exec_sql_text(). Use the links to see the information being returned.

The query to view session activity.

/* Origin Unknown - Adapted by Jack Donnell */
USE master
GO
SET NOCOUNT ON
GO 
SELECT 
SERVERPROPERTY('ServerName') [Server Name]
,er.session_Id [Spid]
,sp.blocked [Blocked]
-- ,sp.physical_io
-- ,cpu_time
,er.command
,logical_reads
,er.writes
,er.reads
,RTRIM(sp.Hostname) Hostname
,DB_NAME(sp.dbid)[Database]
,RTRIM(sp.loginame)[User]
,er.status [Status]
--,er.command
,er.wait_type [Wait]
,er.wait_time[Wait Time]
-- ,er.[wait_resource]
,CONVERT(VARCHAR(25),er.start_time,22) [Start Time]
,DateDiff(minute,er.start_time,getdate()) [minutes]
,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)[Individual Query]
,qt.text [Parent Query]
,program_name [Program]
,(SELECT TOP 1 CAST (a.query_plan as XML) from sys.dm_exec_query_plan (er.plan_handle) as a) as [QUERY PLAN]
,datediff(mi,start_time,getdate())[Runtime Minutes]
,er.plan_handle
FROM sys.dm_exec_requests er with(NOLOCK)
JOIN sys.sysprocesses sp with(NOLOCK)
ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE 1=1
--AND session_Id = 186
AND session_Id != ( @@SPID ) -- Ignore this current statement.
OPTION (MAXDOP 1);

Tiny URL for this post:
 Copy