SQL Server は製品のバージョンアップや更新プログラムによってクエリ情報を取得するための「クエリプロファイリング」の機能が向上しています。
クエリ プロファイリング インフラストラクチャ というドキュメントで、詳細はまとめられているのですが、自分の言葉で書く機会が少なかったのと、SQL Server 2019 CTP 2.4 でも取得できる情報が増えていますので、この機会にまとめておこうと思います。
クエリプロファイリングは、
- 標準クエリ実行統計プロファイリング
- 軽量クエリ実行統計プロファイリング
の 2 種類に分けられ、「軽量クエリ実行統計プロファイリング」がバージョンが上がるたびに機能の改善が行われています。
(SQL Server 2019 CTP 2.4 時点では軽量クエリ実行統計プロファイリング v3 となっています。)
「標準クエリ実行統計プロファイリング」は、SQL Server のバージョンに依存する箇所が少なく、どのバージョンでも汎用的に使用することができます。
しかし、情報の取得によるオーバーヘッドが大きく、「常時取得を行う」のではなく、「問題が発生しているときに調査用の情報として取得を行う」という利用方法が一般的でした。
しかし、このような取得ですと「実際に問題が発生した際中の情報が取得できていない」というケースが多くなります。
そこで「情報取得のオーバーヘッドを抑えた」情報の取得方法として「軽量クエリ実行統計プロファイリング」という機能が、SQL Server 2014 SP2 / SQL Server 2016 以降で追加が行われました。
この取得方法でも、情報取得のオーバーヘッドは発生するのですが、2~3% のオーバーヘッドに抑えられ、常時実行しておくことも視野に入れられています。
SQL Server 2019 になると、「軽量クエリ実行統計プロファイリング」は、デフォルトで有効な状態となっていますので、「何か問題が発生した際に情報が取得できない」ということを、回避することができるようになっています。
それでは、それぞれの情報取得がどのようなものなのかを見ていきましょう。
標準クエリ実行統計プロファイリング
実行するクエリに対して、クエリの実際の実行プランの取得を行うための方法となります。
「標準」とついているように、SQL Server のバージョンに依存する箇所が少なく、汎用的に利用することができます。
一般的な内容としては、次の SET 句で設定を有効にしてからクエリを実行することで、実際のクエリ情報を取得することができます。
「SET STATISTICS XML ON」は、クエリ実行時にクエリ実行時に実際の実行プランを表示するものです。
SSMS の「実際の実行プランを含める」を介して、利用されている方も多いのではないでしょうか。
「推定実行プラン」は「統計情報」をベースにした実行プランの生成となるため、「クエリを実際に実行してデータの取得が行われた場合」の「実際の行数」は取得することができません。
(統計情報と実情報の乖離がある場合、処理想定のデータ件数が大幅に乖離する可能性があるといえます)
「実際の実行プラン」については、「クエリを実際に実行した結果」から表示が行われますので、実行プラン内で流れてきたデータについても正確なものとなります。
「SET STATISITCS XML」では、次のような、グラフィカルな「実際の実行プラン」を取得することができます。
クエリによっては、XML 形式ではなく、行列のレコードして取得した方が解析が容易なケースがあるかもしれません。
そのような場合は「SET STATISTICS PROFILE ON」を実行してからクエリの実行を行います。
上記の SET 句は「クエリを実行するセッション単位」で有効化する必要があります。
上記の SET 句を有効にしていないセッションについても、実際の実行プランの取得を行いたい場合があると思います。
そのような場合は、拡張イベント / SQL Server Profiler で次のイベントの取得を行い、トレースログとして取得を行います。
- 拡張イベント : query_post_execution_showplan
- SQL Server Profiler : Showplan XML イベント クラス
これにより、作業者のセッション以外のクエリの情報についても、トレースログから取得を行うことが可能となります。
(昨今、イベントが追加されるのは拡張イベントの方に集中していますので、可能であれば拡張イベントを使えるようにしておいた方がよいかと思います)
これにより、他のセッションで実行されたクエリについても「実際の実行プラン」がクエリ完了時に取得できるようになります。
SQL Server 2014 以降については、拡張イベントで「query_post_execution_showplan」を取得することができるようになりました。
利用状況モニターの「ライブ実行プランの表示」や、DMV : sys.dm_exec_query_profiles から、クエリの進行状況を確認することができるようになります。
「ライブ実行プラン」(ライブ実行統計)では、「実行中のクエリの進行状況」を取得することが可能となります。
処理が完了しないクエリについて次のような情報を確認することができますので、クエリの進行状況を確認するための強力な機能となります。
- 今、どの処理を実行しているのか
- 何行アクセスされたのか
ただし、「query_post_execution_showplan」を取得することにより、サーバーの負荷が増加するため、本方法による情報の取得は「常時実行」ではなく、「調査が必要な場合にのみ有効化して利用する」ようなものとなっていました。
軽量クエリ実行統計プロファイリング v1
標準クエリ実行統計プロファイリングでは、情報の取得によるサーバーの負荷増加が問題としてありました。
SQL Server 2014 SP2 / SQL Server 2016 以降では、この問題への対応として、「軽量クエリ実行統計プロファイリング」が新しく追加されました。
これらのバージョンでは拡張イベント「query_thread_profile」の追加が行われました。
このイベントは「Debug」チャネルとなっており、デフォルトでは表示されませんので取得する場合は「チャネル」から「Debug」を有効にしてください。
この拡張イベントを使用することで、「query_post_execution_showplan」を有効にした場合と比較して、パフォーマンスへのオーバーヘッドの発生を抑えながら、実行中のクエリ情報を取得することが可能となりました。
この拡張イベントが有効な状態で実行されたクエリについては、「ライブ実行プランの表示」「sys.dm_exec_query_profiles」から、実行中のクエリの進行状況を取得することができます。
「query_thread_profile」を有効にした状態で「sys.dm_exec_query_profiles」の情報を取得した場合、「query_post_execution_showplan」を有効にしていた場合に取得していた情報の一部は取得されなくなります。
上段が「query_post_execution_showplan」、下段が「query_thread_profile」の情報となります。
「query_thread_profile」については一部の情報が 0 のままであり取得されていないことが確認できます。
このように、「query_thread_profile」は性能へのオーバーヘッドは低いですが、取得される情報が一部の情報に限定されます。
しかし、クエリの進行状況を確認するために必要となる基本的な情報については取得が行われていますので、十分に活用することが可能です。
軽量クエリ実行統計プロファイリング v2
SQL Server 2016 SP1 / SQL Server 2017 以降では、軽量クエリ実行統計プロファイリングの機能が向上しています。
「query_post_execution_showplan」「query_thread_profile」を有効にした場合のパフォーマンスのオーバーヘッドの発生を、v1 よりさらに抑えることができるようになりました。
トレースフラグ 7412 により、トレースフラグの設定によって、拡張イベント「query_thread_profile」を有効にした場合と同様の情報取得を可能にすることができるようになりました。
DMV「sys.dm_exec_query_statistics_xml」の追加も行われました。
この DMV を使用することで、クエリの進行状況を実行プランの XML の形式で取得することができます。
SQL Server 2016 SP2 CU3 / SQL Server 2017 CU11 以降では、クエリヒント「QUERY_PLAN_PROFILE」と拡張イベント「query_plan_profile」が追加されました。
これは、トレースフラグ 7412 / 拡張イベント query_thread_profile に近い動作となりますが、クエリの進行状況の取得対象が、さらに限定されたものとなります。
トレースフラグ 7412 / 拡張イベント query_thread_profile は、インスタンスとしての設定となるため、全セッションで設定が有効化されます。
拡張イベント「query_plan_profile」については、クエリ内に「OPTION(USE HINT(‘QUERY_PLAN_PROFILE’))」が指定されているクエリでのみ、ライブクエリ統計のクエリの進行状況を取得することが可能となります。
特定のクエリのみクエリの進行状況取得する動作となるため、情報取得のオーバーヘッドをさらに限定的なものとすることができます。
軽量クエリ実行統計プロファイリング v3
SQL Server 2019 以降では、軽量クエリ実行統計プロファイリングの機能がさらに向上しています。
インスタンスでデフォルトで軽量クエリ実行統計プロファイリングが有効な状態となっています。
トレースフラグ 7412 は SQL Server 2019 では動作しなくなり、機能の有効 / 無効化は、データベース単位で設定を行うことができ、データベーススコープの構成 の「LIGHTWEIGHT_QUERY_PROFILING」で制御を行います。
軽量クエリ実行統計プロファイリングを活用した、新しい拡張イベントと DMF の追加も行われています。
拡張イベント「query_post_execution_plan_profile」が追加されました。
この拡張イベントにより、「実際の実行プラン」の情報を「query_post_execution_showplan」より、負荷を抑えて取得することができるようになりました。
クエリの完了が終わった際にイベントが出力され、実際に操作された行数が表示されている実行プランが取得されています。
DMF としては、「sys.dm_exec_query_plan_stats」が追加されました。
この DMF はトレースフラグ 2451 を有効にすることで使用することができます。
DMF にプランハンドルを渡すことで、「実際の実行プラン」の情報を取得することができます。
プランハンドルが取得できる DMV と組み合わせることで、「最後に実行された実際の実行プラン」の情報を取得することができます。
今までのバージョンでは、plan_handle の情報から、クエリの実行プランを取得しようとした場合、「sys.dm_exec_query_plan」から取得を行うことが一般的でした。
この情報は、「実際の行数」ではなく、統計情報をベースにした推定件数の情報での取得となっていました。
新しく追加された「sys.dm_exec_qery_plan_stats」は、最後に実行された実際の実行プランの情報を可能な場合は取得するようで、推定だけでなく実際の行数を取得できるようになります。
このように、「実際の実行プラン」では、「推定の行数」ではなく、「実際の行数」を取得することができ、実際に何行のデータが取得されたのかを確認することができます。
また、軽量プロファイリングで実装されている情報取得は、オーバーヘッドを少なくして情報の取得が可能なことを目指しているため、情報取得のための性能の低下を抑えることができるです。
簡単ではありますが、SQL Server 2019 CTP 2.4 までのクエリプロファイリングの手法を見てみました。
SQL Server 2019 はまだ Preview の段階ですので、製品出荷時にはさらに情報の取得方法が追加されるかもしれません。
最近の SQL Server は累積更新プログラムの適用により、情報取得方法の機能が向上することもありますので、新しい更新プログラムがリリースされた際には、機能向上についても目を通してみるとよいのではないでしょうか。