淺談偵測「死結(DeadLock)」的作法，使用「警示(Alert)」作訊息通知，以WMI 提供者為例

在文章：
淺談偵測「死結(DeadLock)」的作法，以使用「追蹤旗標(Trace flag)」1204、1222為例
http://sharedderrick.blogspot.com/2010/03/deadlocktrace-flag12041222.html

我們已經討論過將發生「死結」的資訊記錄到SQL Server記錄檔內。

---

但是若要做到主動通知資料庫管理人員得知此事件的發生，
可以使用以下的方式來完成。

功能說明：
使用警示，可將 XML 死結圖形事件儲存在資料表中，以便稍後進行分析。
SQL Server Agent 會提交 WQL 要求、接收 WMI 事件，以及執行工作來回應事件。
請注意，雖然在處理通知訊息時包含數個 Service Broker 物件，但是 WMI 事件提供者會處理建立與管理這些物件的詳細資料。

首先，在 msdb 資料庫中建立一個資料表來容納死結圖形事件。
此資料表包含兩個資料行：AlertTime 資料行容納警示執行的時間，而 DeadlockGraph 資料行容納其中包含死結圖形的 XML 文件。

接著，建立警示。指令碼會先建立警示將執行的工作、將作業步驟加入到工作中，然後將工作目標瞄準為目前的 SQL Server 執行個體。然後，指令碼會建立警示。

作業步驟會擷取 WMI 事件執行個體的 TextData 屬性，然後將該值插入 DeadlockEvents 資料表的 DeadlockGraph 資料行中
。請注意，SQL Server 會以隱含的方式，將字串轉換為 XML 格式。作業步驟會使用 Transact-SQL 子系統，因此，作業步驟不會指定 Proxy。

每當記錄死結圖形追蹤事件時，警示就會執行作業。
對於 WMI 警示，SQL Server Agent 會使用指定的命名空間和 WQL 陳述式來建立通知查詢。

SQL Server Agent 會針對此警示監視本機電腦上的預設執行個體。
WQL 陳述式會要求預設執行個體中的任何 DEADLOCK_GRAPH 事件。若要變更警示所監視的執行個體，將警示的執行個體名稱取代為 @wmi_namespace 中的 MSSQLSERVER。

---

實作練習：
偵測「死結(DeadLock)」的作法，使用「警示(Alert)」作訊息通知，以WMI 提供者為例


準備工作：
1. 請確認SQL Server 服務啟動帳戶，已經加入到 sysadmin 角色內。



任務1：設定「取代回應警示之所有作業的 Token」
SQL Server Agent 可讓您在 Transact-SQL 作業步驟指令碼中使用 Token。
撰寫作業步驟時使用 Token，所賦予您的彈性與撰寫軟體程式時使用的變數一樣。
在作業步驟指令碼中插入 Token 後，SQL Server Agent 就會先在執行階段取代此 Token，然後再由 Transact-SQL 子系統執行作業步驟。

--01 設定「取代回應警示之所有作業的 Token」
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

請參考下圖所示：





任務2：建立存放「死結(DeadLock)」記錄的資料表：msdb.dbo.DeadlockEvents

-- 01 建立存放「死結(DeadLock)」記錄的資料表：msdb.dbo.DeadlockEvents
USE msdb;
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND type in (N'U'))
DROP TABLE [dbo].[DeadlockEvents]
GO
CREATE TABLE dbo.DeadlockEvents
(Rid INT IDENTITY, AlertTime DATETIME, DeadlockGraph XML) ;
GO

任務3：建立「作業(Job)」與「步驟(Step)」

--01 建立「作業(Job)」，用於當發生「死結」時，使用「警示」來觸發執行此「作業」。
USE [msdb]
GO
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'記錄「死結」資訊')
EXEC msdb.dbo.sp_delete_job @job_name=N'記錄「死結」資訊', @delete_unused_schedule=1
GO

EXEC  msdb.dbo.sp_add_job @job_name=N'記錄「死結」資訊',
@enabled=1, @owner_login_name = 'sa',
@notify_level_eventlog=3,
@description=N'此「作業」用來記錄 DEADLOCK_GRAPH 事件' ;
GO

--02 建立「步驟(Step)」：發生「死結」時，將「死結」資訊由 Service Broker 寫入到資料表：DeadlockEvents。
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'記錄「死結」資訊',
@step_name=N'新增資料列到資料表：DeadlockEvents',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command=N'INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
@database_name=N'msdb' ;
GO

--03 指派此「作業」給本機伺服器使用。若未指定，此「作業」將以「多伺服器作業」方式存在
EXEC msdb.dbo.sp_add_jobserver @job_name = N'記錄「死結」資訊' ;
GO

請參考下圖所示：




任務4：建立「警示(Alert)」：發生「死結」，使用此「警示」來回應，並執行「作業」

--01 建立「警示(Alert)」：發生「死結」，使用此「警示」來回應，並執行「作業」。
USE [msdb]
GO
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'回應 DEADLOCK_GRAPH 事件')
EXEC msdb.dbo.sp_delete_alert @name=N'回應 DEADLOCK_GRAPH 事件'
GO
EXEC msdb.dbo.sp_add_alert @name=N'回應 DEADLOCK_GRAPH 事件',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name=N'記錄「死結」資訊' ;
GO

請參考下圖所示：


由上，已經完成使用「警示」來偵測「死結」的組態。
請自行在設定此「警示」要使用的「操作員(Operator)」來做回應。

---

在建立完成後，若發生「死結」事件，可以查詢資料表來取得與「死結」相關的資料。

--01 若是發生「死結」事件，可以使用以下的 XQuery 來查詢資料
SELECT Rid, AlertTime ,DeadlockGraph.query('/TextData/deadlock-list') N'DeadlockGraph_XML'
FROM msdb.dbo.DeadlockEvents
ORDER BY Rid DESC

請參考下圖所示：




請將此 XML 文件存檔後，修改其附檔名為 *.xdl。
在開啟此 *.xdl 檔案，就可以在 SSMS 管理工具內，使用圖型介面方式來閱讀與「死結」相關的資料。
請參考下圖所示：

---

參考資料：
於作業步驟使用 Token
http://msdn.microsoft.com/zh-tw/library/ms175575.aspx

伺服器事件的 WMI 提供者概念
http://msdn.microsoft.com/zh-tw/library/ms180560.aspx

範例：使用伺服器事件的 WMI 提供者建立 SQL Server Agent 警示
http://msdn.microsoft.com/zh-tw/library/ms186385.aspx

WMI Event Alerts
http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/26/WMI-Event-Alerts.aspx

Event Notifications
http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/22/Event-Notifications.aspx