Extended Events 세션 생성: 느린 쿼리를 파일로 수집하고 읽기

명령어

SET QUOTED_IDENTIFIER ON;
CREATE EVENT SESSION [dba_long_queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name,
            sqlserver.session_id, sqlserver.sql_text, sqlserver.username, sqlserver.query_hash)
    WHERE (duration >= 1000000 AND sqlserver.is_system = 0)),   -- 1 s (microseconds)
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name,
            sqlserver.session_id, sqlserver.sql_text, sqlserver.username, sqlserver.query_hash)
    WHERE (duration >= 1000000 AND sqlserver.is_system = 0))
ADD TARGET package0.event_file (SET filename = N'D:\XE\dba_long_queries.xel',
                                    max_file_size = 256, max_rollover_files = 4)
WITH (MAX_MEMORY = 8 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 15 SECONDS, STARTUP_STATE = OFF);

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

-- Read:
SELECT x.value('(event/@name)[1]', 'sysname')                                AS event_name,
       x.value('(event/@timestamp)[1]', 'datetime2')                         AS utc_time,
       x.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000   AS duration_ms,
       x.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000   AS cpu_ms,
       x.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint')     AS logical_reads,
       x.value('(event/action[@name="database_name"]/value)[1]', 'sysname')  AS db,
       x.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS app,
       x.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM  (SELECT CAST(event_data AS XML) AS x
       FROM sys.fn_xe_file_target_read_file(N'D:\XE\dba_long_queries*.xel', NULL, NULL, NULL)) t
ORDER BY duration_ms DESC;

-- When done:
-- ALTER EVENT SESSION [dba_long_queries] ON SERVER STATE = STOP;
-- DROP EVENT SESSION [dba_long_queries] ON SERVER;

하는 일

1초보다 긴 문장과 RPC 호출을 애플리케이션, 호스트, 사용자, 데이터베이스, SQL 텍스트와 함께 디스크에 기록하는 경량 Extended Events 세션을 생성한다. .xel 파일을 T-SQL로 행으로 변환한다.

사용 시점

간헐적 성능 저하 불만이 있을 때, Query Store가 보여 주지 않는 클라이언트 정보(app/host)가 필요할 때, SQL Trace/Profiler의 대체 수단으로.

주의

매우 낮은 임계값(예: 0)은 바쁜 서버에서 파일을 빠르게 채우고 오버헤드를 추가하므로 사후가 아니라 WHERE predicate에서 필터링한다. sql_text action은 대량에서 비용이 크다. 폴더는 SQL Server 서비스 계정이 쓸 수 있어야 한다. duration과 cpu_time 단위는 마이크로초다. 끝나면 세션을 중지/삭제한다. STARTUP_STATE = OFF이면 재시작 후 시작되지 않는다.