PART 04 — Extended Events による取得とセクション定義

CREATE EVENT SESSION [session_name] ON SERVER
ADD EVENT sqlserver.event_name (    -- 取得するイベント
    ACTION (
        sqlserver.sql_text,         -- 実行 SQL テキスト
        sqlserver.client_hostname,  -- クライアントホスト名
        sqlserver.database_name,    -- データベース名
        sqlserver.username,         -- 接続ユーザー
        sqlserver.session_id        -- セッション ID
    )
    WHERE (                         -- フィルタ条件
        [sqlserver].[database_name] = N'YourDatabase'
    )
)
ADD TARGET package0.event_file (   -- ファイルターゲット（ディスクへ保存）
    SET filename     = N'C:\XE\session_name.xel',
        max_file_size = 50,         -- MB
        max_rollover_files = 5
)
WITH (
    MAX_MEMORY            = 4096 KB,
    EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY  = 5 SECONDS,
    TRACK_CAUSALITY       = OFF     -- 関連イベントの因果関係追跡
);
GO

-- セッションを開始
ALTER EVENT SESSION [session_name] ON SERVER STATE = START;

-- セッションの停止
ALTER EVENT SESSION [session_name] ON SERVER STATE = STOP;

-- セッションの削除
DROP EVENT SESSION [session_name] ON SERVER;

-- system_health セッションのリングバッファからデッドロックグラフを取得
SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_time,
    xdr.query('.')                        AS deadlock_graph_xml
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets   t
    JOIN sys.dm_xe_sessions          s
        ON t.event_session_address = s.address
    WHERE s.name   = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdt(xdr)
ORDER BY deadlock_time DESC;

CREATE EVENT SESSION [capture_deadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (
    SET filename = N'C:\XE\deadlocks.xel',
        max_file_size = 100,
        max_rollover_files = 10
)
WITH (MAX_MEMORY = 4096 KB, MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION [capture_deadlocks] ON SERVER STATE = START;

CREATE EVENT SESSION [capture_slow_queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.session_id,
        sqlserver.plan_handle   -- 実行プランを後から取得するために記録
    )
    WHERE (
        [duration] >= 1000000         -- 1 秒 = 1,000,000 マイクロ秒以上
        AND [sqlserver].[database_name] = N'YourDatabase'
    )
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.session_id
    )
    WHERE ([duration] >= 1000000)
)
ADD TARGET package0.event_file (
    SET filename = N'C:\XE\slow_queries.xel',
        max_file_size = 200,
        max_rollover_files = 5
)
WITH (MAX_MEMORY = 8192 KB, MAX_DISPATCH_LATENCY = 30 SECONDS);
GO
ALTER EVENT SESSION [capture_slow_queries] ON SERVER STATE = START;

-- XEL ファイルを読み込んで低速クエリを一覧表示
SELECT
    event_data.value('(@timestamp)[1]',          'datetime2') AS event_time,
    event_data.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms,
    event_data.value('(data[@name="cpu_time"]/value)[1]', 'bigint') / 1000 AS cpu_ms,
    event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint')    AS logical_reads,
    event_data.value('(data[@name="physical_reads"]/value)[1]','bigint')    AS physical_reads,
    event_data.value('(action[@name="database_name"]/value)[1]','nvarchar(256)') AS db_name,
    event_data.value('(action[@name="username"]/value)[1]',     'nvarchar(256)') AS username,
    event_data.value('(action[@name="sql_text"]/value)[1]',     'nvarchar(max)') AS sql_text
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        'C:\XE\slow_queries*.xel', NULL, NULL, NULL
    )
) AS src
ORDER BY duration_ms DESC;

-- system_health セッションの event_file ターゲットのパスを確認
SELECT
    s.name AS session_name,
    t.target_name,
    CAST(t.target_data AS XML).value(
        '(EventFileTarget/File/@name)[1]', 'nvarchar(500)'
    ) AS file_path
FROM sys.dm_xe_sessions          s
JOIN sys.dm_xe_session_targets   t
    ON s.address = t.event_session_address
WHERE s.name = 'system_health'
  AND t.target_name = 'event_file';