SQL SERVER查看阻塞和锁,阻塞源头

｀伪情人

已于 2022-03-31 14:58:49 修改

阅读量958

点赞数

分类专栏：数据库文章标签：运维

于 2020-09-23 11:22:39 首次发布

本文链接：https://blog.csdn.net/qq_44732067/article/details/108749344

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

/*
-----------------------------------------------------------------------------
spm_Block
功能：查看阻塞和锁,阻塞源头
参数：无
---------------------------------------------------------------------------------
*/
--查找有关被阻塞的请求的信息(含用户)
SELECT '有关被阻塞的请求的信息(含用户)'
SELECT  s.loginame
       ,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2 
               ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2) 
       ,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id
       ,qs.wait_type ,qs.wait_time ,qs.wait_resource 
       ,qs.transaction_id 
FROM SYS.DM_EXEC_REQUESTS qs
  LEFT JOIN (
             SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES GROUP BY spid
            ) s ON qs.session_id=s.spid
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qs.status = N'suspended'
--and s.loginame<>''
ORDER BY qs.wait_time DESC
--查找阻塞源头v3.0
SELECT '阻塞源头'
SELECT SP.spid
      ,CASE WHEN ST1.text IS NULL THEN ST2.text
            ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2, 
                 (
                  CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2 
                       ELSE SR.statement_end_offset 
                  END - SR.statement_start_offset)/2
                  )
        END AS [T-sql]
      ,SP.loginame
      ,DB_NAME(SP.dbid) AS [db_name]
      ,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP 
  LEFT JOIN SYS.DM_EXEC_REQUESTS SR ON SP.spid=SR.session_id
  LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC ON SP.spid=SC.session_id
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN
      (
         SELECT BLOCKED FROM SYS.SYSPROCESSES WHERE BLOCKED<>0
      )
  AND SP.BLOCKED=0