【监控笔记】【2.3】扩展事件——慢查询SQL（执行超过3S的SQL）

--sql server 2008及以上才支持，2012及以上才支持GUI界面

【1】T-SQL实现

【1.1】T-SQL构建慢查询扩展日志

基于 rpc_completed（远程过程调用已完成时发生）事件与 sql_batch_completed（Transact-SQL 批处理执行完毕时发生）事件

--  删除事件会话  
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'slow_query')  
    DROP EVENT SESSION slow_query ON SERVER   
GO  
--  创建事件会话  
CREATE EVENT SESSION [slow_query] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
    WHERE  [duration]>=3000000                    -- 消耗秒数超 3 秒
    AND [sqlserver].[database_name]=N'asdegame'    -- 数据库名, 自行修改
    --and sqlserver.username=N'xxxx'                -- 数据库用户名, 自行修改
   
    ),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
    WHERE   [duration]>=3000000                    -- 消耗秒数超 3 秒
    AND [sqlserver].[database_name]=N'asdegame'   -- 数据库名, 自行修改
    --and sqlserver.username=N'xxxx'                -- 数据库用户名, 自行修改
  
    )
ADD TARGET package0.event_file(SET filename=N'E:\dba_tools\eventlog\slow_query.xel',max_file_size=(1000))    --路径、文件MB, 自行修改
WITH (STARTUP_STATE=ON)
GO
--  启用（停止）事件会话（START / STOP）  
ALTER EVENT SESSION slow_query ON SERVER STATE=START  
GO

【1.2】使用sql 查询扩展事件文件

SELECT CONVERT(XML,event_data) AS data
sys.Fn_xe_file_target_read_file(N'd:\xe_sessions\servername_ev_block_session.xel',NULL,NULL,NULL)

【1.3】转成表格查阅

with d as (
SELECT CONVERT(XML,event_data) AS data
from sys.Fn_xe_file_target_read_file(N'E:\dba_tools\eventlog\slow_query_0_132422809173040000.xel',NULL,NULL,NULL)
)
select
dateadd(hour,8,data.value('(/event/@timestamp)[1]','datetime')) as record_time, --获取最上方标题行的内容
data.value('(/event/@name)[1]','nvarchar(128)') as operation_name, --获取最上方标题行的内容
--data.value('(/event/data[@name="cpu_time"]/value)[1]','int')/1000 as 'cpu_time(ms)',--获得 event=>data name=cpu_time 的 value
data.value('(/event/data[@name="duration"]/value)[1]','int')/1000 as 'exec_time(ms)',--获得 event=>data name=duration 的 value
--data.value('(/event/data[@name="physical_reads"]/value)[1]','int') as 'physical_reads',--获得 event=>data name=physical_reads 的 value
--data.value('(/event/data[@name="logical_reads"]/value)[1]','int') as 'logical_reads',--获得 event=>data name=logical_reads 的 value
--data.value('(/event/data[@name="writes"]/value)[1]','int') as 'writes',--获得 event=>data name=writes 的 value
data.value('(/event/data[@name="row_count"]/value)[1]','int') as 'row_count',--获得 event=>data name=row_count 的 value
data.value('(/event/data[@name="result"]/value)[1]','int') as 'result_flag',--获得 event=>data name=result 的 value
data.value('(/event/data[@name="result"]/text)[1]','nvarchar(128)') as 'result_desc',--获得 event=>data name=result 的 text
data.value('(/event/data[@name="batch_text"]/value)[1]','nvarchar(max)') as 'batch_text',--获得 event=>data name=batch_text 的 text

data.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(4000)') as 'current_sql',--获得 event=>action name=sql_text 的 value
--data.value('(/event/action[@name="task_time"]/value)[1]','int')/1000 as 'task_time(ms)',--获得 event=>action name=task_time 的 value
data.value('(/event/action[@name="database_name"]/value)[1]','nvarchar(400)') as 'database_name',--获得 event=>action name=database_name 的 value
data.value('(/event/action[@name="transaction_id"]/value)[1]','nvarchar(400)') as 'transaction_id',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="username"]/value)[1]','nvarchar(400)') as 'username',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="nt_username"]/value)[1]','nvarchar(400)') as 'nt_username'--获得 event=>action name=transaction_id 的 value
from d