select getdate() time_now, de.session_id, de.status, case when nl.blocking_session_id <> 0 and blocked.session_id is null then nl.blocking_session_id when nl.blocking_session_id <> 0 and de.session_id <> blocked.blocking_session_id then blocked.blocking_session_id when nl.blocking_session_id = 0 and de.session_id = blocked.session_id then blocked.blocking_session_id when nl.blocking_session_id <> 0 and de.session_id = blocked.blocking_session_id then nl.blocking_session_id else null end as blocking_session_id, case when deco.statement_start_offset is not null and deco.statement_end_offset is not null then substring( dec.text, (deco.statement_start_offset/2)+1, (( case deco.statement_end_offset when -1 then datalength(dec.text) else deco.statement_end_offset end - deco.statement_start_offset )/2) + 1 ) else dec.text end as query_text, deqp.query_plan, nl.total_elapsed_time total_elapsed_time_ms, nl.total_elapsed_time/1000 total_elapsed_time_sec, nl.total_elapsed_time/1000/60 total_elapsed_time_min, nl.cpu_time, nl.reads, nl.writes, nl.logical_reads, de.host_name, de.login_name, de.program_name, db_name(nl.database_id) db_name, nl.request_id, nl.transaction_id, nl.wait_type, nl.wait_time, nl.last_wait_type, nl.wait_resource, nl.open_transaction_count, nl.open_resultset_count, nl.transaction_isolation_level, case de.transaction_isolation_level when 0 then 'Unspecified' when 1 then 'ReadUncommitted' when 2 then 'ReadCommitted' when 3 then 'Repeatable' when 4 then 'Serializable' when 5 then 'Snapshot' end as transaction_isolation_level_text, nl.row_count, dec.objectid, nl.sql_handle, nl.plan_handle from sys.dm_exec_sessions as de left join sys.dm_exec_requests as nl on nl.session_id = de.session_id outer apply ( select top 1 dbid, last_batch, open_tran, sql_handle, session_id, blocking_session_id, lastwaittype, waittime from ( select sys1.dbid, sys1.last_batch, sys1.open_tran, sys1.sql_handle, sys2.spid as session_id, sys2.blocked as blocking_session_id, sys2.lastwaittype, sys2.waittime, sys2.cpu, sys2.physical_io, sys2.memusage from sys.sysprocesses as sys1 inner join sys.sysprocesses as sys2 on sys1.spid = sys2.blocked ) as blocked where (de.session_id = blocked.session_id or de.session_id = blocked.blocking_session_id) ) as blocked outer apply sys.dm_exec_sql_text(coalesce(nl.sql_handle, blocked.sql_handle)) as dec outer apply sys.dm_exec_query_plan(nl.plan_handle) as deqp left join sys.dm_exec_query_stats as deco on nl.sql_handle = deco.sql_handle and nl.plan_handle = deco.plan_handle and nl.statement_start_offset = deco.statement_start_offset and nl.statement_end_offset = deco.statement_end_offset where de.session_id <> @@spid and de.host_name is not null and coalesce(db_name(nl.database_id), db_name(blocked.dbid)) is not null order by nl.total_elapsed_time desc;
Column | Description -------------------------------- | -------------------------------------------- time_now | Time this report is generated. session_id | ID of the session which this request is related. status | Request status, Background, Running, Runnable, Sleeping, Suspended. blocking_session_id | ID of the session that is blocking this request. query_text | Text of the SQL query. query_plan | Query plan in XML format. total_elapsed_time_ms | Total elapsed time in ms since the request arrived. total_elapsed_time_sec | Total elapsed time in seconds since the request arrived. total_elapsed_time_min | Total elapsed time in minutes since the request arrived. cpu_time | CPU time used by this request. reads | Number of disk reads performed by this request. writes | Number of disk writes performed by this request. logical_reads | Number of logical reads performed by this request. host_name | Name of the client workstation. login_name | SQL Server login used for this execution. program_name | Client program that initiated the request. db_name | Name of database the request is executing against. request_id | Unique request ID. transaction_id | ID of the transaction in which this request executes. wait_type | Type of wait if this request is blocked. wait_time | Wait time in ms if this request is blocked. last_wait_type | Type of wait if this request has previously been blocked. wait_resource | Resource the request is waiting for if blocked. open_transaction_count | Number of transaction open for this request. open_resultset_count | Number of result sets open for this request. transaction_isolation_level | Isolation level of the transaction for this request. transaction_isolation_level_text | Isolation level of the transaction for this request in text form. row_count | Number of rows returned to the client for this request. objectid | ID of the object. sql_handle | SQL handle for this request's SQL. plan_handle | Plan handle for this requests's SQL execution plan.