SELECT ‘KILL‘,
SPID = er.session_id
,STATUS = ses.STATUS
,[LOGIN] = ses.login_name
,HOST = ses.host_name
,BlkBy = er.blocking_session_id
,Kill_SQL = case when er_blocker.blocking_session_id = 0 then ‘kill ‘ + convert(varchar(10),er_blocker.session_id ) else ‘‘ end
,DBName = DB_NAME(er.database_id)
,CommandType = er.command
,SQLStatement = st.text,
t.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
outer apply sys.dm_exec_cursors (er.session_id) c
outer APPLY sys.dm_exec_sql_text (c.sql_handle) t
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
LEFT JOIN sys.dm_exec_requests er_blocker on er.blocking_session_id = er_blocker.session_id
WHERE er.session_id > 0 and ses.host_name is not null
ORDER BY er.blocking_session_id,er.total_elapsed_time desc