---------------------------------------------------------
---实时查询数据库阻塞情况和相关Sql语句
----------------------------------------------------------
select m.spid,m.blocked, db_name(m.dbid) dbname, m.login_time,m.loginame,n.text sqltext,c.request_mode,OBJECT_NAME(resource_associated_entity_id) tableName,m.cpu,m.physical_io
--select *from master..sysprocesses m
cross apply sys.dm_exec_sql_text(m.sql_handle) n
left join sys.dm_tran_locks c on m.spid=c.request_session_id
where
spid in
(
select distinct a.spid
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked
union
select distinct spid from master..sysprocesses where blocked<>0
)
--and m.blocked <> 0
and n.text not like '%master..sysprocesses%' and n.text not like '%SERVERPROPERTY%'
and c.resource_type='OBJECT '