學海無邊，書囊無底

有時會看到SQL發生了deadlock，這時如果沒有即時使用SQL Profiler去錄下來的話，就不知到底是什麼原因發生了deadlock。

這時透過Extended Events來檢視來查看，所以可以到「SQL Server 2008 Extended Events SSMS Addin」安裝SSMS的Addin(SQL 2012已內建，參見註1)。

安裝完後，從SSMS工具列上的　［檢視］->［Show Extended Event Session Explorer］可開啟Extended Events Information的視窗。如下圖

--開啟SESSION 1執行update語法
begin tran 
--update Production.Product set ListPrice=2 where ProductID=1 
UPDATE Northwind.dbo.Customers SET ContactName='Sandy'
WHERE CustomerID='ALFKI'

--開啟SESSION 2執行udpate語法，並在此SESSION中查詢SESSION 1所UPDATE的資料
begin tran 
UPDATE Northwind.dbo.Customers SET ContactName='Dofi'
WHERE CustomerID='ANATR'

SELECT * FROM Northwind.dbo.Customers 
WHERE CustomerID='ALFKI'

--回到SESSION 1查詢SESSION 2所UPDATE的資料
SELECT * FROM Northwind.dbo.Customers 
WHERE CustomerID='ANATR'

在Extended Event Information視窗以滑鼠右鍵點選[ Package0ring_buffer ]，選擇快捷選單中的[ View Target Data ]即可開啟下圖的UI介面，並查詢已發生Deadlock的語法，及造成Deadlock的相關訊息。

建立自訂的擴充事件　　　　　　　　　　　　　　　　　　　　　　　　　

你也可以自行建立要捕捉的事件

-- Extended Event for 發現長時間運行的查詢* 
 IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery')
 DROP EVENT SESSION LongRunningQuery ON SERVER
 GO
 -- Create Event
 CREATE EVENT SESSION LongRunningQuery
 ON SERVER
 -- Add event to capture event
 ADD EVENT sqlserver.sql_statement_completed
 (
 -- Add action - event property 添加事件捕捉事件
 ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
 -- Predicate - time 1000 milisecond
 WHERE sqlserver.sql_statement_completed.duration > 1000
 )
 -- Add target for capturing the data - XML File
 ADD TARGET package0.asynchronous_file_target(
 SET filename='c:\LongRunningQuery.xet', metadatafile='c:\LongRunningQuery.xem'),
 -- Add target for capturing the data - Ring Bugger
 ADD TARGET package0.ring_buffer
 (SET max_memory = 4096)
 WITH (max_dispatch_latency = 1 seconds)
 GO

執行上述語法後，可在視窗難看到以建立的擴充事件

-- Enable Event 啟用事件 
ALTER EVENT SESSION LongRunningQuery ON SERVER
 STATE=START
GO

-- Run long query (longer than 1000 ms)
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
-- Stop the event 停用事件
ALTER EVENT SESSION LongRunningQuery ON SERVER
STATE=STOP
GO

-- Read the data from Ring Buffer
 SELECT CAST(dt.target_data AS XML) AS xmlLockData
 FROM sys.dm_xe_session_targets dt
 JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
 JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
 AND ds.Name = 'LongRunningQuery'
 GO
 -- Read the data from XML File
 SELECT event_data_XML.value('(event/data[1])[1]','VARCHAR(100)') AS Database_ID,
 event_data_XML.value('(event/data[2])[1]','INT') AS OBJECT_ID,
 event_data_XML.value('(event/data[3])[1]','INT') AS object_type,
 event_data_XML.value('(event/data[4])[1]','INT') AS cpu,
 event_data_XML.value('(event/data[5])[1]','INT') AS duration,
 event_data_XML.value('(event/data[6])[1]','INT') AS reads,
 event_data_XML.value('(event/data[7])[1]','INT') AS writes,
 event_data_XML.value('(event/action[1])[1]','VARCHAR(512)') AS sql_text,
 event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS tsql_stack,
 CAST(event_data_XML.value('(event/action[2])[1]','VARCHAR(512)') AS XML).value('(frame/@handle)[1]','VARCHAR(50)') AS handle
 FROM
 (
 SELECT CAST(event_data AS XML) event_data_XML, *
 FROM sys.fn_xe_file_target_read_file
 ('c:\LongRunningQuery*.xet',
 'c:\LongRunningQuery*.xem',
 NULL, NULL)) T
 GO

此結果集包含正在運行的查詢超過1000毫秒。在我們的例子中，因為使用了XML文件，因此當SQL服務或電腦重新啟動時資料不會reset（若使用的是DMV，SQL服務重新啟動他會被reset）。

此事件會是非常有用的故障排除。using DMV, it will reset when SQL services restarts

若不再使用，可透過Extended Events視窗直接刪除Event Session，如下圖所示

或者透過以下指令直接刪除即可

 DROP EVENT SESSION LongRunningQuery
 ON SERVER

補充　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

SQL Server 2012 中可於SSMS工具中的物件總管是裝看到內建的擴充事件

參考：http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/
http://www.dotblogs.com.tw/rainmaker/archive/2012/09/26/75073.aspx
SQL Server 2008使用Extended Events SSMS Addin + Performance Dashboard Reports來監看系統
使用SQL Server 2008的擴展活動