关闭

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

151人阅读 评论(0) 收藏 举报
分类:

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

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1706次
    • 积分:62
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章存档