2012 使用XEvent sqlserver.blocked_process_report检测阻塞

概述网上看到的一篇文章来追踪阻塞的，非常简便而且性能很好，跟大家分享。原文地址：AnXEventa Day (21 of 31) – The Future – Tracking Blocking in Denali   在2005新增的Blocked Process Report trace事件是我最喜欢的功能之一，当进程被阻塞超过用户允许的阻塞时间后会自动产生XML的报表。我曾经2年前针对这个功能在

网上看到的一篇文章来追踪阻塞的，非常简便而且性能很好，跟大家分享。原文地址：AnXEventa Day (21 of 31) – The Future – Tracking Blocking in Denali

 

在2005新增的Blocked Process Report trace事件是我最喜欢的功能之一，当进程被阻塞超过用户允许的阻塞时间后会自动产生XML的报表。我曾经2年前针对这个功能在sqlServer Center写过一篇文章Using the Blocked Process Reportin SQL Server 2005/2008。使用这个事件需要使用sql Server trace或者配置Event Notifications在Service broker Queue中捕获事件信息。这两种配置都比较复杂。在sql Server2012中引入了一个新的扩展事件sqlserver.blocked_process_report，非常方便使用。我们现在可以通过创建一个活动会话来捕获被阻塞的进程信息。我们仍然需要配置‘blocked process threshold’选项。

 

CREATE EVENT SESSIONMonitorBlocking

ON SERVER

ADD EVENT sqlserver.blocked_process_report

ADD TARGET package0.ring_buffer(SETMAX_MEMORY=2048)

WITH (MAX_disPATCH_LATENCY= 5SECONDS)

GO

ALTER EVENT SESSIONMonitorBlocking

ON SERVER

STATE=START

GO

EXECUTE sp_configure 'show advanced options',1

GO

RECONfigURE

GO

EXECUTE sp_configure 'blocked process threshold',15

GO

RECONfigURE

GO

EXECUTE sp_configure 'show advanced options',0

GO

RECONfigURE

GO

 

为了测试这个会话事件，我们在SSMS中开启两个查询窗口然后连接到数据库执行下面的代码：

 

USE [tempdb]

GO

CREATE table t1(RowIDintIDentity primary key)

GO

BEGIN TRANSACTION

INSERT INTO t1DEFAulTVALUES

WAITFOR DELAY '00:00:30'

COMMIT

 

第二个窗口代码：

 

USE [tempdb]

GO

SELECT *FROM t1

 

第一个查询将会阻塞第二个查询知道执行完成，在目标ring_buffer将会为我们的事件会话产生blocked processreport。查询ring_buffer目标的阻塞信息，我们可以快速的使用Xquery解析XML数据，代码如下：

 

-- query the XML to get the Target Data

SELECT

    n.value('(event/@@R_502_6889@)[1]','varchar(50)')AS event_@R_502_6889@,

    n.value('(event/@package)[1]','varchar(50)')AS package_@R_502_6889@,

    DATEADD(hh,

           DATEDIFF(hh,GETUTcdaTE(),CURRENT_TIMESTAMP),

           n.value('(event/@timestamp)[1]','datetime2'))AS [timestamp],

    ISNulL(n.value('(event/data[@@R_502_6889@="database_ID"]/value)[1]','int'),

           n.value('(event/action[@@R_502_6889@="database_ID"]/value)[1]','int'))as[database_ID],

    n.value('(event/data[@@R_502_6889@="database_@R_502_6889@"]/value)[1]','nvarchar(128)')as [database_@R_502_6889@],

    n.value('(event/data[@@R_502_6889@="object_ID"]/value)[1]','int')as[object_ID],

    n.value('(event/data[@@R_502_6889@="index_ID"]/value)[1]','int')as[index_ID],

    CAST(n.value('(event/data[@@R_502_6889@="duration"]/value)[1]','bigint')/1000000.0AS decimal(6,2))as[duration_seconds],

    n.value('(event/data[@@R_502_6889@="lock_mode"]/text)[1]','nvarchar(10)')as [file_handle],

    n.value('(event/data[@@R_502_6889@="transaction_ID"]/value)[1]','bigint')as[transaction_ID],

    n.value('(event/data[@@R_502_6889@="resource_owner_type"]/text)[1]','nvarchar(10)')as [resource_owner_type],

    CAST(n.value('(event/data[@@R_502_6889@="blocked_process"]/value)[1]','nvarchar(max)')as XML) as[blocked_process_report]

FROM

(    SELECTtd.query('.')asn

    FROM

    (

        SELECTCAST(target_dataAS XML)astarget_data

        FROM sys.dm_xe_sessionsASs   

        JOIN sys.dm_xe_session_targetsASt

            ON s.address=t.event_session_address

        WHERE s.@R_502_6889@='MonitorBlocking'

         ANDt.target_@R_502_6889@= 'ring_buffer'

    ) ASsub

    CROSS APPLY target_data.nodes('RingBufferTarget/event')ASq(td)

) as tab

GO

 

 

blocked process report的扩展事件输出中包含了很多额外的信息比如database_ID,object_ID,index_ID,duration,lock_mode,transaction_ID,and resource_owner_type 。XML输出可以在SSMS中打开：

<blocked-process-report>

  <blocked-process>

    <processID="process2eb8bda8"taskpriority="0"logused="0"waitresource="KEY: 2:2666130980878942208 (61a06abd401c)"

             waittime="25480"ownerID="12748"transaction@R_502_6889@="SELECT"lasttranstarted="2010-12-21T18:19:03.263"

             XDES="0x2dfb9c10"lockMode="S"schedulerID="1"kpID="2484"status="suspended"spID="60"sbID="0"ecID="0"

             priority="0"trancount="0"lastbatchstarted="2010-12-21T18:19:03.263"

             lastbatchcompleted="2010-12-21T18:19:03.263"clIEntapp="Microsoft sql Server Management Studio - query"

             host@R_502_6889@="WIN-5B9V8jplP3H"hostpID="2708"login@R_502_6889@="WIN-5B9V8jplP3H\administrator"

             isolationlevel="read committed (2)"xactID="12748"currentdb="2"lockTimeout="4294967295"

             clIEntoption1="671090784"clIEntoption2="390200">

     <executionStack>

       <frameline="1"sqlhandle="0x02000000d9de7b2f4f3a78e40f100bc02a84efbb9f01a84d"/>

     </executionStack>

     <inputbuf>

SELECT * FROM t1   </inputbuf>

   </process>

  </blocked-process>

  <blocking-process>

    <processstatus="suspended"waittime="27430"spID="57"sbID="0"ecID="0"priority="0"trancount="1"

             lastbatchstarted="2010-12-21T18:19:01.437"lastbatchcompleted="2010-12-21T18:13:25.637"

             clIEntapp="Microsoft sql Server Management Studio - query"host@R_502_6889@="WIN-5B9V8jplP3H"

             hostpID="2708"login@R_502_6889@="WIN-5B9V8jplP3H\administrator"isolationlevel="read committed (2)"

             xactID="12733"currentdb="2"lockTimeout="4294967295"clIEntoption1="671090784"clIEntoption2="390200">

     <executionStack>

       <frameline="3"stmtstart="100"stmtend="150"sqlhandle="0x020000005a74b3030117e049389a93b2ce5bfb48e272f938"/>

     </executionStack>

     <inputbuf>

BEGIN TRANSACTION

INSERT INTO t1 DEFAulT VALUES

WAITFOR DELAY '00:00:30'

COMMIT   </inputbuf>

   </process>

  </blocking-process>

</blocked-process-report>

 

blocked process report在Extended Events中的输出跟sql Server trace或者Event Notifications是相同的。这种方法只是一个新的手机信息的机制。

注意：当你不想获得阻塞信息的时候，记得关闭‘blocked process threshold选项，默认值为0.

总结

以上是内存溢出为你收集整理的2012 使用XEvent sqlserver.blocked_process_report检测阻塞全部内容，希望文章能够帮你解决2012 使用XEvent sqlserver.blocked_process_report检测阻塞所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。