淺談偵測「死結(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
Dear Sir:
回覆刪除已依照任務1~4做Job的設定,利用Update資料表Waitfor Delay的語法產生死結,但Job無法觸發。
查詢
1.msdb.dbo.DeadlockEvents資料表中無任個的資料。
2.job檢視記錄中無任何的執行記錄。
3.查詢sys.databases查詢msdb的Service Broker是啟用的。
不知還有什麼步驟是未設定,請賜教...
環境:Win2k8Svr+SQL2K8 Developer
Dear Sir:
回覆刪除已依照任務1~4做Job的設定,利用Update資料表Waitfor Delay的語法產生死結,但Job無法觸發。
查詢
1.msdb.dbo.DeadlockEvents資料表中無任個的資料。
2.job檢視記錄中無任何的執行記錄。
3.查詢sys.databases查詢msdb的Service Broker是啟用的。
不知還有什麼步驟是未設定,請賜教...
環境:Win2k8Svr+SQL2K8 Developer
請問是否記錄死結資料表一定要建於msdb?
回覆刪除建於user database可以嗎?
Q.請問是否記錄死結資料表一定要建於msdb?
回覆刪除A.可以建立在使用者資料庫內。