sqlserver 扩展事件监控慢sql与阻塞sql，xml格式日志解析

零、 启用参数

SqlServer中默认blocked process收集是不开启的，可以通过以下sql查看，值为0表示未开启。

exec sp_configure 'show advanced options',1;
RECONFIGURE;
exec sp_configure 'blocked process threshold (s)';

执行以下脚本，记录所有阻塞超过10s的进程

USE master;
EXEC sp_configure 'blocked process threshold (s)',10
RECONFIGURE WITH OVERRIDE;

一、 创建扩展事件

• 记录执行超过10s的sql（含执行计划）
• 记录阻塞超过10s的sql
• 记录至event_file，每个文件50M，共10个循环

2012版本开始扩展事件创建可以用图形化界面，但是要批量创建还是脚本方便，监控字段、阈值、event_file保留可以根据时间情况调整。

CREATE EVENT SESSION [slow_sql] ON SERVER 
ADD EVENT sqlserver.blocked_process_report(ACTION(package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([duration]>=(10000000))),
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_equal_uint64]([duration],(10000000))))
ADD TARGET package0.event_file(SET filename=N'slow_sql',max_file_size=(50),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

启动扩展事件

ALTER EVENT SESSION [slow_sql] ON SERVER STATE = START;

记录的内容大概如下：

还可以看到查询的执行计划

长时间阻塞sql记录

blocked_process字段可以看到xml格式的阻塞详情

二、 阻塞sql xml格式日志解析

你应该也发现了人工看xml格式的日志记录是很不方便的，如果数量少还好，如果几十上百个简直能看瞎。所以我们还需要解析一下这个日志格式，即把xml格式转为常见的表格形式。

首先查询扩展事件日志文件路径

select s.name as xe_session_name,
    cast(st.target_data as xml) as target_data
from sys.dm_xe_sessions s 
inner join sys.dm_xe_session_targets st 
    on s.address=st.event_session_address
where s.name='slow_sql';

xml格式转换sql是比较消耗资源的，如果日志文件很多，执行时间可能会比较长，可以适当加些过滤条件。如果不想每次执行一个这么长的语句，可以把它建成视图。

SELECT 
-- [XML DATA] AS [Raw XML],
 DATEADD(hour,8,[XML Data].value('(/event[@name=''blocked_process_report'']/@timestamp)[1]','DATETIME')) AS [TimeStamp],
 [XML Data].value('(/event/data[@name=''database_name'']/value)[1]','SYSNAME') AS [Database Name],
 [XML Data].value('(/event/data[@name=''lock_mode'']/text)[1]','SYSNAME') AS [Lock Mode],
 [XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT')/1024 AS [Duration (ms)],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/@waitresource)[1]','SYSNAME') AS [Wait Resource],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/@spid)[1]','SYSNAME') AS [Blocked Spid],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/inputbuf)[1]','SYSNAME') AS [Blocked Query],
  [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','SYSNAME') AS [Blocked sqlhandle],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/@spid)[1]','SYSNAME') AS [Blocking Spid],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/inputbuf)[1]','SYSNAME') AS [Blocking Query],
 [XML Data].value('(/event/data[@name=''blocked_process'']/value/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]','SYSNAME') AS [Blocking sqlhandle]
FROM
    (SELECT CONVERT(XML, event_data) AS [XML Data]
	FROM  sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\slow_sql*.xel',NULL,NULL,NULL) where OBJECT_NAME='blocked_process_report') AS v;

效果大致如下

补充1：性能优化

补充2：使用 Transact-SQL 查看诊断日志文件

-- 使用 Transact-SQL 查看诊断日志文件
SELECT  
xml_data.value('(event/@name)[1]','varchar(max)') AS 'Name'  
,xml_data.value('(event/@package)[1]','varchar(max)') AS 'Package'  
,xml_data.value('(event/@timestamp)[1]','datetime') AS 'Time'  
,xml_data.value('(event/data[@name=''state'']/value)[1]','int') AS 'State'  
,xml_data.value('(event/data[@name=''state_desc'']/text)[1]','varchar(max)') AS 'State Description'  
,xml_data.value('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'Failure Conditions'  
,xml_data.value('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name'  
,xml_data.value('(event/data[@name=''instancename'']/value)[1]','varchar(max)') AS 'Instance Name'  
,xml_data.value('(event/data[@name=''creation time'']/value)[1]','datetime') AS 'Creation Time'  
,xml_data.value('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component'  
,xml_data.value('(event/data[@name=''data'']/value)[1]','varchar(max)') AS 'Data'  
,xml_data.value('(event/data[@name=''info'']/value)[1]','varchar(max)') AS 'Info'  
FROM  
 ( SELECT object_name AS 'event'  
  ,CONVERT(xml,event_data) AS 'xml_data'  
  FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLNODE1_MSSQLSERVER_SQLDIAG_0_129936003752530000.xel',NULL,NULL,NULL)   
)   
AS XEventData  
ORDER BY Time;

参考

Extracting ShowPlan XML from SQL Server Extended Events

Identifying Blocking via Extended Events – SQLServerCentral

SQL Server 扩展事件 - 悦光阴 - 博客园

SQLServer2012基于扩展事件的阻塞监控 - 郭大侠1 - 博客园

三，如何监控和解决SQL Server的阻塞(3) (扩展事件）_zhengfeng2100的博客-优快云博客_sqlserver.locks

确定持有锁的查询 - SQL Server | Microsoft Learn

（4.31）sql server中的xml数据操作 - 郭大侠1 - 博客园

（4.28）for xml path 在合并拆分上的作用演示 - 郭大侠1 - 博客园

SQLServer2012基于扩展事件的阻塞监控 - 郭大侠1 - 博客园

示例：使用 OPENXML - SQL Server | Microsoft Learn

SQLServer 读取XML类型的节点数据_薛定谔的DBA的博客-优快云博客_sqlserver 读取xml

SQLServer 从xml 文件中提取节点数据到数据库中_薛定谔的DBA的博客-优快云博客

SqlServer XML数据类型DML相关操作（图文结合）_薛定谔的DBA的博客-优快云博客