SQL Server - What's running Now with Query Plans

gistfile1.txt

--- 1 Work In Process
--- Ignore me - This is not the query you are looking for
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
USE master
GO

SELECT
	sp.session_Id AS 'Spid'
	,er.blocking_session_id AS 'BlockedBy'	
	,DB_NAME(er.database_id) AS 'Database'
	,Host_name AS 'HostName'
	,sp.login_name AS 'User'	
	,er.command AS 'Command'
	,CASE er.transaction_isolation_level
		WHEN 1 THEN 'ReadUncommitted'
		WHEN 2 THEN 'ReadCommitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
		ELSE 'Unspecified' 
	END	AS TrnIsoLevel
	,er.dop AS 'Dop'	
	,sp.open_transaction_count AS 'OpenTrans'
	,er.cpu_time AS 'CPUTime'
	,er.Logical_reads AS 'Logical Reads'
	,er.reads
	,er.writes
	,er.row_count AS 'Rows'
	,er.granted_query_memory as 'MemGrantKB'
	,er.status AS [Status]
	,er.wait_type AS [WaitType]
	,program_name AS 'Application'
	,p.query_plan as 'QueryPlan'
	,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 SqlStmt
	, qt.text AS [ParentQuery]
	,er.request_id AS 'RequestID'
	,er.percent_complete
	,start_time AS 'Started'
	,DATEADD(ms,er.estimated_completion_time,GETDATE()) AS [ETA Completion]
	,CONVERT(NUMERIC(10,2),er.estimated_completion_time/1000.0/60.0) AS [ETA Mins]
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 <> @@SPID
	-- Search for Specific Query Text
	-- AND qt.text like N'%'+N'vStatement'+N'%'
	--ORDER BY 
	--1, 2
	--cpu_time desc
	AND	sp.program_name NOT LIKE 'DatabaseMail%'

--- 2 Locking
--- Look for any Locks blocking others 
SELECT
	*
FROM
	sys.dm_os_waiting_tasks t
inner join
	sys.dm_exec_connections c
ON
	c.session_id = t.blocking_session_id
cross apply
	sys.dm_exec_sql_text(c.most_recent_sql_handle) as h1

--- 3 Current Sessions
exec sp_who2

--- 4 show all threads, use Process Explorer to suspend the thread KPID
--select * from master..sysprocesses --where spid = 64