select S.host_name as 用户,S.session_id as 用户ID,R.blocking_session_id as 阻塞者ID,
S.program_name as 客户端,
S.login_name as 登录名称,R.command as Sql类型,
(select text from sys.dm_exec_sql_text(R.sql_handle)) as Sql语句,R.status as 当前状态,
(select host_name from sys.dm_exec_requests where session_id=R.blocking_session_id) as 阻塞者,
(
select sql_handle from sys.dm_exec_requests block where block.session_id = R.blocking_session_id
)
sqlhandle,
R.wait_resource as 等待资源,
R.reads as 读的次数,R.writes as 写的次数,
S.last_request_start_time as 最近一次开始时间,S.last_request_end_time as 最近一次结束时间
from sys.dm_exec_sessions S,sys.dm_exec_requests R
where S.session_id=R.session_id and S.host_name is not null and S.program_name is not null
order by R.blocking_session_id,S.session_id