|
| 1 | +/* |
| 2 | +Author: Nagaraj |
| 3 | +Original link: http://www.sqlservercentral.com/blogs/sql-and-sql-only/2016/08/07/current-running-queries/ |
| 4 | +*/ |
| 5 | + |
| 6 | +SELECT getdate() as dt, |
| 7 | +ss.session_id, |
| 8 | +db_name(sysprocesses.dbid) as dbname, |
| 9 | +er.status as req_status, |
| 10 | +ss.login_name, |
| 11 | +cs.client_net_address, |
| 12 | +ss.program_name, |
| 13 | +sysprocesses.open_tran, |
| 14 | +er.blocking_session_id, |
| 15 | +ss.host_name, |
| 16 | +ss.client_interface_name, |
| 17 | +[eqp].[query_plan] as qplan, |
| 18 | +SUBSTRING(est.text,(er.statement_start_offset/2)+1, |
| 19 | +CASE WHEN er.statement_end_offset=-1 OR er.statement_end_offset=0 |
| 20 | +THEN (DATALENGTH(est.Text)-er.statement_start_offset/2)+1 |
| 21 | +ELSE (er.statement_end_offset-er.statement_start_offset)/2+1 |
| 22 | +END) as req_query_text, |
| 23 | +er.granted_query_memory, |
| 24 | +er.logical_reads as req_logical_reads, |
| 25 | +er.cpu_time as req_cpu_time, |
| 26 | +er.reads as req_physical_reads, |
| 27 | +er.row_count as req_row_count, |
| 28 | +er.scheduler_id, |
| 29 | +er.total_elapsed_time as req_elapsed_time, |
| 30 | +er.start_time as req_start_time, |
| 31 | +er.percent_complete, |
| 32 | +er.wait_resource as wait_resource, |
| 33 | +er.wait_type as req_waittype, |
| 34 | +er.wait_time as req_wait_time, |
| 35 | +wait.wait_duration_ms as blocking_time_ms, |
| 36 | +lock.resource_associated_entity_id, |
| 37 | +lock.request_status as lock_request_status, |
| 38 | +lock.request_mode as lock_mode, |
| 39 | +er.writes as req_writes, |
| 40 | +sysprocesses.lastwaittype, |
| 41 | +fn_sql.text as session_query, |
| 42 | +ss.status as session_status, |
| 43 | +ss.cpu_time as session_cpu_time, |
| 44 | +ss.reads as session_reads, |
| 45 | +ss.writes as session_writes, |
| 46 | +ss.logical_reads as session_logical_reads, |
| 47 | +ss.memory_usage as session_memory_usage, |
| 48 | +ss.last_request_start_time, |
| 49 | +ss.last_request_end_time, |
| 50 | +ss.total_scheduled_time as session_scheduled_time, |
| 51 | +ss.total_elapsed_time as session_elpased_time, |
| 52 | +ss.row_count as session_rowcount |
| 53 | +FROM sys.dm_exec_sessions ss |
| 54 | +INNER JOIN sys.dm_exec_connections cs ON ss.session_id = cs.session_id |
| 55 | +OUTER APPLY fn_get_sql(cs.most_recent_sql_handle) as fn_sql |
| 56 | +INNER JOIN sys.sysprocesses ON sys.sysprocesses.spid = cs.session_id |
| 57 | +LEFT OUTER JOIN sys.dm_exec_requests [er] ON er.session_id = ss.session_id |
| 58 | +OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est] |
| 59 | +OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp] |
| 60 | +LEFT OUTER JOIN sys.dm_os_waiting_tasks wait ON er.session_id = wait.session_id |
| 61 | +and wait.wait_type like 'LCK%' and |
| 62 | +er.blocking_session_id = wait.blocking_session_id |
| 63 | +LEFT OUTER JOIN sys.dm_tran_locks lock ON lock.lock_owner_address = wait.resource_address |
| 64 | + AND lock.request_session_id = er.blocking_session_id |
| 65 | +WHERE ss.status != 'sleeping'; |
0 commit comments