Query Store: データベース版フライト データ レコーダー機能

このポストは、6 月 8 日に投稿された Query Store: A flight data recorder for your database の翻訳です。

Query Store は SQL Server 2016 に導入された、クエリのパフォーマンスのトラブルシューティングに役立つ新しい機能で、今回このプレビュー版が最新バージョンの Azure SQL Database V12 でご利用いただけるようになりました。

データベースのサイズが大きくなると、パフォーマンスが低下することは珍しくありません。クエリ プランを変更した場合、そのすべての変更点が明らかでないと、パフォーマンス低下の原因を特定し、防止につなげることが難しくなります。また、トラブルシューティングには数時間から数日かかることもあり、生産性にも影響します。

Query Store は、飛行機のフライト データ レコーダーのような機能で、すべてのクエリの詳細な履歴情報を収集して表示することができます。問題の診断や解決までの時間が短縮されるため、パフォーマンス調査がスムーズになります。

収集されたデータはいくつかの時間間隔に分割され、時間の経過と共にクエリのパフォーマンスがどのように変化したかを把握することができます。たとえば、クエリが複数のプランを生成した場合には、Query Store を使用してプランの変更点を分析し、パフォーマンス低下の可能性を把握して、クエリ プロセッサがそのクエリに対して特定のプランを使用するように強制できます。

Query Store には Transact-SQL からアクセスできます。収集されたデータの分析と機能の管理を行うには、SQL Server Management Studio (SSMS) または SQL Server Data Tools をインストールする必要があります。

この際、SQL Server 2016 Management Studio Community Technology Preview 2 ( 英語 ) を使用していただくことを強く推奨します。このバージョンには、Query Store 向けに設計された新しいユーザー インターフェイスが採用されているためです。

それでは、Query Store でクエリのパフォーマンス分析を行う手順を説明していきたいと思います。

手順 1: 対象のデータベースで Query Store を有効化する

Query Store はデータベース用の機能であるため、最初に、監視対象のデータベースでこの機能を有効にする必要があります。Query Store が有効になると、収集されたデータがデータベースのストレージ空間に保存されます。SQL Database で有効化する場合は、Azure SQL Server インスタンスに接続して、マスター データベースに接続しているときに下記のスクリプトを実行します。

ALTER DATABASE <database_name> SET QUERY_STORE = ON;

SSMS 2016 CTP2 を実行している場合はその GUI から有効化することもできます。これには、監視対象のデータベースを右クリックし、[Properties] の [Query Store] タブをクリックします。

 

要件に応じて、下記のオプションの既定値を変更することができます。

• [Max Size (MB) ]: Query Store がデータベース内で占有するデータベース空間の制限値 (最大サイズ)。大量のクエリやプランのデータを収集する場合、または履歴を長期間保存する場合は、既定値の 100 MB では足りない可能性があります。ディスク サイズが制限値を超え、Query Store が「読み取り専用」モードに切り替わってしまわないように、使用状況を随時把握し、値を調整してください。
• [Statistics Collection Interval]: 実行時のデータ収集間隔 (既定値は 1 時間)。高い粒度が必要な場合はこの値を小さくすることを検討してください。ただし、この値は Query Store のデータ サイズに直接影響するため、注意が必要です。

その他のオプションの詳細については、Query Store に関する MSDN ドキュメント (英語) を参照してください。

手順 2: ワークロードを実行してデータを分析する

次に、ワークロードにおいてどのクエリが最もリソースを消費しているかを Query Store で特定します。

SSMS 2016 CTP2 を使用している場合は、オブジェクト エクスプローラーでデータベース ノードの Query Store サブフォルダーにある [Top Resource Consuming Queries] をダブルクリックします。新しいウィンドウが開き、システム内で実行されている特に負荷が大きいクエリの詳細情報が表示されます。

 

この画面は 3 つのウィンドウで構成されていて、左のウィンドウにはリソース消費が特に大きいクエリのヒストグラム、右のウィンドウには選択したクエリのプランの概要、下のウィンドウには選択したプランのクエリ プランを視覚化したものが表示されます。[Configure] ボタンをクリックすると、分析するクエリの数やデータ収集の時間間隔を設定できます。他にも、どの種類のリソースの消費状況を表示するか (期間、CPU 使用量、メモリ使用量、IO、実行数) や基準値 (平均値、最小値、最大値、合計値、標準偏差) を選択できます。

クエリのヒントでは、クエリ テキスト、クエリ ID (内部識別番号)、平均期間、実行数、プランのユニーク数に関する詳細を確認できます。右側のプランの概要には分析の実行履歴が表示され、ここから各プランの詳細や実行時の統計を把握することができます。下のウィンドウでは、[Compare] ボタンをクリックすると異なるプランを横に並べて表示できるため、視覚的に形状を把握したり、比較したりすることができます。

Transact-SQL による分析

SSMS 2016 CTP2 より前のバージョンを使用している場合、Transact-SQL スクリプトを使用すると、最もリソース消費が大きいクエリを特定できます。Query Store では、分析時に下記の情報が表示されます。

表示	説明
sys.query_store_query_text	取得されたクエリ テキストの情報。
sys.query_context_settings	動作に関連するコンテキスト設定 (プランの形状に影響する SET オプション、言語 ID など) の実行時の各種組み合わせ。
sys.query_store_query	クエリ テキストとコンテキスト設定の一意の組み合わせ。
sys.query_store_plan	SQL Server がシステム内でクエリを実行する際に使用するプランに関する情報。
sys.query_store_runtime_stats_interval	Query Store で集約情報が作成される時間間隔。
sys.query_store_runtime_stats	実行されたクエリ プランで収集された実行時の統計 (指定された間隔に基づいて集計)。

下記のスクリプトを実行して、最近 1 時間以内に実行された 25 種類のクエリの合計時間を取得します。

WITH AggregatedDurationLastHour

AS

(

   SELECT q.query_id, SUM(count_executions * avg_duration) AS total_duration,

   COUNT (distinct p.plan_id) AS number_of_plans

   FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

   ON qt.query_text_id = q.query_text_id

   JOIN sys.query_store_plan AS p ON q.query_id = p.query_id

   JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id

   JOIN sys.query_store_runtime_stats_interval AS rsi

   ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

   WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())

   AND rs.execution_type_desc = 'Regular'

   GROUP BY q.query_id

)

,OrderedDuration

AS

(

   SELECT query_id, total_duration, number_of_plans,

   ROW_NUMBER () OVER (ORDER BY total_duration DESC, query_id) AS RN

   FROM AggregatedDurationLastHour

)

SELECT qt.query_sql_text, object_name(q.object_id) AS containing_object,

total_duration AS total_duration_microseconds, number_of_plans,

CONVERT(xml, p.query_plan) AS query_plan_xml, p.is_forced_plan, p.last_compile_start_time,q.last_execution_time

FROM OrderedDuration od JOIN sys.query_store_query AS q ON q.query_id  = od.query_id

JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id

JOIN sys.query_store_plan p ON q.query_id = p.query_id

WHERE OD.RN <=25 ORDER BY total_duration DESC

このクエリから下のようなデータ セットが返されます。この結果はそのまま使用することも、Transact-SQL スクリプトでの分析の入力値として使用することもできます。Query Store のサンプルは他にもいくつか公開されていますので、こちら (英語) を併せてご覧ください。

 

手順 3: パフォーマンスが低下したクエリを修正する

ワークロードの中に、複数のプランで実行されていてパフォーマンスが異なるクエリが含まれている場合、Query Store を使用して、SQL Server が今後の実行で必ず最適なプランを使用するように強制することができます。下の図は、そのクエリの実行履歴の表示を示したものです。query_id と plan_id は永続的な内部識別番号で、Query Store が収集したすべてのクエリとプランに割り当てられます。

 

クエリ 1 のプラン概要ウィンドウを見ると、このクエリには 2 つのプランがあり、plan id = 1 の期間の方がより良い結果を残していることがわかります。SQL Server が今後必ずこのプランを使用するようにするには、plan id = 1 の選択中に [Force Plan] ボタンをクリックします。これにより、強制適用されたプランにチェック マークが表示され、このプランが使用されていることがわかるようになります。

 

Transact-SQL からプランを強制適用する

SSMS 2016 CTP2 より前のバージョンを使用している場合、Query Store に表示されるクエリを直接実行して、パフォーマンスが低下しているプランのクエリを特定することができます。上のスクリプトを以下のように少し変更すると、複数のプランのクエリを戻したり、比較用に各計測間隔でのプランごとの平均期間を含めるようにすることができます。

WITH AggregatedDurationLastHour

AS

(

   SELECT q.query_id, SUM(count_executions * avg_duration) AS total_duration,

   COUNT (distinct p.plan_id) AS number_of_plans

   FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

   ON qt.query_text_id = q.query_text_id

   JOIN sys.query_store_plan AS p ON q.query_id = p.query_id

   JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id

   JOIN sys.query_store_runtime_stats_interval AS rsi

   ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

   WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())

   AND rs.execution_type_desc = 'Regular'

   GROUP BY q.query_id

)

,OrderedDuration

AS

(

   SELECT query_id, total_duration, number_of_plans,

   ROW_NUMBER () OVER (ORDER BY total_duration DESC, query_id) AS RN

   FROM AggregatedDurationLastHour

)

SELECT qt.query_sql_text, object_name(q.object_id) AS containing_object, q.query_id,

p.plan_id,rsi.start_time as interval_start, rs.avg_duration,

CONVERT(xml, p.query_plan) AS query_plan_xml

FROM OrderedDuration od JOIN sys.query_store_query AS q ON q.query_id  = od.query_id

JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id

JOIN sys.query_store_plan AS p ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id

JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())

AND OD.RN <=25 AND number_of_plans > 1

ORDER BY total_duration DESC, query_id, rsi.runtime_stats_interval_id, p.plan_id

下のスクリーンショットは、上のスクリプトから取得されたデータ セットの一部です。

 

このデータ セットでは、query id = 1 のクエリが、パフォーマンスが異なる 2 つのプランで実行されたことがわかります (表の [query_plan_xml] 列をクリックするとその差を視覚的に確認できます)。緑の線で囲まれた plan id = 1 では、赤い線で囲まれた plan id = 2 と比較して、すべての計測期間で平均 2 ～ 3 倍も良好なパフォーマンスを得られていることがわかります (avg_duration の値が小さいなどの理由)。

このため、plan id = 1 には query id = 1 を強制適用することが推奨されます。次のコマンドを実行します。

EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 1;

次のステップ

Query Store のさらに詳しい情報については、オンラインで公開されているプレビュー版のドキュメント (英語) および Channel 9 の動画 (英語) をご覧ください。

ご意見やご不明な点がありましたら、この記事の下のコメント欄までお寄せください。皆様からのフィードバックは今後の機能強化に活用させていただきます。