监控-阻塞检查

原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！

---

在文章如何使用Profiler跟踪阻塞信息，提到可以使用[SQL Server警报+作业]来自动化侦测和收集阻塞信息。但在测试后发现警报触发条件不好设置，计数器Average Wait Time (ms)/Lock Wait Time (ms)的值无法反映出具体的某组阻塞与被阻塞的关系。累计值超过阈值可能是多条阻塞语句造成，也有可能是一个长时间阻塞的语句造成。在累计值超过阈值之后，可能没有阻塞，但警报却依旧会被触发。
使用以下语句获取【阻塞信息】，语句从sys.dm_os_waiting_tasks视图获取阻塞，结合其他相关DMV扩展阻塞与被阻塞信息

USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_BlockedInfo]    脚本日期: 12/14/2016 09:19:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2016/11/22 16:40
    <中文名称>    获取阻塞信息
    <功能说明>    采集当前SQL实例下面的阻塞信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：暂无
    <调用示例>    exec DBA_Pro_Get_BlockedInfo 100233
【修订记录】
-------------------------------------------------------------------
    <2016/11/22 16:40>  <DBA>: 创建 
******************************************************************/
ALTER proc [dbo].[DBA_Pro_Get_BlockedInfo]
    @InstanceID    INT
AS
BEGIN
    SET NOCOUNT ON
    
    IF (@InstanceID < 100000 OR @InstanceID > 999999)
    BEGIN
        PRINT '数据库实例编号参数错误！'
        RETURN
    END
    
    BEGIN TRY
        --得到阻塞对应关系        
        SELECT distinct
               wt.session_id                        AS BlockedSessionId,
               DB_NAME(tl.resource_database_id)        AS DatabaseName,
               wt.wait_type                            AS WaitType,
               tl.request_mode+':'+tl.resource_type+':'+tl.resource_description    AS WaitResource,
               es1.last_request_start_time            AS BlockedTime,
               wt.wait_duration_ms                    AS WaitDuration_ms,
               ec1.client_net_address                AS BlockedClientAddress,
               --h1.text AS BlockedStmt,
               SUBSTRING(
                   h1.text,
                   (er1.statement_start_offset / 2) + 1,
                   (
                       (
                           CASE 
                                WHEN er1.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), h1.text)) 
                                     * 2
                                ELSE er1.statement_end_offset
                           END - er1.statement_start_offset
                       ) / 2
                   ) + 1
               )                                    AS BlockedStmt,
               wt.blocking_session_id                AS BlockingSessionId,
               es2.transaction_isolation_level        AS BlockingIsolationLevel,
               wt.resource_description                AS BlockingHoldResource,
               --h2.text AS BlockingStmt,
               SUBSTRING(
                   h2.text,
                   (ISNULL(er2.statement_start_offset, 0) / 2) + 1,
                   (
                       (
                           CASE 
                                WHEN ISNULL(er2.statement_end_offset, 0) <= 0 THEN 
                                     LEN(CONVERT(NVARCHAR(MAX), h2.text)) 
                                     * 2
                                ELSE er2.statement_end_offset
                           END - ISNULL(er2.statement_start_offset, 0)
                       ) / 2
                   ) + 1
               )                                    AS BlockingStmt,
               ec2.client_net_address                AS BlockingClientAddress,
               es2.program_name                        AS BlockingProgramName,
               es2.login_name                        AS BlockingLoginame,
               GETDATE()                            AS InsertTime
               INTO #BlockInfo
        FROM   sys.dm_tran_locks AS tl WITH(NOLOCK) --会过滤并行问题CXPACKET
        INNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK)
             ON  wt.resource_address = tl.lock_owner_address
        INNER JOIN sys.dm_exec_connections ec1 WITH(NOLOCK)
             ON  ec1.session_id = wt.session_id
        INNER JOIN sys.dm_exec_connections ec2 WITH(NOLOCK)
             ON  ec2.session_id = wt.blocking_session_id
        INNER JOIN sys.dm_exec_sessions AS es1 WITH(NOLOCK)
             ON  es1.session_id = ec1.session_id             
        INNER JOIN sys.dm_exec_sessions AS es2 WITH(NOLOCK)
             ON  es2.session_id = ec2.session_id
        INNER JOIN sys.dm_exec_requests er1 WITH(NOLOCK)
             ON  er1.session_id = ec1.session_id
        LEFT JOIN sys.dm_exec_requests er2 WITH(NOLOCK)
             ON  er2.session_id = ec2.session_id
        CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
        
        --获取spid对应的批处理语句    
        DECLARE @count     INT,
                @s         NVARCHAR(MAX),
                @i         INT
        
        SELECT id = IDENTITY(INT, 1, 1),
               SessionId
               INTO #SessionId
        FROM   (
                   SELECT BlockedSessionId AS SessionId
                   FROM   #BlockInfo
                   UNION
                   SELECT BlockingSessionId
                   FROM   #BlockInfo
               ) a
        
        SELECT @count = @@rowcount,
               @i = 1
        
        CREATE TABLE #EventInfo
        (
            id             INT IDENTITY(1, 1),
            EventType      NVARCHAR(MAX),
            Parameters     INT,
            EventInfo      NVARCHAR(MAX)
        )
        IF @count > 0
        BEGIN
            WHILE @i <= @count
            BEGIN
                SELECT @s = 'dbcc inputbuffer(' + CAST(SessionId AS VARCHAR) + ')'
                FROM   #SessionId a 
                INNER JOIN sys.dm_exec_connections b
                     ON a.SessionId=b.session_id
                WHERE  id = @i
                
                INSERT #EventInfo(EventType,Parameters,EventInfo)
                EXEC (@s) 
                
                SET @i = @i + 1
            END
        END
        
        INSERT INTO Info_Blocked
          (
            InstanceID,
            BlockedSessionId,
            DatabaseName,
            WaitType,
            WaitResource,
            BlockedTime,
            WaitDuration_ms,
            BlockedClientAddress,
            BlockedStmt,
            BlockingSessionId,
            BlockingIsolationLevel,
            BlockingHoldResource,
            BlockingStmt,
            BlockingClientAddress,
            BlockingProgramName,
            BlockingLoginame,
            InsertTime,
            BlockedBatch,
            BlockingBatch
          )
        SELECT @InstanceID,
               a.*,
               c.EventInfo,
               c1.EventInfo
        FROM   #BlockInfo a
               INNER JOIN #SessionId b
                    ON  a.BlockedSessionId = b.SessionId
               INNER JOIN #EventInfo c
                    ON  b.id = c.id
               INNER JOIN #SessionId b1
                    ON  a.BlockingSessionId = b1.SessionId
               INNER JOIN #EventInfo c1
                    ON  b1.id = c1.id        
        
        IF OBJECT_ID('tempdb..#BlockInfo')IS NOT NULL
            DROP TABLE #BlockInfo        
        IF OBJECT_ID('tempdb..#SessionId')IS NOT NULL
            DROP TABLE #SessionId        
        IF OBJECT_ID('tempdb..#EventInfo')IS NOT NULL
            DROP TABLE #EventInfo
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
GO

几处改进的地方：
1、master.dbo.sysprocesses，对于并行操作，此视图同一spid会存在多个kpid。不加区分的话，会得到很多重复的阻塞信息
2、master.dbo.sysprocesses，拆分成sys.dm_exec_connections、sys.dm_exec_sessions、sys.dm_exec_requests三个DMV代替
3、更新各字段的取值，分清主次。例如sys.dm_os_waiting_tasks、sys.dm_tran_locks得到阻塞与被阻塞会话ID、阻塞时长、等待资源、持有资源；sys.dm_exec_connections、sys.dm_exec_requests、sys.dm_exec_sql_text得到阻塞与被阻塞运行的语句、连接IP；sys.dm_exec_sessions得到上次请求时间、事务隔离级别、程序名称、登录名。
4、根据会话ID，使用DBCC INPUTBUFFER()获得从客户端发送到SQLServer实例的最后一个批处理语句
5、当阻塞发生在Object时，WaitResource不会有值
创建作业，设定调度。定时执行存储过程，检测是否有阻塞信息。
测试样例
使用下面脚本创建阻塞

use test
go
create table waiting_tasks1(id int,rundate datetime)
create table waiting_tasks2(id int,rundate datetime)

--生成连续数字
;with GenerateHelpData
as
(
    select 1 as id,getdate() rundate
    union all
    select id+1,dateadd(ss,1,rundate) from GenerateHelpData where id<5000
)
insert into waiting_tasks1 --分别往waiting_tasks1、waiting_tasks2表插入数据
select id,rundate from GenerateHelpData option (maxrecursion 5000);

--会话1，开启事务更新 并且不提交
begin tran 
    update waiting_tasks1 set rundate = getdate()
--rollback

--会话2，做一个查询 并且开启并行
select * from waiting_tasks1 a
inner join waiting_tasks2 b
on a.id = b.id
option (querytraceon 8649)

查看连接信息

--查看连接信息
select spid,kpid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,cpu,login_time,last_batch,ecid,open_tran,status
from sys.sysprocesses where spid in(52,59)
select session_id,most_recent_session_id,connect_time,last_read,client_net_address,most_recent_sql_handle 
from sys.dm_exec_connections where session_id in(52,59)
select session_id,login_time,last_request_start_time,status,transaction_isolation_level,login_name,program_name
from sys.dm_exec_sessions where session_id in(52,59)
select session_id,start_time,status,database_id,blocking_session_id,wait_type,wait_time,last_wait_type,wait_resource
,statement_start_offset,statement_end_offset
from sys.dm_exec_requests where session_id in(52,59)

sys.dm_os_waiting_tasks

--sys.dm_os_waiting_tasks
select * from sys.dm_os_waiting_tasks wt 
where wt.session_id in(52,59)
order by wt.exec_context_id,wt.blocking_exec_context_id

得到阻塞对应关系

--得到阻塞对应关系        
SELECT wt.session_id                     AS BlockedSessionId,
       DB_NAME(tl.resource_database_id)  AS DatabaseName,
       wt.wait_type                      AS WaitType,
       tl.request_mode + ':' + tl.resource_type + ':' + tl.resource_description AS 
       WaitResource,
       wt.wait_duration_ms               AS WaitDuration_ms,
       wt.resource_address            AS BlockingResourceAddress,
       wt.blocking_session_id            AS BlockingSessionId,
       wt.resource_description           AS BlockingHoldResource
FROM   sys.dm_tran_locks AS tl WITH(NOLOCK) --会过滤并行问题CXPACKET
INNER JOIN sys.dm_os_waiting_tasks AS wt WITH(NOLOCK)
     ON  wt.resource_address = tl.lock_owner_address

综合以上脚本，就是文章开篇给出的存储过程。执行存储过程可以获取阻塞信息