Oracle 経験者のための SQL Server HowTo #1: 拡張イベントを使って On-Error Dump を取得する

早川 邦彦 
SQL Engine Support Engineer

技術者としていくつかのデータベース製品を扱う機会があるかと思いますが、データベース製品には構文や機能の違いがあるので、一つのデータベース製品でできることが別の製品では実装方法が分からないケースが多々あると思います。そこで、 Oracle データベースでできることをいくつか例として挙げて、 SQL Server ではどのようにこれらを実装できるかを何回かのシリーズで紹介しようと思います。

まず一回目の今回は、SQL Server の拡張イベント機能を使って Oracle でいう On-Error Dump を取得する方法について紹介します。

1. On-Error Dump について

Oracle データベースでは以下のようなコマンドを実行することで特定のエラーが発生した際にエラー発生時の情報をトレースファイルに出力させることができます。 下記のコマンドは ORA-942 エラーが発生した際に errorstack というタイプのダンプファイルを詳細レベル 3 で出力するというコマンド例です。

    alter system set events ‘942 trace name errorstack level 3’

SQL Server 2008 以降では拡張イベントを使えば上記の Oracle データベースのコマンドのように特定のエラーが発生したタイミングで情報をトレースファイルに出力させることができます。なお、 Oracle のコマンドとは違い、SQL Server の拡張イベントを使用すればいつ、何を、どこに出力するかをより詳細に制御することが可能です。 以下は拡張イベントを使って SQL Server エラー 208 が発生した際に情報をトレースファイルに出力するための設定例です。

    create event session on_error on server
add event sqlserver.error_reported
(
action
(
package0.callstack,
sqlserver.session_id,
sqlserver.create_dump_all_threads,
sqlserver.sql_text
)
where error = 208
)
add target package0.asynchronous_file_target
(SET filename='C:\Temp\on_error.txt', metadatafile='C:\Temp\on_error_meta.txt')
go

    alter event session on_error on server state = start
go

上記拡張イベントの設定例(以後、設定例)をベースに SQL Server での On-Error Dump の取得方法を説明します。

2. 拡張イベント セッションの作成

2.1. 拡張イベント セッションの名前を設定する

拡張イベントを使って情報を取得するためには create event session コマンドで拡張イベント セッションを作成する必要があります。 拡張イベント セッションの定義によりいつ、何が、どこに出力されるかが決定します。しかし、これらの詳細を設定する前に設定例の一行目にあるように拡張イベント セッションの名前を設定する必要があります。 設定例では拡張イベント セッションは on_error という名前に設定されています。

    create event session on_error on server   

2.2. 情報が出力されるタイミングを指定する

名前が決まりましたら次はいつ情報を出力するかを決める必要があります。 何をトリガーに情報を出力するかは create event session コマンドの add event 句で決まります。設定例では sqlserver.error_reported イベントをトリガーに情報を出力する設定になっており、sqlserver.error_reported はエラーが報告された際に発生するイベントとなります。

    add event sqlserver.error_reported

しかし、エラーが発生するたびに情報が出力されると情報が大量に出力される可能性があるので設定例では add event 句の where 句で述語を追加して情報が出力されるタイミングをより限定しています。設定例では発生したエラーの番号が 208 である場合にのみ情報を出力する設定になっています。

    where error = 208    

add event 句で指定できるイベントは以下のクエリを実行することで確認することができます。On-Error Dump の場合は sqlserver.error_reported を指定するケースがほとんどです。

    select p.name, xo.name, xo.description, xo.object_type
from sys.dm_xe_objects xo join sys.dm_xe_packages p on xo.package_guid = p.guid
where xo.object_type = 'event'
order by p.name, xo.name

なお、add event 句で指定するイベントに対して利用可能な述語は以下のクエリで確認できます。設定例で使用している sqlserver.error_reported イベントで利用できる述語を確認するには以下のクエリにある <イベント名> に error_reported を指定します。

    select *
from sys.dm_xe_object_columns
where object_name = ‘<イベント名>’
and column_type = 'data'

2.3. 出力する情報を指定する

add event 句で指定したイベントが発生した際に出力される情報項目は add event 句の action 句で指定します。 設定例ではイベントを発生させたスレッドのコールスタック(package0.callstack)、セッションID(sqlserver.session_id)、実行していたSQL文(sqlserver.sql_text)、そしてイベント発生時に存在していたすべてのスレッドのメモリダンプ(sqlserver.create_dump_all_threads) を出力する設定になっています。

    action
(
package0.callstack,
sqlserver.session_id,
sqlserver.create_dump_all_threads,
sqlserver.sql_text
)     

action 句で指定できる action の一覧は以下のクエリで確認できます。

    select p.name as 'package_name', xo.name as 'action_name', xo.description, xo.object_type
from sys.dm_xe_objects as xo
join sys.dm_xe_packages as p
on xo.package_guid = p.guid
where xo.object_type = 'action'
and (xo.capabilities & 1 = 0
or xo.capabilities is null)
order by p.name, xo.name     

2.4. 情報の出力先を指定する

add event 句の action 句で指定した情報の出力先は add target 句で指定します。設定例では出力形式をファイルシステム上の非同期ファイル(package0.asynchronous_file_target) に指定し、トレースファイルと関連するメタデータファイルを C:\Temp\ ディレクトリ配下に出力するように設定しています。 出力形式をファイルシステム上の非同期ファイルに指定する場合は以下のように filename と metadatafile オプションにトレースファイルとメタデータファイルの出力先を設定する必要があります。 以下の設定で add event 句の action 句で指定した情報のほとんどは指定したファイルパスに出力されるようになりますが、sqlserver.create_dump_all_threads アクションで生成されるメモリダンプファイルだけは SQL Server の既定のログ出力先に出力されます。

  add target package0.asynchronous_file_target
(SET filename='C:\Temp\on_error.txt', metadatafile='C:\Temp\on_error_meta.txt')     

なお、add target 句で指定できる target (出力先) の一覧は以下のクエリで確認できます。

    select p.name as 'package_name', xo.name as 'target_name', xo.description, xo.object_type
from sys.dm_xe_objects as xo
join sys.dm_xe_packages as p
on xo.package_guid = p.guid
where xo.object_type = 'target'
and (xo.capabilities & 1 = 0
or xo.capabilities is null )
order by p.name, xo.name

3. 拡張イベント セッションの開始と停止

拡張イベント セッションを作成したら、エラー発生時に情報を取得するにはそのセッションを開始する必要があります。拡張イベント セッションを開始にするには以下のように alter event session コマンドを使って拡張イベント セッションの state オプションを start に設定します。

    alter event session on_error on server state = start

なお、拡張イベント セッションを停止するためには state オプションを以下のように stop に設定します。

    alter event session on_error on server state = stop

現在、開始されている拡張イベント セッションは以下のクエリで確認することができます。

    select * from sys.dm_xe_sessions    

4. 取得したトレースファイルを読む

設定例の拡張イベントセッションを作成し、開始した後に 208 エラーが発生した場合メモリダンプ以外の情報は add target 句で指定したディレクトリパスに出力され、メモリダンプはSQL Server の既定のログ出力先に出力されます。実際に出力されるトレースファイル名は以下のように add target 句で指定したファイル名にタイムスタンプ情報が追加されたものになり、 メモリダンプは SQLDump####.mdmp という形式で出力されます。

 
メモリダンプはデバッガを使って解析しますがトレースファイルの内容は以下のコマンドのように出力されたトレースファイル名とメタデータファイル名を sys.fn_xe_file_target_read_file の第一と第二引数として指定することで確認できます。第三、第四引数は通常nullに指定します。

    select cast(event_data as xml) as event_data
from sys.fn_xe_file_target_read_file('C:\Temp\on_error_0_129683848363190000.txt',
                                                           'C:\Temp\on_error_meta_0_129683848363190000.txt',
                                                           null,
null)

上記コマンドを実行すると以下のように XML 形式の結果へのリンクが出力され、このリンクをクリックするとトレース情報が XML 形式で表示されます。
          

5. 拡張イベントに関する参考ページ

拡張イベントの詳細については以下のページで紹介しています。

    SQL Server 拡張イベントの概要
    https://msdn.microsoft.com/ja-jp/library/bb630354.aspx

    SQL Server 拡張イベントの概念
    https://msdn.microsoft.com/ja-jp/library/bb630285.aspx

    SQL Server 拡張イベントの使用
    https://msdn.microsoft.com/ja-jp/library/bb630317.aspx

    SQL Server 拡張イベントの使用法に関するトピック
    https://msdn.microsoft.com/ja-jp/library/bb630340.aspx

    CREATE EVENT SESSION (Transact-SQL)
    https://msdn.microsoft.com/ja-jp/library/bb677289.aspx

    ALTER EVENT SESSION (Transact-SQL)
    https://msdn.microsoft.com/ja-jp/library/bb630368.aspx

    sys.fn_xe_file_target_read_file (Transact-SQL)
    https://msdn.microsoft.com/ja-jp/library/cc280743.aspx