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.<\/p>\n
USE MASTER 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 …<\/p>\n
\n
\n--jackdonnell.com <\/code><\/p>\n
\nGO
\nSELECT @@SERVERNAME [Server]
\n, r.session_id [SPID]
\n,d.name [DB NAME]
\n,s.host_name [HOST NAME]
\n,s.login_name [LOGIN]
\n,r.status [STATUS]
\n,r.command [COMMAND]
\n,(select text from sys.dm_exec_sql_text(r.sql_handle)) as [SQL TEXT]
\n,(SELECT CAST (a.query_plan as XML) from sys.dm_exec_query_plan (r.plan_handle) as a) as [QUERY PLAN]
\n,r.cpu_time [CPU TIME]
\n,r.logical_reads [LOGICAL READS]
\n,s.reads
\n,s.writes
\n,sp.[ThreadCount]
\n,s.login_time [LOGIN TIME]
\n,s.last_request_start_time [LAST REQUEST START]
\n,last_request_end_time [LAST REQUEST END]
\n,r.wait_type [WAIT TYPE]
\n,r.last_wait_type[LASTWAIT TYPE]
\n,s.program_name[APPLICATION]
\n,s.client_interface_name
\n,r.plan_handle as [PLAN HANDLE]
\n,r.sql_handle as [SQL HANDLE]
\n,GetDate() [SAMPLE TIME]
\nFROM sys.dm_exec_sessions as s
\ninner join sys.dm_exec_requests as r
\non s.session_id =r.session_id
\ninner join sys.databases as d
\non d.database_id=r.database_id
\ninner join (Select spid , count(spid)[ThreadCount]from sysprocesses group by spid)as sp
\non sp.spid=r.session_id
\nwhere is_user_process = 1
\nGO<\/p>\n","protected":false},"excerpt":{"rendered":"