SQL Server Profiler から SSMS XEvent Profiler への移行 第 22 回 SQL Server 2019 勉強会 @日本マイクロソフト品川本社 2019/09/21 ymasaoka

Agenda 2 1. SQL Trace と 拡張イベント 2. SQL Trace の今後 3. SSMS XEvent Profiler 概要 4. SQL Trace から拡張イベントへの設定移行

Agenda 3 1. SQL Trace と 拡張イベント 2. SQL Trace の今後 3. SSMS XEvent Profiler 概要 4. SQL Trace から拡張イベントへの設定移行

SQL Trace 概要 実行された SQL 文の実行履歴情報を SQL トレースファイルなどに出力する機能 4 • 別名、Server Trace / Server-side Trace • 初登場は SQL Server 2000 • 設定したイベントとカラムに紐づく、SQL の実行履歴情報を取得 • 専用の Transact-SQL システムストアドプロシージャを実行して利用 • 取得したデータはSQL トレースファイル(.trc)に出力 # /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'databaseadmin@1’ -- 既存のトレースファイル情報すべてを表示 1> SELECT traceid, property, value FROM ::fn_trace_getinfo(0) 2> GO -- Transact-SQL を使用したSQL トレースファイルの作成 1> DECLARE @RC int, @TraceID int 2> EXEC @RC = sp_trace_create @TraceID OUTPUT 3> , 2 -- @options int 4> , N’/var/opt/mssql/log/mysqltrace’ -- @tracefile nvarchar(256) 5> -- @maxfilesize, @stoptime, @filecountは今回は省略 6> SELECT RC = @RC, TraceID = @TraceID 7> GO RC TraceID ----------- ----------- 0 2

SQL Trace の設定 取得対象のイベント、取得するデータ列(カラム)を設定し、設定を有効化 必要に応じて、フィルター設定も実施 5 -- 取得するイベントおよびカラムの設定 1> EXEC sp_trace_setevent 2> 2 -- @TraceID int 対象のトレースID 3> , 10 -- @eventid int どのイベントを取得対象するか 4> , 18 -- @column_id int イベントを取得した際にどの情報を記録するか 5> , 1 -- @on int 設定の有効化/無効化 6> GO -- 取得するSQL Traceデータのフィルター設定 1> EXEC sp_trace_setfilter 2> 2 -- @TraceID int 対象のトレースID 3> , 10 -- @column_id int フィルター対象とするカラムID 4> , 0 -- @logical_operatpr int 論理演算子(0: AND、1: OR) 5> , 0 -- @comparison_operator int 比較演算子(0: =、1: <>、6: LIKE etc.) 6> , N’ Microsoft SQL Server Management Studio - クエリ’ -- @value nvarchar/varbinary8000 値 7> GO -- トレースの開始 1> EXEC sp_trace_setstatus 2> 2 -- @TraceID int 対象のトレースID 3> , 1 -- @status int トレースに対する操作(0: 停止、1: 開始、2: トレース停止、削除) 4> GO

SQL Server Profiler SQL Trace を作成および管理し、出力結果を分析・再生するためのインタフェース 6 • Profiler GUI とも呼ばれ、GUI で SQL Trace 関連の操作が可能 • SQL Server 2014以前/2016以降にて、インストール方法が変更 ₋ SQL Server 2016 以降：SSMSインストールに同梱 ₋ SQL Server 2014 以前：SQL Server のインストールで管理ツールを選択 ※SSMS と同時にインストールという点は不変 • SQL Trace を実行しながら、ウインドウに出力結果を表示 • 取得データのファイル出力なども可能 別名：パフォーマンスお化け リアルタイムモニタや、取得したデータ のテーブル保存などを Profiler で実行 →パフォーマンス問題の原因に直結 多数のクライアント接続やリクエストを １つの Profiler で受け取ってしまうので、 できれば使わないほうがいい

SQL Server Profiler on Azure Data Studio Azure Data Studio に拡張機能として、SQL Server Profiler が導入可能 ※2019/09/21現在、Preview版 7 Azure Data Studio の拡張機能一覧から 検索してインストール 既存のSQL Server Profiler と同じ感覚で利用できる

拡張イベント (xEvent) 概要 SQL Trace より軽量なパフォーマンス監視システム 8 • 初登場は SQL Server 2008 (実運用的なものは SQL Server 2012 から登場) • SQL Trace の後継機能として、Microsoft は SQL Trace からの移行を推奨 • 設定したイベントとアクションに紐づく、SQL の実行履歴情報を取得 • Transact-SQL スクリプトの実行 or SSMS UI を使用して利用 • 取得したデータはイベントファイル(.xel)に出力 • SQL Trace では取得できなかった SQL Server 内部操作も確認可能 • Azure SQL Database でも利用可能 で Azure Storage BLOB にデータを出力 ※ SQL Database で使用できる拡張イベ ントの機能は、SQL Server のものと ところどころ差異があるため注意 https://docs.microsoft.com/ja-jp/azure/sql- database/sql-database-xevent-db-diff-from- svr

拡張イベント (xEvent) の用語 拡張イベントで使用される用語とその意味 ※説明は MS 解説に私的解釈を追加したもの 9 項目 説明 イベントセッション 1つ以上のイベントを中心にしたコンストラクトとアクショ ンなどの補助項目をまとめたもの。 拡張イベント エンジンをホストしている SQL Server プロ セス内で作成される。 イベント 監視対象となる地点。拡張イベントでは、監視対象の地点ま で到達したという事実のほか、イベントが生成された時点の 状態情報を伴って発生する。 SQL Trace でのイベントに相当。 アクション イベントに呼応して実行する特定(または一連)の応答。 取得したデータをターゲットに送信する。 SQL Trace でのカラムに相当。 ターゲット キャプチャしたイベントから送られてくる出力データを受信 する項目。 受信したデータの操作について定義を行う。

拡張イベント (xEvent) の用語 拡張イベントで使用される用語とその意味 ※説明は MS 解説に私的解釈を追加したもの 10 項目 説明 パッケージ SQL Server 拡張イベント オブジェクトのコンテナ。 大きく分けて3つのパッケージが存在する。 拡張イベントで定義できるイベントやアクションなどを含め た定義情報をまとめているもの。 述語フィルター アクションで取得したデータのフィルタリングに使用。 設定した条件に合うデータのみターゲットに送信する。

拡張イベント (xEvent) の設定 取得対象とするイベント、取得するアクションを設定 ターゲットを設定し、取得結果をイベントファイル(.xel)として出力 11 # /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'databaseadmin@1’ -- 拡張イベント作成 1> CREATE EVENT SESSION [xevent_name] 2> ON SERVER -- Azure SQL Database の場合は ON DATABASE -- 取得対象のイベントとフィールドを設定 3> ADD EVENT sqlserver.rpc_completed 4> ( 5> ACTION 6> ( 7> sqlserver.client_hostname, 8> sqlserver.database_name, 9> sqlserver.sql_text 10> ) 11> ), 12> ADD EVENT sqlserver.sql_batch_completed … (以下、ADD EVENT句省略) – イベントを複数指定可能

拡張イベント (xEvent) の設定 取得対象とするイベント、取得するアクションを設定 ターゲットを設定し、取得結果をイベントファイル(.xel)として出力 12 -- 前項からの続き -- ターゲット: イベントファイルを設定 13> ADD TARGET package0.event_file -- filenameは必須指定、そのほかは任意設定 14> ( 15> SET filename = N’/var/opt/mssql/log/myxevent.xel’, -- ファイル出力先(拡張子は任意指定) 16> max_file_size = -- ファイルの最大サイズ(MB) ※デフォルトは1GB、16bit整数指定 17> 0000000000000000000000000000000000000000000000000000000001100100, 18> max_rollover_files = 50 -- ファイルシステム内に保持するファイルの最大数 ※デフォルトは5 19> ), 20> ADD TARGET package0.event_counter … (以下、ADD TARGET句省略) -- ターゲットを複数指定可能 -- イベントセッションで使用するオプションを指定 21> WITH 22> ( 23> MAX_MEMORY = 512MB, -- バッファーリング用にセッションに割り当てる最大メモリ容量 ※デフォルトは4MB 24> STARTUP_STATE = ON -- SQL Server 起動時に自動的に開始するかどうか 25> ) 26> GO

拡張イベント (xEvent) の設定 取得対象とするイベント、取得するアクションを設定 ターゲットを設定し、取得結果をイベントファイル(.xel)として出力 13 -- 前項からの続き -- 拡張イベントの有効化 1> ALTER EVENT SESSION [xevent_name] 2> ON SERVER 3> STATE = START 4> GO -- 作成した拡張イベントセッションの状態を確認 ※拡張イベントセッションが開始の場合のみ結果が返る 1> SELECT * FROM sys.dm_xe_sessions 2> WHERE name = [xevent_name] 3> GO

Agenda 14 1. SQL Trace と 拡張イベント 2. SQL Trace の今後 3. SSMS XEvent Profiler 概要 4. SQL Trace から拡張イベントへの設定移行

SQL Trace は非推奨機能 SQL Trace および SQL Server Profiler は既にメンテナンスモードの機能 Microsoft は拡張イベント および SSMS XEvent Profiler への移行を案内 15 URL: https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-trace/sql-trace?view=sql-server-2017 SQL Trace および SQL Server Profiler の機能は、次期バージョン以降で 機能削除される予定 ※いつかは未定 ※ただし、SSAS (SQL Server Analysis Service) のワークロード用の SQL Server Profiler は 引き続きサポート

SQL Trace が担っていた役割は分割 パフォーマンス監視とデータベース監査の２つの役割は、それぞれ拡張イベントと SQL Server Audit に分割 16 パフォーマンス監視 • ベースライン情報の取得 • データベース性能の評価 • 実行速度の遅いクエリの検出、原因診断 • ブロッキング発生の検出 など データベース監査 • 特権ユーザーの操作 • データベースユーザーの権限管理 • SQL インジェクションなどの不正行為 • PCI DSS や J-SOX のための対策 など 拡張イベント (xEvent) サーバー監査 / データベース監査 (SQL Server Audit)

SQL Database は SQL Trace 利用不可 Azure SQL Database では、SQL Trace は既に利用不可でサポートされない 17 • Azure SQL Database では、ALTER TRACE などの権限付与が不可 • 拡張イベント、または 専用の監査機能(SQL Server Audit)の利用が確定 sysadmin ロールの所有などが求められるが、 SQL Database ではこれらは設定できない SQL Database の監査については本資料で 触れないが、詳細は Microsoft Docs で確認 可能 https://docs.microsoft.com/ja-jp/azure/sql- database/sql-database-auditing https://github.com/MicrosoftDocs/azure-docs.ja- jp/blob/master/articles/sql-database/sql-database- auditing.md

Agenda 18 1. SQL Trace と 拡張イベント 2. SQL Trace の今後 3. SSMS XEvent Profiler 概要 4. SQL Trace から拡張イベントへの設定移行

SSMS xEvent Profiler 概要 設定した拡張イベントが取得した内容を表示するライブビューワーウィンドウ SSMSの1機能として提供 (SSMS v17.3以降) 19 発生したイベント、 および発生日時など の情報を時系列表示 選択したイベントに よって取得された データの詳細を表示

XEvent Profiler はライブビューワーウインドウ XEvent Profiler 経由で XEvent を作成するわけではない 20 XEvent Profilerで可能な操作は、拡張イベントで 取得したデータのフィルターやグルーピング、保存 拡張イベント自体は、事前に個別で用意あるいは既存のテンプレートを使用した事前作成が必要 クエリ実行だけではなく、PowerShell でも拡張イベントを管理・作 成することが可能

SSMS XEvent Profiler を使ってみる オブジェクトエクスプローラーから XEvent プロファイラーを選択 Standard もしくは TSQL テンプレートを選択してセッションを起動 21 実行される動作 ① 選択した Standard もしくは TSQL に紐づく、 専用の拡張イベントセッションをSSMSが自動で 生成 (QuickSessionStandard / QuickSessionTSQL) ② 自動作成された拡張イベントセッションを開始 ③ XEvent Profiler を起動してライブビューイング 開始 Standard このテンプレートは、Profiler の 'Standard' テンプレートに相当し ます。トレースを作成するために通常用いる開始点となります。実 行されるすべてのストアド プロシージャと Transact-SQL バッチ をキャプチャします。データベース サーバーの全般的な利用状況を 監視するために使用します。 TSQL このテンプレートは、Profiler の 'TSQL' テンプレートに相当しま す。クライアントによって SQL Server に送信されたすべての Transact-SQL ステートメントと、発行時間をキャプチャします。 クライアント アプリケーションのデバッグに使用します。

Agenda 22 1. SQL Trace と 拡張イベント 2. SQL Trace の今後 3. SSMS XEvent Profiler 概要 4. SQL Trace から拡張イベントへの設定移行

SQL Trace から拡張イベントの設定移行 既に SQL Trace が存在、もしくはトレーススクリプトがある場合は、専用 SQL を 実行して対応するイベントとアクションを確認可能 23 -- 専用 SQL の実行 1> USE MASTER 2> GO 1> DECLARE @trace_id int 2> SET @trace_id = 2 -- ここでは対象 SQL Trace のトレースIDが 2 と想定 3> SELECT DISTINCT el.eventid, em.package_name, em.xe_event_name AS ‘event’ 4> , el.columned, ec.xe_action_name AS ‘action’ 5> FROM (sys.fn_trace_geteventinfo(@trace_id) AS el 6> LEFT OUTER JOIN sys.trace_xe_event_map AS em 7> ON el.eventid = em.trace_event_id) 8> LEFT OUTER JOIN sys.trace_xe_action_map AS ec 9> ON el.columnid = ec.trace_column_id 10> WHERE em.xe_event_name IS NOT NULL AND ec.xe_action_name IS NOT NULL 11> GO

SQL Trace から拡張イベントの設定移行 確認したイベントおよびアクションを使用して拡張イベントを作成 24 SQL 実行結果から取得したイベント、アクション情報を使用して CREATE EVENT SESSION 文の ADD EVENT 句を作成 ALTER EVENT SESSION 文で拡張イベントを開始

その他 SQL Trace から拡張イベントへの設定移行に関する詳細は、Microsoft Docs を確認 25 https://docs.microsoft.com/ja-jp/sql/relational-databases/extended-events/convert-an-existing-sql-trace-script-to-an- extended-events-session?view=sql-server-2017

参考情報 本資料のトピックに関連した、関連情報のURL など 26 【SQL Trace】 • SQL Server プロファイラー • sp_trace_create (Transact-SQL) • DO’s&DONT’s #1: やらない方がいいこと – 運用環境で、Profiler GUI を使用してトレースする • SQL Server監査機能：SQL トレース • SQL Profiler to Azure SQL Database 【拡張イベント】 • 拡張イベントの概要 • SQL Database の拡張イベント • CREATE EVENT SESSION (Transact-SQL) • Event File Target • SQL Server の拡張イベントに対するシステム ビューからの SELECT と JOIN • SQL トレースのイベント クラスと等価な拡張イベントを確認する • SQL Server 拡張イベント パッケージ • オブジェクト エクスプローラーでのイベント セッションの管理 • トレースツールは SQL Server Profiler から Extended Events （拡張イベント）へ

参考情報 本資料のトピックに関連した、関連情報のURL など 27 【SSMS Xevent Profiler】 • SSMS XEvent Profiler の使用 • Use XEvent Profiler to capture queries in SQL Server 【Azure SQL Database】 • Professional Azure SQL Database Administration: Equip yourself with the skills to manage and maintain data in the cloud, 2nd Edition (English Edition) • Connect SQL Server Profiler 2017 to Azure SQL Server