实时查询数据库阻塞情况和相关Sql语句

---------------------------------------------------------

---实时查询数据库阻塞情况和相关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 '

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/pphuw/article/details/49902023
个人分类: 数据库
上一篇修改远程桌面连接的端口
下一篇sql锁表查询和解锁
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭