SQL Server - What's running Now with Query Plans

gistfile1.txt

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

SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON

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'
	,FORMAT(er.percent_complete,'N2') AS 'Percent_Complete'
	,FORMAT(er.estimated_completion_time/1000.0/60.0,'N2') AS [ETA Mins]
	,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'
	,FORMAT(er.cpu_time,'N0') AS 'CPUTimeMS'
	,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 VARCHAR(MAX)) AS SqlStmt
	, qt.text AS [ParentQuery]
	,er.request_id AS 'RequestID'
	,start_time AS 'Started'
	,DATEADD(ms,er.estimated_completion_time,GETDATE()) AS [ETA Completion]
FROM
	sys.dm_exec_requests er WITH (NOLOCK)
JOIN
	sys.dm_exec_sessions sp WITH (NOLOCK)
ON
	er.session_id = sp.session_id
OUTER APPLY
	sys.dm_exec_sql_text(er.sql_handle) 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
ORDER BY
	2,3

--- 3 Current Sessions
EXEC sp_who2

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

--- 5 Excessive Memory Grants
SELECT    SUM(granted_memory_kb) / 1024 AS granted_memory_mb
        , SUM(requested_memory_kb) / 1024 AS requested_memory_mb
        , SUM(used_memory_kb) / 1024 AS used_memory_mb
        , (SUM(granted_memory_kb) - SUM(used_memory_kb)) / 1024 AS excess_memory_granted_mb
FROM    sys.dm_exec_query_memory_grants
OPTION (RECOMPILE)