【３分で流し読み】SQL Server / Azure SQL Databaseの軽量クエリプロファイリングでできることを理解する

こんばんは。今日はSQL Serverの軽量クエリプロファイリングについて少し勉強したので、理解したことを簡単にまとめておきたいと思います。

それではまいります。

軽量クエリプロファイリングとは

概要

クエリ プロファイリング インフラストラクチャ – SQL Server | Microsoft Docs

よりも以下の記事が非常にわかりやすくまとまっていますので、これを読むだけで十分かも笑

SQL Server 2019 CTP 2.4 時代のクエリプロファイリング at SE の雑記 (engineer-memo.com)

以下、上記記事からの要点抜粋。

• パフォーマンス問題の調査には、実行中のクエリプランや実際の実行プランを確認することが有効
• そうした情報を取得する方法として、標準クエリ実行統計プロファイリングと軽量クエリ実行統計プロファイリングの２種類がある
• 標準クエリ実行統計プロファイリングはすべてのSQL Serverバージョンで利用可能
• 軽量クエリ実行統計プロファイリングはSQL Server 2014で登場し（v1）、SQL Server新バージョンが出るたび改良が加えられている（SQL Server 2016でv2、SQL Server 2019でv3）
• v1, v2ではライブクエリ統計を軽量クエリプロファイリング経由で取得できるようになっていたのに加えて、v3では実際の実行プランに相当する情報も軽量クエリプロファイリング経由で取得可能に。（ちょっと理解が怪しい、違ってたらごめんなさい）
• 標準クエリ実行統計プロファイリングは情報取得のオーバーヘッドが大きいので「常時取得」ではなく「問題が発生しているときに一時的に有効化」する使い方が一般的だった
• このオーバーヘッドを減らして「常時取得」も見据えたのが軽量クエリ実行統計プロファイリング。標準クエリプロファイリングでは75%ほどあったオーバーヘッドがなんと2%未満に。
• SQL Server 2019またはAzure SQL Databaseでは現在規定で軽量クエリプロファイリングv3が有効化されている

結局何が嬉しいの？

こちらの記事に分かりやすく解説されていました！

SQL Database で軽量なクエリプロファイリングを使用し、「実行中のクエリの情報」を取得する at SE の雑記 (engineer-memo.com)

以下、軽量クエリプロファイリング機能についてもう少し詳細に追いかけてみます。

標準クエリプロファイリング

その前にまずは標準クエリプロファイリング。以下の３種類の情報の取り方がある。

• SET STATISTICS XML
• SET STATISTICS PROFILE
• ライブクエリ統計

なお、将来のバージョンでは、SET STATISTICS PROFILEは使えなくなるらしい。

SQL Server の今後のバージョンでは、新しいクエリ実行プランの情報は SET STATISTICS XML ステートメントでのみ表示され、SET STATISTICS PROFILE ステートメントでは表示されなくなります。

SET STATISTICS XML (Transact-SQL) – SQL Server | Microsoft Docs

以下の場合も、内部的には標準クエリプロファイリング機能を使った情報取得が行われている。

• 拡張イベント機能で「query_post_execution_showplan」を設定したとき
• SSMSで「実行時の実行統計」取得ボタンを押したとき

それぞれのオプションで取得できる情報を一応確認しておきます。

SET STATISTICS XML

SET STATISTICS XML ON

Select * from Sales.Customer where StoreID = 934

SET STATISTICS PROFILE

SET STATISTICS PROFILE ON

Select * from Sales.Customer where StoreID = 934

軽量クエリプロファイリング

続いて軽量クエリプロファイリング。

v1

クエリ プロファイリング インフラストラクチャ – SQL Server | Microsoft Docs

• SQL Server 2014 SP2およびSQL Server 2016 以降で導入されたバージョン
• 軽量クエリプロファイリングを利用する新しい拡張イベント「query_thread_profile」が導入されてた（従来は標準クエリプロファイリングを取得する「query_post_execution_showplan」拡張イベントが存在した）

qeury_thread_profile、v3で登場したquery_post_execution_plan_profileと何が違うんだっけと混乱しましたが、よくよく読むとthread_profileの方は実際の実行プランではなくライブクエリ統計情報を軽量クエリプロファイリングで取得するための拡張イベントということなのかな？

Developers Choice: Query progress – anytime, anywhere | Microsoft Docs

v2

クエリ プロファイリング インフラストラクチャ – SQL Server | Microsoft Docs

• SQL Server 2016 SP1では、v1の軽量クエリプロファイリングのオーバーヘッドがさらに改善された。トレースフラグ7412で有効化できる。
• 送信中の要求にクエリ実行プランを返すために、新しいDMF（動的管理関数） sys.dm_exec_query_statistics_xml が導入された。
• SQL Server 2016 SP3とSQL Server 2017以降ではさらに機能が拡張され、QUERY_PLAN_PROFILEヒント句を利用することでセッション毎に軽量クエリプロファイリングが有効化できるようになった。
• 併せて新しい拡張イベント「query_plan_profile」も導入され、「query_post_execution_showplan」と同等の情報を取得できるようになった。

KB4458593-Update は、SQL Server 2016 および2017の軽量クエリプロファイリングのヒントを追加します。 (microsoft.com)

にもう少し解説がありましたが、v1で追加された「query_thread_profile」はサーバー全体で実行プラン情報を取得する一方、v2で追加された「query_plan_profile」はセッション毎に情報取得を制御できるためよりパフォーマンス影響を軽減できる、とありました。なるほど。

総じて、ライブクエリ統計をより影響を抑えて取得できるようにする機能追加っぽいですね。

v3

クエリ プロファイリング インフラストラクチャ – SQL Server | Microsoft Docs

• SQL Server 2019以降またはAzure SQL Databaseで利用可能なバージョン
• 上記バージョンでは規定でこのバージョンが有効化されている（ので、v2機能を有効化するためのトレースフラグ7412は無効に）。データベースレベルで無効化可能
• 新しい拡張イベント「query_post_execution_plan_profile」が追加され、標準クエリプロファイリングを利用する「query_post_execution_showplan 」と同等の実際の実行プラン情報が取得できるように。
• 最後の実際の実行プランと同等の情報が取得できる新しいDMF「sys.dm_exec_query_plan_stats」が導入された。これはオプトイン機能なので、トレースフラグ2451で有効化可能。

Docsや関連記事を読んだ自分の理解ですが、v1, v2と異なり、v3ではライブクエリ統計だけでなく実際の実行プラン情報も軽量クエリプロファイリング経由で取得できるようになった点が大きな変化なのかなと感じました。（理解が違ったらごめんなさい）

その他メモ

軽量クエリプロファイリングの有効化状況を確認する方法

SELECT * FROM sys.database_scoped_configurations WHERE name = 'LIGHTWEIGHT_QUERY_PROFILING';

以下はAzure SQL Databaseでの実行例ですが、確かに規定で有効化されています。

以上、軽量クエリプロファイリングについての簡単なまとめでした。また、使い込んでいくうちに、内容もUpdateしていきたいと思います。

少しでも参考になりましたら幸いです。

おしまい