FORMATMESSAGE関数を試す

t-sql

SQL Server 2016からFORMATMESSAGE関数が拡張されて、プレイスホルダーを使って文字列を組み立てられるようになりました。

FORMATMESSAGE (Transact-SQL) | Microsoft Docs

C言語のprintfみたい感じですかね。たぶん。

文字列を埋め込むには%sを使います。

-- 文字列を埋め込む
print formatmessage(N'Hello, %s!', N'World');

/*
Hello, World!
*/

数字の埋め込みには%dや%u、%xなどを使います。

-- 整数を埋め込む
print formatmessage(N'Hello, %d!', 1);

/*
Hello, 1!
*/

-- 符号なし整数、符号なし16進数を埋め込む
declare @value int = 10;
print formatmessage(N'10進数 %u => 16進数 %x', @value, @value);

/*
10進数 10 => 16進数 a
*/

詳しい書式の説明は、こちらのRAISERROR関数のドキュメントにあります。

RAISERROR (Transact-SQL) | Microsoft Docs

STRING_SPLIT関数で文字列を分割する

t-sql

SQL Server 2016から使えるSTRING_SPLIT関数を試してみます。

STRING_SPLIT (Transact-SQL) | Microsoft Docs

この関数は1つ目の引数の文字列を2つ目の引数の文字で分割します。戻り値はvalueカラムを1つだけ持つ表形式の結果セットです。

-- ','で分割する
select *
from string_split(N'abc,def,ghi', N',');

-- 結果
/*
value
-----------
abc
def
ghi
*/

1つ目の引数がnullの場合、戻り値は空の結果セット（レコードが0件）になります。分割できない場合は、引数の値がそのまま返ってきます。

-- 引数がnullの場合は空の結果セット
select *
from string_split(null, N',');
/*
value
-----
*/

-- 引数が分割できない場合は引数の値
select *
from string_split(N'abc', N',');
/*
value
-----
abc
*/

また2つ目の引数に文字を複数指定するとエラーになります。

-- エラーになる
select *
from string_split(N'abc_def-ghi', N'_-');

/*
プロシージャでは型 'nchar(1)/nvarchar(1)' のパラメーター 'separator' を想定しています。
*/

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

sql-server t-sql

前の記事では、拡張イベントを使ってスロークエリを取得しつつ次の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;

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

参考

SQL Server - 拡張イベントを使ってスロークエリを取得するクエリ

sql-server t-sql

SQL Serverの拡張イベントを手っ取り早く試すにはこの記事が良いかなと思います。

この記事もわかりやすくSSMSのGUI自体もわかりやすいので、簡単に拡張イベントを使えるかなと思います。簡単に使えてしまうのですが、せっかくなのでクエリをそこそこ理解したいところ。今回はGUIを使わずにクエリだけで次のことを試します。

  • 拡張イベントのセッションを作成する
  • イベントをキャプチャする
  • キャプチャしたイベントを確認する

例としてはありがちですが、いわゆるスロークエリを取得してみます。順番に見ていきましょう。

拡張イベントのセッションを作成する

イベントをキャプチャするためにまずはセッションを作成します。

-- セッションを作成
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
    )
    -- イベントデータはring_buffer（メモリ）に出力する
    add target package0.ring_buffer;
  • add event句でキャプチャしたい「イベント」を指定します。このクエリではsql_batch_completedイベント・rpc_completedイベントの2つをキャプチャするようにしています。
  • where句は「述語」や「述語フィルタ」と呼ばれる部分です。クエリが完了するまでに3秒以上かかったイベントだけにフィルタをしています。
  • add target句でイベントデータの出力先である「ターゲット」を指定します。このクエリではリングバッファ（メモリ）を指定しています。

またイベントや述語、ターゲットなどは「パッケージ」に含まれています。このクエリのsqlserverやpackage0がパッケージ名です。パッケージは名前空間やコンテナのようなイメージでいいのかなと思います。

参考

イベントをキャプチャする

セッションを開始します。キャプチャを開始するというか、監視を開始するというか。

-- セッション（キャプチャ）を開始
alter event session test_xes
    on server
    state = start;

-- ちなみに停止するには
/*
alter event session test_xes
   on server
   state = stop;
*/

セッションが開始されたので、イベントを発生させてキャプチャしたいと思います。そのために次のプログラムを実行します。sql_batch_completedイベントとrpc_completedイベントをキャプチャできるように、3秒以上かかるクエリを2つ実行するプログラムです。

class Program {
    private static readonly string _connectionString
        = new SqlConnectionStringBuilder {
            DataSource = ".",
            IntegratedSecurity = true,
        }.ToString();

    static void Main(string[] args) {
        using (var connection = new SqlConnection(_connectionString)) {
            // 3秒以上かかってsql_batch_completedイベントを発生
            var result1 = connection.Query<int>(
                "waitfor delay '00:00:03'; select 100;").First();
            Console.WriteLine(result1); // 結果に意味はない

            // 3秒以上かかってrpc_completedイベントを発生
            var result2 = connection.Query<int>(
                "waitfor delay '00:00:03'; select @p;",
                new { p = 99 }).First();
            Console.WriteLine(result2); // 結果に意味はない
        }
    }
}

これで2つのイベントがリングバッファに出力されているはずです。

キャプチャしたイベントを確認する

キャプチャしたイベントのイベントデータを確認します。

出力されたイベントデータは、SSMSだとオブジェクトエクスプローラから[管理]-[拡張イベント]-[セッション]-[対象のセッション]の対象ターゲットを選択して[ターゲットデータを表示]すると確認できます。今回の記事ではターゲットはpackage0.ring_bufferです。

ターゲットデータを表示すると次のようなxmlを確認できます。

<!-- イベントデータ：一部要素や属性を省略 -->
<RingBufferTarget>
  <event name="sql_batch_completed" package="sqlserver" timestamp="2017-08-11T03:54:22.422Z">
    <data name="duration">
      <type name="uint64" package="package0"></type>
      <value>3002521</value>
    </data>
    <data name="batch_text">
      <type name="unicode_string" package="package0"></type>
      <value><![CDATA[waitfor delay '00:00:03'; select 100;]]></value>
    </data>
  </event>
  <event name="rpc_completed" package="sqlserver" timestamp="2017-08-11T03:54:25.445Z">
    <data name="duration">
      <type name="uint64" package="package0"></type>
      <value>3001218</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0"></type>
      <value><![CDATA[exec sp_executesql N'waitfor delay ''00:00:03''; select @p;',N'@p int',@p=99]]></value>
    </data>
  </event>
</RingBufferTarget>

これをクエリで確認するには、次のようなクエリを実行します。

select
    cast(xe_st.target_data as xml)  -- データ（xml）
from sys.dm_xe_sessions as xe_s
    inner join sys.dm_xe_session_targets as xe_st
        on xe_s.address = xe_st.event_session_address
where xe_s.name = N'test_xes';

sys.dm_xe_sessionsを使ってアクティブな（=開始されている）セッションを特定できます。セッションが停止中は取得できません。sys.dm_xe_session_targetsを使ってセッションのターゲットを取得でき、target_dataカラムからイベントデータを取得できます。

ここまでくればあとはxmlデータ型のnodesメソッドやvalueメソッドを使って必要な情報を抜き出すだけです。上記クエリと組み合わせて、xmlデータから必要な情報を抜き出すクエリはこんな感じです。

with xe_e(data)
as(
    select
        cast(xe_st.target_data as xml)  -- データ（xml）
    from sys.dm_xe_sessions as xe_s
        inner join sys.dm_xe_session_targets as xe_st
            on xe_s.address = xe_st.event_session_address
    where xe_s.name = N'test_xes'
)
select
    node.value('@package', 'nvarchar(10)') as package,
    node.value('@name', 'nvarchar(20)') as event,
    node.value('@timestamp', 'datetime2') as timestamp,
    node.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    node.value('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') as batch_text,
    node.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement
from xe_e
    cross apply [data].nodes('/RingBufferTarget/event') as event(node);

-- 結果
/*
package    event                timestamp                   duration             batch_text                               statement
---------- -------------------- --------------------------- -------------------- ---------------------------------------- --------------------------------------------------------------------------------
sqlserver  sql_batch_completed  2017-08-11 03:54:22.4220000 3002521              waitfor delay '00:00:03'; select 100;    NULL
sqlserver  rpc_completed        2017-08-11 03:54:25.4450000 3001218              NULL                                     exec sp_executesql N'waitfor delay ''00:00:03''; select @p;',N'@p int',@p=99
*/

必要な情報を取得することができました。ただXQueryがよく分からないので手探りです。

参考

T-SQLでn個ずつのグループにわける

t-sql

前回はn個のグループにわけるクエリを書いてみました。

ichiroku11.hatenablog.jp

今回はn個ずつになるようにグルーピングするクエリをROW_NUMBER関数を使って書いてみたいと思います。

ROW_NUMBER (Transact-SQL) | Microsoft Docs

前回と同じように11から20までの数値があるとします。

-- 11から20までの数値があるとして
select *
from #Sequence;

/*
Value
-----------
11
12
13
14
15
16
17
18
19
20
*/

この10個の数値を4個ずつのグループ（この場合は4個・4個・2個のグループ）にわかれるようにグループ番号を割り振ります。グループ番号はNTILE関数と同じように1からとしてみます。

-- 4個ずつのグループにわける
declare @count int = 4;

select *,
    -- グループ番号は1からにする
    (row_number() over (order By Value) - 1) / @count + 1 as GroupNo
    -- グループ番号が0からでよければ
    -- (row_number() over (order By Value) - 1) / @count as GroupNo
from #Sequence;

/*
Value       GroupNo
----------- --------------------
11          1
12          1
13          1
14          1
15          2
16          2
17          2
18          2
19          3
20          3
*/

4個ずつのグループにわけることができました。