SQL SERVER-Extendevent捕获执行慢的语句

USE MASTER;
GO

/* Conditionally drop the session if it already exists */
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'EE_SlowQueryLog')
    DROP EVENT SESSION EE_SlowQueryLog ON SERVER;
 GO  

 /* Create the session */
CREATE EVENT SESSION EE_SlowQueryLog ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (   ACTION (
               sqlserver.client_app_name
              ,sqlserver.client_hostname
              ,sqlserver.database_id
              ,sqlserver.database_name
              ,sqlserver.plan_handle
              ,sqlserver.sql_text
              ,sqlserver.username)
         WHERE duration > 500000 /* 500 milliseconds in microseconds */ 
              AND sql_text NOT LIKE 'WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue)%' /* Exclude WMI waits */
              AND sql_text NOT LIKE '%sp_GetSlowQueries%' /* Exclude traffic from stored procedure to read data */)
    ADD TARGET package0.asynchronous_file_target
    (   SET FILENAME = N'D:\XEvent\EE_SlowQueryLog.xel', METADATAFILE = N'D:\XEvent\EE_SlowQueryLog.xem')
    WITH (max_dispatch_latency = 1 seconds);
 GO 

 /* Start Session */
 ALTER EVENT SESSION EE_SlowQueryLog ON SERVER STATE = START;
 GO 

 

--解析xel数据
select 
    SWITCHOFFSET(n.value('@timestamp','Datetime'),'+08:00') as EventTime,
    n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
    n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
    n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') as client_app_name,
    n.value('(action[@name="username"]/value)[1]', 'nvarchar(128)') as username,
    n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') as client_hostname
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\XEvent\EE_SlowQueryLog_0*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)

 

转载于:https://www.cnblogs.com/JinweiChang/p/11050359.html