Query Plans and active query information

Quick script to see active processes and to grab the query plan. It leverages some DMVs to get the query text and plan. Only can be used on SQL 2005 and above. It is a great and light wight way to see what is going on with the server and performance tuning.



--jackdonnell.com

USE MASTER
GO
SELECT @@SERVERNAME [Server]
, r.session_id [SPID]
,d.name [DB NAME]
,s.host_name [HOST NAME]
,s.login_name [LOGIN]
,r.status [STATUS]
,r.command [COMMAND]
,(select text from sys.dm_exec_sql_text(r.sql_handle)) as [SQL TEXT]
,(SELECT CAST (a.query_plan as XML) from sys.dm_exec_query_plan (r.plan_handle) as a) as [QUERY PLAN]
,r.cpu_time [CPU TIME]
,r.logical_reads [LOGICAL READS]
,s.reads
,s.writes
,sp.[ThreadCount]
,s.login_time [LOGIN TIME]
,s.last_request_start_time [LAST REQUEST START]
,last_request_end_time [LAST REQUEST END]
,r.wait_type [WAIT TYPE]
,r.last_wait_type[LASTWAIT TYPE]
,s.program_name[APPLICATION]
,s.client_interface_name
,r.plan_handle as [PLAN HANDLE]
,r.sql_handle as [SQL HANDLE]
,GetDate() [SAMPLE TIME]
FROM sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id
inner join sys.databases as d
on d.database_id=r.database_id
inner join (Select spid , count(spid)[ThreadCount]from sysprocesses group by spid)as sp
on sp.spid=r.session_id
where is_user_process = 1
GO

Tiny URL for this post:
 

Share the joy

Comments are closed.