SQL Server阻塞查询语句
SQL Server的阻塞查询主要来自sys.sysprocesses。通常在处理时需要加入其它相关的视图或表，例如如sys.dm_exec_connections，sys.dm_exec_sql_text。通过如下几个语句的查询，可以找到阻塞的语句。
点击(此处)折叠或打开
- SELECT BL.SPID BLOCKING_SESSION,
- BL.BLOCKED BLOCKED_SESSION,
- ST.TEXT BLOCKEDTEXT
- FROM (SELECT SPID, BLOCKED
- FROM SYS.SYSPROCESSES A
- WHERE BLOCKED > 0
- AND NOT EXISTS (SELECT 1
- FROM SYS.SYSPROCESSES B
- WHERE BLOCKED > 0
- AND A.BLOCKED = B.SPID)
- UNION
- SELECT SPID, BLOCKED
- FROM SYS.SYSPROCESSES
- WHERE BLOCKED > 0) BL,
- (SELECT T.TEXT, C.SESSION_ID
- FROM SYS.DM_EXEC_CONNECTIONS C
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) ST
- WHERE BL.BLOCKED = ST.SESSION_ID;
点击(此处)折叠或打开
- SELECT A.BLOCKING_SESSION_ID, A.WAIT_DURATION_MS, A.SESSION_ID, B.TEXT
- FROM SYS.DM_OS_WAITING_TASKS A,
- (SELECT T.TEXT, C.SESSION_ID
- FROM SYS.DM_EXEC_CONNECTIONS C
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) B
- WHERE A.SESSION_ID = B.SESSION_ID
- AND A.BLOCKING_SESSION_ID IS NOT NULL;
包含阻塞与被阻塞的SQL脚本
点击(此处)折叠或打开
- SELECT BL.SPID BLOCKING_SESSION,
- BL.BLOCKED BLOCKED_SESSION,
- ST.TEXT BLOCKEDTEXT,
- SB.TEXT BLOCKINGTEXT
- FROM (SELECT SPID, BLOCKED
- FROM SYS.SYSPROCESSES A
- WHERE BLOCKED > 0
- AND NOT EXISTS (SELECT 1
- FROM SYS.SYSPROCESSES B
- WHERE BLOCKED > 0
- AND A.BLOCKED = B.SPID)
- UNION
- SELECT SPID, BLOCKED
- FROM SYS.SYSPROCESSES
- WHERE BLOCKED > 0) BL,
- (SELECT T.TEXT, C.SESSION_ID
- FROM SYS.DM_EXEC_CONNECTIONS C
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) ST,
- (SELECT T.TEXT, C.SESSION_ID
- FROM SYS.DM_EXEC_CONNECTIONS C
- CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) SB
- WHERE BL.BLOCKED = ST.SESSION_ID
- AND BL.SPID = SB.SESSION_ID;
查询死锁：
点击(此处)折叠或打开
- SELECT *
- FROM MASTER ..SYSPROCESSES
- WHERE DB_NAME(DBID) = '数据库名'
- AND SPID <> @@SPID
- AND DBID <> 0
- AND BLOCKED > 0;
About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(642808185)，注明添加缘由
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
