2012 使用XEvent sqlserver.blocked_process_report检测阻塞

网上看到的一篇文章来追踪阻塞的，非常简便而且性能很好，跟大家分享。原文地址：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

CREATETABLE?t1(RowIDintidentityprimarykey)

GO

BEGINTRANSACTION

INSERTINTO?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/@name)[1]','varchar(50)')AS?event_name,

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

DATEADD(hh,

DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),

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

ISNULL(n.value('(event/data[@name="database_id"]/value)[1]','int'),

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

n.value('(event/data[@name="database_name"]/value)[1]','nvarchar(128)')as?[database_name],

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

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

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

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

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

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

CAST(n.value('(event/data[@name="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.name='MonitorBlocking'

ANDt.target_name=?'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"transactionname="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"

hostname="WIN-5B9V8JPLP3H"hostpid="2708"loginname="WIN-5B9V8JPLP3HAdministrator"

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"hostname="WIN-5B9V8JPLP3H"

hostpid="2708"loginname="WIN-5B9V8JPLP3HAdministrator"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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!