SQL ServerのProfiler周りについて調べてみた。

はじめに

直近SQLServerに関する登壇をする予定ができたので、資料作成のために調べたことを記事にしていきます！

今回はRDSで起動したSQLServerに対して、発行されたSQLなどを確認するSQL Server Profilerの調査をしたので、そのまとめ。

Profilerって？

以下のようなことでできる便利な機能です！

• クエリの実行状況のモニタリング
• パフォーマンス分析
• セキュリティ監査

試してみた！

検証環境

今回はローカル環境をあまり汚したくないので、AWSのRDSのサービスを使って確認しています。

まずはSQL Server Management Studio (SSMS)

現時点で一番簡単にProfilerを使うには、SQL Server Management Studio (SSMS)経由で、SQL Server Profilerを使うのが、楽そうです。

特に設定等の変更は不要で、簡単に利用できました。

ただし、２つほど注意が必要です。

1. SSMSは、Windows専用ソフトウェア

SSMSはMac版の提供がありません。

後述しますが、MacユーザはAzuru Data Studioを使う必要があります。

2. SQL Server Profilerの利用は非推奨

下記サイトにある通り、SQL Serverの将来のバージョンで削除される予定です。

代替手段として、拡張イベントを使用することを推奨しているようです。

拡張イベントってなに？

AIを駆使しつつ、要約すると以下のような機能です。

• パフォーマンス監視やトラブルシューティングのための軽量な監視システム
• パフォーマンスへの影響が少ない
• 従来のSQL Traceと比べてオーバーヘッドが小さいので、システムリソースの消費が抑えられる
• 柔軟な収集機能があり、以下のような様々なイベントを収集可能
  • T-SQLステートメントの実行
  • デッドロックの発生
  • 待機統計
  • メモリ使用状況
  • などなど
• フィルタリング機能

Azuru Data Studio

Macユーザは、Azuru Data Studioを利用する必要があります。

VSCodeに似たUIですが、SSMSに比べ、GUIでできる変更は少ないように見えました。
（ユーザの追加や権限変更がGUIできない？）

拡張ツール「SQL Server Profiler」

Azuru Data Studioで拡張イベントをモニタリングする場合は、拡張ツール「SQL Server Profiler」をインストールする必要があります。

こちらもまだプレビュー版ということで、今後もっと機能が増えていく旨の説明がありました。

「SQL Server Profiler」インストール後はサーバの項目を右クリックしてから、起動することができます。

AWS RDSを使う場合の注意点

上記ページに、RDS上で動作する際の制約が記載されており、この制約の一部が影響し、Azuru Data Studioの標準プロファイルがそのまま利用できないので、少し修正が必要です。

設定画面表示

設定から、Profilerと検索し、下記赤枠のsetting.jsonで編集を選択します。

MEMORY_PARTITION_MODEの変更

Standard_OnPremのcreateStatement内のMEMORY_PARTITION_MODをPER_CPUからNONEへ変更しておきます。

        {
            "name": "Standard_OnPrem",
            "defaultView": "Standard View",
            "engineTypes": [
                "Standalone"
            ],
-            "createStatement": "CREATE EVENT SESSION [{sessionName}] ON SERVER\n\t\t\t\t\tADD EVENT sqlserver.attention(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.login(SET collect_options_text=(1)\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.logout(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.rpc_completed(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.sql_batch_completed(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.sql_batch_starting(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))\n\t\t\t\t\tADD TARGET package0.ring_buffer(SET max_events_limit=(1000))\n\t\t\t\t\tWITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)"
+            "createStatement": "CREATE EVENT SESSION [{sessionName}] ON SERVER\n\t\t\t\t\tADD EVENT sqlserver.attention(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.login(SET collect_options_text=(1)\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.logout(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)),\n\t\t\t\t\tADD EVENT sqlserver.rpc_completed(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.sql_batch_completed(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),\n\t\t\t\t\tADD EVENT sqlserver.sql_batch_starting(\n\t\t\t\t\t\tACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.client_hostname)\n\t\t\t\t\t\tWHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))\n\t\t\t\t\tADD TARGET package0.ring_buffer(SET max_events_limit=(1000))\n\t\t\t\t\tWITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)"
        },

完了

拡張イベントのモニタリングができるようになりました。

動作確認

フィルタリングしてみる。

初期状態だと、自身の操作以外のログが多く出るので、フィルタリングすると見やすくなります。

例えば、自身のユーザでの操作に絞ると、、

自身が投入したSQLが見れたりします。

フィルタリングはその他、端末名を指す、hostnameや、アプリケーション名を指す、ApplicationNameで絞ると特定しやすいかなと思います。

なお、Azuru Data Studioを使っている場合は、ApplocationNameには、azdataが設定されてました。

ビューの変更

ビューをTuning Viewに変更すると、SQLの実行時間が見やすくなります。
（Durationはマイクロ秒単位です。）

まとめ

自身の経験は、オンプレ環境のSQLServerにWindows上のSSMS（SQLProfiler）しかなかったので、知識のおさらいのつもりが色々と新しいことを知ることが出来ました。

もう数記事くらい、SQLServer関連の内容を書いていきたいと思います！