SQL Server - 拡張イベントを使ってスロークエリを取得するクエリ(ファイル編)

前の記事では、拡張イベントを使ってスロークエリを取得しつつ次の2つのクエリを書いてみました。

  • ターゲットにリングバッファ(メモリ)を指定してセッションを作成するクエリ
  • リングバッファに出力されたイベントデータを確認するクエリ

ichiroku11.hatenablog.jp

拡張イベントではターゲット(=イベントデータの出力先)にファイルも指定できます。ファイルを使ったクエリを前回と同じように書き残しておきたいと思います。

ターゲットにファイルを指定してセッションを作成するクエリ

add target句でターゲットにpackage0.event_fileを指定し、さらにパラメータとして出力先のファイルパスを指定します。それ以外は前回と同じクエリです。

-- イベントセッションを作成
create event session test_xes
    on server
    -- sql_batch_completedイベントをキャプチャする
    add event sqlserver.sql_batch_completed(
        -- duration(バッチが完了するまでの時間)が3秒以上のイベントにフィルタする
        where duration >= 3000000
    ),
    -- rpc_completedイベントをキャプチャする
    add event sqlserver.rpc_completed(
        where duration >= 3000000
    )
    -- イベントデータをファイルに出力する
    add target package0.event_file(
        -- 出力先のファイルパスを指定する
        set filename = N'C:\Temp\test_xes.xel'
    );

実際には出力先のファイル名の末尾に_0_{数字}がつきます。

-- こんな感じのファイル名になる
test_xes_0_131475877870900000.xel

ファイルに出力されたイベントデータを確認するクエリ

まずファイルを読み込むにはsys.fn_xe_file_target_read_file関数を使います。関数が返す結果セットのevent_dataカラムからイベントデータを取得できるので、あとはXQueryを使って何とかします。

with xe_e(data)
as(
    select cast(event_data as xml) -- データ
    -- ファイルを読み込む
    from sys.fn_xe_file_target_read_file(N'C:\Temp\test_xes_*.xel', null, null, null)
)
select
    data.value('(event/@package)[1]', 'nvarchar(10)') as package,
    data.value('(event/@name)[1]', 'nvarchar(20)') as event,
    data.value('(event/@timestamp)[1]', 'datetime2') as timestamp,
    data.value('(event/data[@name="duration"]/value)[1]', 'bigint') as duration,
    data.value('(event/data[@name="batch_text"]/value)[1]', 'nvarchar(max)') as batch_text,
    data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement
from xe_e;

-- 結果は前回と同じなので省略

参考