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.<\/p>\n
It uses sys.dm_exec_query_plan() <\/a>(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<\/a>, sys.sysprocesses<\/a> and CROSS APPLY sys.dm_exec_sql_text<\/a>(). Use the links to see the information being returned.<\/p>\n 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 …<\/p>\n
\nThe query to view session activity.<\/p>\n\r\n\/* Origin Unknown - Adapted by Jack Donnell *\/\r\nUSE master\r\nGO\r\nSET NOCOUNT ON\r\nGO \r\nSELECT \r\nSERVERPROPERTY('ServerName') [Server Name]\r\n,er.session_Id [Spid]\r\n,sp.blocked [Blocked]\r\n-- ,sp.physical_io\r\n-- ,cpu_time\r\n,er.command\r\n,logical_reads\r\n,er.writes\r\n,er.reads\r\n,RTRIM(sp.Hostname) Hostname\r\n,DB_NAME(sp.dbid)[Database]\r\n,RTRIM(sp.loginame)[User]\r\n,er.status [Status]\r\n--,er.command\r\n,er.wait_type [Wait]\r\n,er.wait_time[Wait Time]\r\n-- ,er.[wait_resource]\r\n,CONVERT(VARCHAR(25),er.start_time,22) [Start Time]\r\n,DateDiff(minute,er.start_time,getdate()) [minutes]\r\n,SUBSTRING(qt.text,\r\ner.statement_start_offset\r\n\/ 2,\r\n( CASE WHEN er.statement_end_offset = -1\r\nTHEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2\r\nELSE er.statement_end_offset\r\nEND\r\n- er.statement_start_offset )\r\n\/ 2)[Individual Query]\r\n,qt.text [Parent Query]\r\n,program_name [Program]\r\n,(SELECT TOP 1 CAST (a.query_plan as XML) from sys.dm_exec_query_plan (er.plan_handle) as a) as [QUERY PLAN]\r\n,datediff(mi,start_time,getdate())[Runtime Minutes]\r\n,er.plan_handle\r\nFROM sys.dm_exec_requests er with(NOLOCK)\r\nJOIN sys.sysprocesses sp with(NOLOCK)\r\nON er.session_id = sp.spid\r\nCROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt\r\nWHERE 1=1\r\n--AND session_Id = 186\r\nAND session_Id != ( @@SPID ) -- Ignore this current statement.\r\nOPTION (MAXDOP 1);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"