SQL Server阻塞查询语句

最新推荐文章于 2023-10-19 11:40:41 发布

转载最新推荐文章于 2023-10-19 11:40:41 发布 · 364 阅读

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群，学习最实用的数据库技术。