Deadlock monitor management by wmi
אחד מהדברים החשובים בתפקידו של ה-DBA זה לדעת מה קורה בשרת שלך, לייתר דיוק האם אתה מנטר נעילות בשרת.
כמובן, יש להבדיל בין נעילות אפליקטיביות (שגם אותן נדרש לנטר אך לא נעסוק בהם במאמר זה) לבין נעילות מסוג Deadlock.
Deadlock – מצב של נעילה מעגלית שבו מספר טרנזקציות ממתינות זו לזו כדי לעדכן מקור נתונים מסוים המוחזק ע"י האחרת.
אני רוצה להציג דרך פשוטה יחסית ופחות מוכרת שבה אני מנטר את נעילות מסוג Deadlock אצלנו במערכת.
הניתור בנוי על Alerts של SQL Server של wmi – Windows Management Instrumentation .
כאשר נרשמת נעילת Deadlock היא נרשמת בwmi.
אנו בונים "אזהרה" שמנתרת את הService של SQL Server ומחפשת DEADLOCK_GRAPH.
כאשר נמצאה שורה העונה על השאילתה האזהרה מפעילה JOB.
בJOB ניתן לשמור את הנתונים לטבלה במסד נתונים ניהולי או לשלוח אותו במייל אלינו.
סקריפט ליצירת המנגנון
USE DBA;--Use your own management DB
GO
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL DROP TABLE DeadlockEvents ;
GO
CREATE TABLE DeadlockEvents (AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.
EXEC msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', @enabled=1,
@description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO
-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.
EXEC msdb.dbo.sp_add_jobstep @job_name = N'Capture Deadlock Graph', @step_name=N'Insert graph into LogEvents', @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'DBA';--Use your own management DB
GO
-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
GO
-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', @job_name='Capture Deadlock Graph' ;
GO
לאחר מכן בשביל לפענח את הנתונים שנכנסו לטבלה יש פרוצדורה שכתבה דיאנה סיטו(אני מקווה שפענחתי את שמה נכון – Diane Sithoo) שעושה את העבודה בצורה מעולה GetDeadLockInfo
צריך לדעת-
עדיף להפעיל זאת על שרתים מ- Cumulative Update 5 for SQL Server 2005 Service Pack 2 ומעלה.מכוון שיש באג שקשור לwmi .
על 2 מסדי הנתונים יש להפעיל Service Broker – מסד הנתונים שבוא אנו נאסוף את הנתונים וmsdb .
בחלק מהמקרים שינינו את טיפוס העמודה DeadlockGraph בטבלה מxml לnvarchar(max) וביצענו את ההמרה בתצוגה.
