使用XEVENT来捕获返回加过超过N行的查询

--=====================================================================
--创建XEVENT来捕获sql_statement_completed事件
CREATE EVENT SESSION [XE_QUERY_MONITOR] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(
    sqlserver.client_app_name,--应用程序名
    sqlserver.client_connection_id,--活动连接ID
    sqlserver.client_hostname,--应用程序服务器名
    sqlserver.database_name,--建立连接是打开的数据库名
    sqlserver.query_plan_hash,--查询计划hash
    sqlserver.username--登陆用户
    )
    WHERE (
    [package0].[greater_than_uint64]([sqlserver].[session_id],(55)) --限制session_id>55
    AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) --只捕获非系统事件
    AND [row_count]>(1000))--只捕获返回行数超过1000的查询
    ) 
ADD TARGET package0.ring_buffer(SET max_memory=(204800))
WITH (
    MAX_MEMORY=204800 KB,--要分配给会话用来缓冲事件的最大内存量为200MB
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,--允许事件在缓冲区满时丢失
    MAX_DISPATCH_LATENCY=30 SECONDS,--事件调度至事件会话目标前事件将在内存中缓冲的时间
    MAX_EVENT_SIZE=0 KB,--允许的最大事件大小
    MEMORY_PARTITION_MODE=NONE,--在 SQL Server 实例中创建一组缓冲区
    TRACK_CAUSALITY=OFF,--不允许将不同服务器连接上的相关事件关联在一起
    STARTUP_STATE=ON --在 SQL Server 启动时自动启动此事件会话
)
GO

--=============================================
--启动回话
ALTER EVENT SESSION [XE_QUERY_MONITOR]
ON SERVER
STATE=START
GO

--=============================================
--运行测试查询。。。


--==============================================
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
    event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS [Duration_MilliSeconds],
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS [cpu_time],
    event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'int') AS [physical_reads],
    event_data.value('(event/data[@name="writes"]/value)[1]', 'int') AS [writes],
    event_data.value('(event/data[@name="row_count"]/value)[1]', 'int') AS [row_count],
    event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(4000)') AS [cpu_time],
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(4000)') AS [client_app_name],
    event_data.value('(event/action[@name="client_connection_id"]/value)[1]', 'nvarchar(4000)') AS [client_connection_id],
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(4000)') AS [client_hostname],
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(4000)') AS [database_name],
    event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(4000)') AS [username]
FROM 
(    SELECT XEvent.query('.') AS event_data 
    FROM 
    (    -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
        WHERE name = 'XE_QUERY_MONITOR' 
          AND target_name = 'ring_buffer'
    ) AS Data 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
) AS tab (event_data)
​