利用扩展事件监控死锁
- 第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
返回结果如下:
