SQL Server - 拡張イベントで収集できるイベントデータを確認するクエリ

拡張イベントで収集できるイベントデータを確認するクエリです。

動的管理ビューを3つ使います。sys.dm_xe_packages、sys.dm_xe_objects、sys.dm_xe_object_columnsの3つ。

動的管理ビュー 概要
sys.dm_xe_packages (Transact-SQL) パッケージ一覧。イベントはパッケージによってグルーピングされています。
sys.dm_xe_objects (Transact-SQL) イベント(オブジェクト)一覧。収集したいイベントを確認できます。
sys.dm_xe_object_columns (Transact-SQL) オブジェクトのカラム一覧。イベントが収集されるときにどんなデータが含まれるかを確認できます。イベントデータというかイベントの属性というか。

収集できるイベントデータを確認するクエリがこちらです。例としてsql_batch_completedイベントのイベントデータを確認しています。

-- sql_batch_completedイベントのイベントデータを確認する
select
    xe_oc.name as column_name,
    xe_oc.column_type as column_type,
    xe_oc.type_name as type_name,
    xe_oc.description as column_description
from sys.dm_xe_packages as xe_p
    inner join sys.dm_xe_objects as xe_o
        on xe_p.guid = xe_o.package_guid
    inner join sys.dm_xe_object_columns as xe_oc
        on xe_o.package_guid = xe_oc.object_package_guid and
            xe_o.name = xe_oc.object_name
where
    -- sys.dm_xe_objectsにはアクション・ターゲットなどイベント以外も含まれるので
    -- object_type = N'event'でフィルタ
    xe_o.object_type = N'event' and
    xe_p.name = N'sqlserver' and
    xe_o.name = N'sql_batch_completed';

結果です。(空白などは調整してあります。)

column_name         column_type   type_name          column_description
------------------- ------------- ------------------ -------------------------------------------------------------------------------------
UUID                readonly      guid_ptr           グローバル一意 ID
VERSION             readonly      uint8              イベント スキーマのバージョン
CHANNEL             readonly      etw_channel        ETW チャネル
KEYWORD             readonly      keyword_map        関連キーワード
collect_batch_text  customizable  boolean            0 に設定すると、collect_batch_text によって batch_text の収集が無効になります。既定では、batch_text 列が生成されます。
cpu_time            data          uint64             バッチによって使用される CPU 時間 (ミリ秒)。
duration            data          uint64             バッチ完了にかかった時間 (マイクロ秒)。
physical_reads      data          uint64             バッチによって発行された、物理ページの読み取り回数。
logical_reads       data          uint64             バッチによって発行された、論理ページの読み取り回数。
writes              data          uint64             NULL
row_count           data          uint64             バッチによって返された行数。
result              data          rpc_return_result  バッチの実行結果を示します。
batch_text          data          unicode_string     実行されているバッチのテキスト。

column_typeがdataの行が実行時に収集されるイベントデータです。column_typeがcustomizableの行は収集するかどうかを設定できます。readonlyは固定値?まだよくわからず。

ついでにrpc_completedイベントのイベントデータも確認しておきます。where句以外は上記と同じクエリです。

-- rpc_completedイベントのイベントデータを確認する
select
    xe_oc.name as column_name,
    xe_oc.column_type as column_type,
    xe_oc.type_name as type_name,
    xe_oc.description as column_description
from sys.dm_xe_packages as xe_p
    inner join sys.dm_xe_objects as xe_o
        on xe_p.guid = xe_o.package_guid
    inner join sys.dm_xe_object_columns as xe_oc
        on xe_o.package_guid = xe_oc.object_package_guid and
            xe_o.name = xe_oc.object_name
where
    xe_o.object_type = N'event' and
    xe_p.name = N'sqlserver' and
    xe_o.name = N'rpc_completed';

結果。

column_name                column_type   type_name                 column_description
-------------------------- ------------- ------------------------- --------------------------------------------------------------------------------------------------------------
UUID                       readonly      guid_ptr                  グローバル一意 ID
VERSION                    readonly      uint8                     イベント スキーマのバージョン
CHANNEL                    readonly      etw_channel               ETW チャネル
KEYWORD                    readonly      keyword_map               関連キーワード
collect_statement          customizable  boolean                   0 に設定すると、collect_statement によって statement の収集が無効になります。既定では、statement 列が生成されます。
collect_data_stream        customizable  boolean                   1 に設定すると、collect_data_stream によって data_stream の収集が有効になります。既定では、data_stream 列は生成されません。
collect_output_parameters  customizable  boolean                   1 に設定すると、collect_output_parameters によって output_parameters の収集が有効になります。既定では、output_parameters 列は生成されません。
cpu_time                   data          uint64                    NULL
duration                   data          uint64                    リモート プロシージャ呼び出しが完了するまでにかかった時間 (マイクロ秒)。
physical_reads             data          uint64                    リモート プロシージャ呼び出しによって発行された、物理ページの読み取り回数。
logical_reads              data          uint64                    リモート プロシージャ呼び出しによって発行された、論理ページの読み取り回数。
writes                     data          uint64                    NULL
result                     data          rpc_return_result         リモート プロシージャ呼び出しからの戻り値。
row_count                  data          uint64                    リモート プロシージャ呼び出しによって返された行数。
connection_reset_option    data          connection_reset_option   接続のリセットに使用されたオプション。このフィールドは、接続のリセット リモート プロシージャ呼び出しでのみ使用できます。
object_name                data          unicode_string            リモート プロシージャ呼び出しで参照されていたオブジェクトの名前。
statement                  data          unicode_string            リモート プロシージャ呼び出しで実行されたステートメントのテキスト。
data_stream                data          binary_data               トレースの再生に使用される、リモート プロシージャ呼び出しのバイナリ ストリーム。
output_parameters          data          xml                       リモート プロシージャ呼び出しから返された出力パラメーター。

SQL Server 2016で確認しています。

SQL Server - トレースイベントに対応する拡張イベントを確認するクエリ

拡張イベントを調べています。勉強した小ネタを残していこうと思います。

今回はトレースイベントに対応する拡張イベントを確認するクエリです。sys.trace_eventsとsys.trace_xe_event_mapを使います。

例として次の2つのトレースイベントに対応する拡張イベントを取得したいとします。

クエリは次のような感じに。

-- トレースイベントに対応する拡張イベントを確認する
select
    te.trace_event_id as te_event_id,   -- トレースイベントID
    te.name as te_event_name,       -- トレースイベント名
    xe.package_name as xe_package_name, -- 拡張イベントパッケージ名
    xe.xe_event_name as xe_event_name   -- 拡張イベント名
from sys.trace_events as te
    inner join sys.trace_xe_event_map as xe
        on  te.trace_event_id = xe.trace_event_id
where
    te.name in (N'RPC:Completed', N'SQL:BatchCompleted');

-- 結果
/*
te_event_id  te_event_name        xe_package_name      xe_event_name
------------ -------------------- -------------------- --------------------
10           RPC:Completed        sqlserver            rpc_completed
12           SQL:BatchCompleted   sqlserver            sql_batch_completed
*/

いずれは拡張イベントでいわゆるスロークエリを取得したいと思っているのでこの2つのイベントを例として選んでみました。

T-SQL - OVER句のROWS句のサンプル

OVER句のROWS句がすごいなと思ったので、少し整理していくつかサンプルを残しておきます。

OVER 句 (Transact-SQL)

OVER句を使うと行をグルーピングしたり、行に順位付けをしたり、行セットの範囲を指定したりといったことができます。

OVER句の括弧の中には次にようにPARTITION BY句、ORDER BY句、ROWS句を書きますが、今回はグルーピングするためのPARTITION BY句は使わず、集約関数とあわせて使うORDER BY句、ROWS句を見ていきたいと思います。

OVER(
    PARTITION BY カラム名
    ORDER BY カラム名
    ROWS(RANGE) なんとか
)

ROWS句

ROWS句は結果セットの範囲を指定する機能です。現在の行からみた前後の行の範囲を指定します。おそらくこの範囲のことを「ウィンドウ」と言うのかなと。

次の書き方で行セットをAからBの範囲内に絞ります。

rows between A and B

AとBの書き方としては5パターンあります。

-- 最初の行
unbounded preceding

-- 現在の行からみてn行前の行
n preceding

-- 現在の行
current row

-- 現在の行からみてn行後の行
n following

-- 最後の行
unbounded following

AとBを埋めていくつかROWS句を書いてみます。

-- 最初の行から現在の行まで
rows between unbounded preceding and current row

-- 1つ前の行から1つの後の行まで
rows between 1 preceding and 1 following

-- 1つ前の行から1つ前の行まで(つまり1つ前の行)
rows between 1 preceding and 1 preceding

ROWS句を使ったサンプル

ROWS句を使ったクエリを書いてみます。まずは月別の売上みたいなテストデータがあるとします。

drop table if exists #Sale;

select *
into #Sale
from (values
    (N'201610', 100),
    (N'201611', 200),
    (N'201612', 300),
    (N'201701', 200),
    (N'201702', 400),
    (N'201703', 200)) as Src([Date], Sale);

select *
from #Sale
order by [Date];

/*
Date   Sale
------ -----------
201610 100
201611 200
201612 300
201701 200
201702 400
201703 200
*/

移動平均を求める

例えば直近3ヶ月間の平均といったものを移動平均と言うようです。

ROWS句に2行前の行から現在の行を指定して、直近3ヶ月の移動平均を計算してみます。わかりやすいように直近3ヶ月の合計も計算しておきます。

-- 移動平均
select *,
    sum(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の合計,
    avg(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の移動平均
from #Sale
order by [Date];

/*
Date   Sale        直近3ヶ月の合計    直近3ヶ月の移動平均
------ ----------- ----------- -----------
201610 100         100         100
201611 200         300         150
201612 300         600         200
201701 200         700         233
201702 400         900         300
201703 200         800         266
*/

1行目(Dateが201610)は前月と前々月の行がないので、合計も平均も現在の行の値そのままになっています。2行目(Dateが201611)は前々月の行がないので2ヶ月分の合計と平均になっています。

累計を求める

最初の行からその行までの累計を計算してみます。

-- 累計
select *,
    sum(Sale) over(
        order by [Date]
        rows between unbounded preceding and current row
    ) as 累計
from #Sale
order by [Date];

/*
Date   Sale        累計
------ ----------- -----------
201610 100         100
201611 200         300
201612 300         600
201701 200         800
201702 400         1200
201703 200         1400
*/

実はこれ数年前に試しました。

ichiroku11.hatenablog.jp

このとき書いたクエリはこのROWS句を省略したようなものでした。(このときはROWS句をよく理解していなかったような気が・・・。)

別の行と比較する

ROWS句では1つ前の1行、1つ後の1行というようにピンポイントで行を指定できるので、ある行と現在の行を比較することもできます。1つの前の行と比較して、前月からの増減を計算してみます。わかりやすいように前月のSaleの値も取得しています。

--- 前月からの増減
select *,
    sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月のSale,
    Sale - sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月からの増減
from #Sale
order by [Date];
/*
Date   Sale        前月のSale     前月からの増減
------ ----------- ----------- -----------
201610 100         NULL        NULL
201611 200         100         100
201612 300         200         100
201701 200         300         -100
201702 400         200         200
201703 200         400         -200
*/

1行目(Dateが201610)は前月の行がないので前月の値がNULLになっています。

ROWS句すごいなと。

Getterのみ自動実装プロパティの初期化子のメモ

うっかりやってしまいそうだなと思ったのでサンプルを残しておきます。

C# 6.0からの機能に

  • 自動実装プロパティの初期化子(Getterのみの自動実装プロパティ)
  • ラムダ式本体によるプロパティの記述

があります。

使う側からするとどちらも同じGetterプロパティですが、動きは微妙に違います。ちゃんと理解して使いわけたいと思います。

というより初期化するつもりでプロパティ内でメソッドを呼び出す場合は、うっかりラムダ式を書かないように。

class Sample {
    private static int GetValue([CallerMemberName]string caller = null) {
        // どのプロパティから呼ばれたか
        Console.WriteLine($"#{nameof(GetValue)} from {caller}");
        return 1;   // 値に意味なし
    }

    public Sample() {
        Console.WriteLine($"#{nameof(Sample)} Constructor");
    }

    // 自動実装プロパティの初期化子
    public int Value1 { get; } = GetValue();

    // ラムダ式本体によるプロパティ
    public int Value2 => GetValue();
}

class Program {
    static void Main(string[] args) {
        // Value1プロパティのGetValueメソッドは、
        // newしたタイミングでコンストラクタ本体より先に実行される
        Console.WriteLine($"new {nameof(Sample)}()");
        var sample = new Sample();
        // 結果:
        //new Sample()
        //#GetValue from Value1
        //#Sample Constructor

        // Value1プロパティを参照してもGetValueメソッドは実行されない
        Console.WriteLine($"{nameof(sample.Value1)}");
        var value1 = sample.Value1;
        // 結果:
        //Value1

        // Value2プロパティを参照するごとにGetValueメソッドが実行される
        Console.WriteLine($"{nameof(sample.Value2)}");
        var value2 = sample.Value2;
        // 結果:
        //Value2
        //#GetValue from Value2
    }
}

Path.GetDirectoryNameでフォルダパスを取得

パス文字列からフォルダパスを取得したいときはPath.GetDirectoryNameを使います。

Path.GetDirectoryName メソッド (String) (System.IO)

このメソッド名だとフォルダパスじゃなくてフォルダ名を取得できる感じが・・・というのは置いておいて。

上記にもサンプルがのっていますが、他にもいくつかペタっとしておきます。

// よくある絶対パス
Console.WriteLine(Path.GetDirectoryName(@"c:\folder\file.txt"));
// c:\folder

// 区切り文字で終わっているパス
// 先頭から最後の区切り文字まで(区切り文字は含まない)の文字列を取得する
Console.WriteLine(Path.GetDirectoryName(@"c:\folder1\folder2\"));
// c:\folder1\folder2

// 区切り文字は"\"でも"/"でも大丈夫
Console.WriteLine(Path.GetDirectoryName(@"c:/folder/file.txt"));
// c:\folder

// 相対パスでも大丈夫みたい
Console.WriteLine(Path.GetDirectoryName(@"folder\file.txt"));
// folder

// 相対パスその2
Console.WriteLine(Path.GetDirectoryName(@"..\..\file.txt"));
// ..\..

// ルート
Console.WriteLine(Path.GetDirectoryName(@"c:\") == null ? "null" : "nullではない");
// null

// ルートその2
Console.WriteLine(Path.GetDirectoryName(@"\\server\") == null ? "null" : "nullではない");
// null

Path.GetDirectoryNameはパス文字列の先頭から最後の区切り文字まで(区切り文字は含まない)の文字列を取得するメソッドということで。ただしパス文字列がルートの場合はnullになると。