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 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> ??? <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. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
