利用扩展事件监控死锁

  • 第1步,创建扩展事件,捕捉 deadlock / 自动增长 / 收缩 操作
USE master 
GO 

--.1.清理同名对象
IF EXISTS (SELECT name FROM sys.server_event_sessions WHERE name = 'xe_deadlock') 
DROP EVENT SESSION xe_deadlock ON SERVER 
GO 

--.2.创建日志目录
EXEC xp_create_subdir 'c:\xevent' 

--.3.创建事件会话 
CREATE EVENT SESSION xe_deadlock 
  ON SERVER 
  ADD EVENT lock_deadlock (   --.旧版Trace/Profiler中的事件类,不建议使用,但login_name及db_name及session_id准确
    ACTION ( sqlserver.database_name 
      ,sqlserver.transaction_id 
      ,sqlserver.client_app_name 
      ,sqlserver.client_hostname 
      ,sqlserver.server_principal_name 
      ,sqlserver.server_instance_name 
      ,sqlserver.session_id 
      ,sqlserver.sql_text 
      ,sqlserver.username ) )
 ,ADD EVENT xml_deadlock_report (   --.2012及以后版本推荐,其XML数据包含了牺牲者列表/参与进程列表和资源列表,但很多信息不准确
    ACTION ( sqlserver.database_name 
      ,sqlserver.client_app_name 
      ,sqlserver.client_hostname 
      ,sqlserver.server_principal_name 
      ,sqlserver.server_instance_name 
      ,sqlserver.session_id 
      ,sqlserver.sql_text 
      ,sqlserver.username ) ) 
 ,ADD EVENT blocked_process_report (   --.捕获阻塞
    ACTION ( sqlserver.database_name 
      ,sqlserver.transaction_id 
      ,sqlserver.client_app_name 
      ,sqlserver.client_hostname 
      ,sqlserver.server_principal_name 
      ,sqlserver.server_instance_name 
      ,sqlserver.session_id 
      ,sqlserver.sql_text 
      ,sqlserver.username ) ) 
 ,ADD EVENT database_file_size_change (   --.捕获自增长/收缩
    ACTION ( sqlserver.server_instance_name 
      ,sqlserver.client_hostname 
      ,sqlserver.database_id 
      ,sqlserver.session_id 
      ,sqlserver.database_name 
      ,sqlserver.server_principal_name 
      ,sqlserver.username ) ) 
  ADD TARGET package0.event_file ( 
    SET filename = N'c:\xevent\xe_deadlock.xel'     --.存储路径及文件名前缀 
      ,max_file_size = 1024                         --.最大文件大小,单位MB 
      ,max_rollover_files = 5 )                     --.启用文件滚动存储的最大文件数 
  WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON) 

--.4.启用事件会话
ALTER EVENT SESSION xe_deadlock ON SERVER STATE = START
  • 第2步,模拟一次死锁过程
USE master
IF DB_ID('demo') IS NULL CREATE DATABASE demo COLLATE Chinese_PRC_CI_AS
GO 

--.1.创建演示表及数据
USE demo
GO 

IF OBJECT_ID('Bookings') IS NOT NULL DROP TABLE Bookings;
IF OBJECT_ID('Payments') IS NOT NULL DROP TABLE Payments;

CREATE TABLE Bookings (ID INT PRIMARY KEY, Status VARCHAR(10), Amount DECIMAL(10,2));
CREATE TABLE Payments (ID INT PRIMARY KEY, BookingID INT, Amount DECIMAL(10,2));

INSERT INTO Bookings VALUES (1, 'Pending', 100.00);
INSERT INTO Payments VALUES (101, 1, 100.00);

--.2.执行如下代码(会话 A)
-- ==================== 会话 A ====================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  UPDATE Bookings SET Status = 'Confirmed' WHERE ID = 1;
  WAITFOR DELAY '00:00:05';
  UPDATE Payments SET Amount = 95.00 WHERE BookingID = 1;
COMMIT TRANSACTION;

--.3.新开一个查询窗口, 在 5 秒内执行如下代码, 等待约 5-10 秒则触发死锁
-- ==================== 会话 B ====================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  UPDATE Payments SET Amount = 90.00 WHERE ID = 101;
  WAITFOR DELAY '00:00:05';
  UPDATE Bookings SET Amount = 95.00 WHERE ID = 1;
COMMIT TRANSACTION;
  • 第3步,批量写入 100w 条记录,模拟数据库的自动增长操作
USE demo 
GO 

--.调整一下自增长属性
ALTER DATABASE demo MODIFY FILE ( NAME = N'demo', FILEGROWTH = 50MB) 
ALTER DATABASE demo MODIFY FILE ( NAME = N'demo_log', FILEGROWTH = 100MB) 
GO 

--.创建测试表
IF OBJECT_ID('tab001') IS NOT NULL DROP TABLE tab001 
CREATE TABLE tab001(id int, name sysname) 
GO 

--.插入100w测试数据
INSERT INTO tab001 (id, name) 
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),a.name FROM sys.all_objects a CROSS JOIN sys.all_objects b
  • 第4步,查看扩展事件捕获的操作
--.查看扩展事件捕获的操作,定制脚本
-- SELECT CAST(event_data AS XML) AS event_xml FROM sys.fn_xe_file_target_read_file('c:\xevent\xe_deadlock*.xel',NULL,NULL,NULL)
SELECT TOP 100 
   x.value('(event/action[@name="server_instance_name"])[1]', 'nvarchar(1000)') AS server_instance_name 
  ,x.value('(event/action[@name="server_principal_name"])[1]', 'nvarchar(100)') AS login_name 
  ,x.value('(event/action[@name="database_name"])[1]','nvarchar(100)') AS database_name 
  ,x.value('(event/action[@name="client_app_name"])[1]', 'nvarchar(1000)') AS client_app_name 
  ,DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP), x.value('(event/@timestamp)[1]','datetime')) AS run_time 
  ,x.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text          --.针对 xml_deadlock_report 原始文件有详情
  ,x.value('(event/@name)[1]', 'varchar(100)') AS event_name 
  ,x.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id                --.死锁
  ,x.value('(event/data[@name="resource_type"]/text)[1]', 'nvarchar(255)') AS resource_type   --.死锁
  ,x.value('(event/data[@name="mode"]/text)[1]', 'nvarchar(255)') AS mode                     --.死锁
  ,x.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms         --.自增长
  ,x.value('(event/action[@name="database_id"])[1]','int') AS database_id                     --.自增长
  ,x.value('(event/data[@name="file_id"])[1]', 'int') AS file_id                              --.自增长
  ,x.value('(event/data[@name="file_type"]/text)[1]', 'nvarchar(255)') AS file_type           --.自增长 
  ,x.value('(event/data[@name="file_name"]/value)[1]', 'nvarchar(255)') AS file_name          --.自增长
  ,x.value('(event/data[@name="size_change_kb"]/value)[1]', 'int') / 1024 AS growth_mb        --.自增长
FROM ( 
  SELECT x = CAST(event_data AS XML).query('.') 
  FROM sys.fn_xe_file_target_read_file('c:\xevent\xe_deadlock*.xel',NULL,NULL,NULL) 
) t 
WHERE x.value('(event/@timestamp)[1]','datetime') > '2026-08-30 00:00:00.000'                   --.只看xxx区间的操作
--AND x.value('(event/@name)[1]', 'varchar(100)') = 'database_file_size_change'                 --.只看自增长事件
--AND x.value('(event/action[@name="server_principal_name"])[1]', 'nvarchar(100)') <> 'sa'      --.排除sa的操作
--AND x.value('(event/action[@name="database_name"])[1]','nvarchar(100)') = 'demo'              --.只看demo库的操作
--AND x.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') LIKE '%t1%'         --.查看包含xxx关键字的操作
ORDER BY x.value('(event/@timestamp)[1]','datetime') DESC

返回结果如下:

Copyright © www.sqlfans.cn 2021 - 2026 All Right Reserved更新时间: 2026-11-03 12:45:51

results matching ""

    No results matching ""