搜尋本站文章

2010-03-30

淺談偵測「死結(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

4 則留言:

  1. Dear Sir:
    已依照任務1~4做Job的設定,利用Update資料表Waitfor Delay的語法產生死結,但Job無法觸發。
    查詢
    1.msdb.dbo.DeadlockEvents資料表中無任個的資料。
    2.job檢視記錄中無任何的執行記錄。
    3.查詢sys.databases查詢msdb的Service Broker是啟用的。
    不知還有什麼步驟是未設定,請賜教...
    環境:Win2k8Svr+SQL2K8 Developer

    回覆刪除
  2. Dear Sir:
    已依照任務1~4做Job的設定,利用Update資料表Waitfor Delay的語法產生死結,但Job無法觸發。
    查詢
    1.msdb.dbo.DeadlockEvents資料表中無任個的資料。
    2.job檢視記錄中無任何的執行記錄。
    3.查詢sys.databases查詢msdb的Service Broker是啟用的。
    不知還有什麼步驟是未設定,請賜教...
    環境:Win2k8Svr+SQL2K8 Developer

    回覆刪除
  3. 請問是否記錄死結資料表一定要建於msdb?
    建於user database可以嗎?

    回覆刪除
  4. Q.請問是否記錄死結資料表一定要建於msdb?
    A.可以建立在使用者資料庫內。

    回覆刪除