簡要介紹SQL Server 2008新的事件處理系統

SQL Server Extended Events（下面簡稱XEvent）是SQL Server 2008裡新加的事件處理系統，用來取代SQL Server原先的SQL Trace的跟蹤機制。事件處理系統對一個復雜服務器系統的排錯，調試是極為關鍵的。和SQL Server原來的事件處理系統相比較，XEvent具有下列的優勢：

消耗更少的系統資源，更適用於在產品服務器上的排錯和調試。並且每收集一個系統事件所消耗的資源都是可預測的。

1. 不僅僅能收集事件數據，還能收集在這事件觸發點的系統動態運行信息，例如內存，T-SQL Stack等等。
2. 可配置性，能夠根據系統負載的需求配置所需收集的事件信息。
3. 下面是一個簡單的例子介紹如何使用XEvent來做系統排錯工作。

當前台應用性能下降的時候，數據庫管理員經常需要確定是否有資源鎖的申請沖突，既所謂我們說的blocking。在SQL Server 2008裡面，你可以查詢系統視圖sys.dm_exec_requests，並且能發現有一些進程正在等待資源鎖，比如像下面這個結果：

 

我們可以看到有個Session 54的狀態是Suspended,意味著一個查詢的操作被blocked, 但是我們看不到是哪一個Query占用了資源，以及查詢計劃和T-SQL Stack等等，如果我有下面的XEvent Session我們就能夠解決這個問題。（在下面的例子中我們假設XEvent Session在Block發生之前已經啟動好了）

1、在查詢編輯器執行下面的命令，這會創建一個XEvent Session來捕捉lock_acquired和lock_released，同時我們還會抓下sql_text，tsql_stack和plan_handle等信息。

-- Perform cleanup.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers')

DROP EVENT SESSION FindBlockers ON SERVER

GO

-- 我們這邊假設是數據庫”DAA”裡出了block,所以我們會使用一個predicate去過濾掉其他數據庫的Block問題

--

DECLARE @dbid int

SELECT @dbid = db_id('DAA')

IF @dbid IS NULL

BEGIN

RAISERROR('DAA is not installed. Install DAA before proceeding', 17, 1)

RETURN

END



DECLARE @sql nvarchar(1024)

SET @sql = '

CREATE EVENT SESSION FindBlockers ON SERVER

--這裡我們加了lock_acquired的Event,並要求返回sql_text，database_id,tsql_stack, --plan_handle和session_id的信息

--

ADD EVENT sqlserver.lock_acquired

(action

( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,

sqlserver.plan_handle, sqlserver.session_id)

WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0)

),

ADD EVENT sqlserver.lock_released

(WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 ))

ADD TARGET package0.pair_matching

( SET begin_event=''sqlserver.lock_acquired'',

begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',

end_event=''sqlserver.lock_released'',

end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',

respond_to_memory_pressure=1)

WITH (max_dispatch_latency = 1 seconds)'



EXEC (@sql)

--

-- Create the metadata for the event session

-- Start the event session

--

ALTER EVENT SESSION FindBlockers ON SERVER

STATE = START

2、一旦XEVENT然後我們可以執行下面的語句去找到那些block了別人的查詢語句：

--

-- The pair matching targets report current unpaired events using

-- the sys.dm_xe_session_targets dynamic management vIEw (DMV)

-- in XML format.

-- The following query retrIEves the data from the DMV and stores

-- key data in a temporary table to speed subsequent Access and

-- retrIEval.

--

SELECT

objlocks.value('(action/value)[5]', 'int')

AS session_id,

objlocks.value('(data/value)[5]', 'int')

AS database_id,

objlocks.value('(data/text)[1]', 'nvarchar(50)' )

AS resource_type,

objlocks.value('(data/value)[9]', 'bigint')

AS resource_0,

objlocks.value('(data/value)[10]', 'bigint')

AS resource_1,

objlocks.value('(data/value)[11]', 'bigint')

AS resource_2,

objlocks.value('(data/text)[2]', 'nvarchar(50)')

AS mode,

objlocks.value('(action/value)[1]', 'varchar(MAX)')

AS sql_text,

CAST(objlocks.value('(action/value)[4]', 'varchar(MAX)') AS XML)

AS plan_handle,

CAST(objlocks.value('(action/value)[3]', 'varchar(MAX)') AS XML)

AS tsql_stack

INTO #unmatched_locks

FROM (

SELECT CAST(xest.target_data as XML)

lockinfo

FROM sys.dm_xe_session_targets xest

JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address

WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'

) heldlocks

CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

--

-- Join the data acquired from the pairing target with other

-- DMVs to return provide additional information about blockers

--

SELECT ul.*

FROM #unmatched_locks ul

INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type

WHERE resource_0 IS NOT NULL

AND session_id IN

(SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)

AND tl.request_status='wait'

上面的語句返回下面的結果，我們可以看到是一個Session 53的Update更新語句Block了Session 54的查詢：

有下面sql_text裡可以看到，整個更新語句begin tran Update BlockTest set Col1 = 'Updated' where ID = 1中，由於少了一個Commit tran，所以block了其他查詢對表BlockTest的操作，