2012 使用XEvent sqlserver.blocked

An XEventa Day (21 of 31) – The Future – Tracking Blocking in Denali 在2005新增的Blocked Process Report trace事件是我最喜欢的功能之一，当进程被阻塞超过用户允许的阻塞时间后会自动产生XML的报表。我曾经2年前针对这个功能在SQLServer Center写过

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/@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