クエリ ストアを使用してワークロードを監視するためのベスト プラクティス

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

この記事では、ワークロードで SQL Server クエリ ストアを使用するためのベスト プラクティスについて説明します。

• クエリ ストアを使用した構成と管理の詳細については、「クエリ ストアを使用したパフォーマンスの監視」を参照してください。
• アクションにつながる情報を検出し、クエリ ストアを使用してパフォーマンスを調整する方法については、「クエリ ストアを使用したパフォーマンスの調整」を参照してください。
• Azure SQL Database におけるクエリ ストアの運用の詳細については、「 Azure SQL Database でクエリ ストアを運用する」を参照してください。
• Azure Synapse Analytics では、クエリ ストアは専用 SQL プールに対して既定では有効になっていませんが、有効にできます。 クエリ ストアの追加の構成オプションはサポートされていません。 詳しくは、「Azure Synapse Analytics での履歴クエリのストレージと分析」を参照してください。

最新の SQL Server Management Studio を使用する

SQL Server Management Studio には、クエリ ストアを構成し、ワークロードに関する収集データを使用するための一連のユーザー インターフェイスが用意されています。 最新バージョンの SQL Server Management Studio をダウンロードします。

トラブルシューティング シナリオでクエリ ストアを使用する方法の簡単な説明については、Query Store Azure blogs を参照してください。

Azure SQL Database で Query Performance Insight を使用する

Azure SQL データベース でクエリ ストアを実行する場合、 クエリ パフォーマンスの分析情報 を使用して、経時的にリソース消費量を分析できます。 Management Studio と Azure Data Studio を使用して、CPU、メモリ、I/O など、すべてのクエリの詳細なリソース消費量を取得することができますが、クエリ パフォーマンスの分析情報 を使用すると、データベースの DTU 全体の消費量に対する影響を簡単かつ効率的に確認できます。 詳細については、「 Azure SQL Database Query Performance Insight」を参照してください。

エラスティック プール データベースでクエリ ストアを使用する

クエリ ストアは、すべてのデータベースで (高密度でパックされたAzure SQL データベースエラスティック プールであっても) 問題なく使用できます。 エラスティック プール内の多数のデータベースに対してクエリ ストアを有効にすると発生する可能性があった、リソースの過剰使用に関連するすべての問題は解決されました。

クエリ パフォーマンスのトラブルシューティングを開始する

次の図に示すように、クエリ ストアでのトラブルシューティングのワークフローはシンプルです。

前のセクションで説明したように、Management Studio を使用してクエリ ストアを有効にするか、次の Transact-SQL ステートメントを実行します。

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

クエリ ストアで、ワークロードを正確に表すデータ セットが収集されるまで、しばらく時間がかかります。 通常は、非常に複雑なワークロードの場合でも 1 日で十分です。 ただし、機能を有効にした後すぐにデータの探索を開始して、注意が必要なクエリを特定することができます。 Management Studio のオブジェクト エクスプローラーでデータベース ノードの下にある クエリ ストア サブフォルダーに移動し、特定のシナリオのトラブルシューティング ビューを開きます。

Management Studio のクエリ ストア ビューの操作には、一連の実行メトリックを使用します。メトリックはそれぞれ、次のいずれかの統計関数で表されます。

次の図は、クエリ ストアのビューの場所を示しています。

次の表では、各クエリ ストア ビューの用途を説明します。

パフォーマンスが最適ではないクエリを特定する際のアクションは、問題の性質によって異なります。

• クエリの実行プランが複数あり、最後のプランのパフォーマンスが前のプランよりも大幅に悪いような場合は、プランの強制適用メカニズムを使用することができます。 SQL Server がオプティマイザーのプランを強制しようとします。 プランの適用に失敗した場合、XEvent が発生し、オプティマイザーは通常の方法で最適化するように指示します。

• クエリに最適な実行のためのインデックスが欠落している場合があります。 この情報は、クエリの実行プラン内で確認できます。 欠落しているインデックスを作成し、クエリ ストアを使用してクエリのパフォーマンスを確認します。

SQL Database でワークロードを実行している場合、SQL Database Index Advisor にサインアップすると、推奨されるインデックスを自動的に取得できます。

• 実行プランの推定行数と実際の行数に大きな差がある場合は、統計情報を強制的に再コンパイルすることもできます。
• たとえば、クエリのパラメーター化を利用したり、より最適なロジックを実装したりする場合は、問題のあるクエリを書き直します。

クエリ ストアでクエリ データが収集されていることを継続的に確認する

クエリ ストアでは、操作モードが通知なしに変更されることがあります。 クエリ ストアの状態を定期的に監視して、クエリ ストアが動作していることを確認し、回避できたはずのエラーが発生しないようにしてください。 操作モードを確認して最も重要なパラメーターを表示するには、次のクエリを実行します。

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

actual_state_desc と desired_state_desc の違いは、操作モードが自動的に変更されたことを示します。 最も一般的な変更は、クエリ ストアが通知なしに読み取り専用モードに切り替わることです。 非常にまれな状況では、クエリ ストアが内部エラーにより、エラー状態になることがあります。

実際の状態が読み取り専用になっている場合は、readonly_reason 列で根本原因を調べます。 通常は、サイズ クォータを超えたために、クエリ ストアが読み取り専用モードに移行したことがわかります。 その場合は readonly_reason を 65536 に設定します。 他の理由については、「sys.database_query_store_options (Transact-SQL)」を参照してください。

クエリ ストアを読み取り/書き込みモードに戻してデータの収集を再開するには、次の手順を実行します。

• ALTER DATABASE のMAX_STORAGE_SIZE_MB オプションを使用して、ストレージの最大サイズを増やします。

• 次のステートメントを使用して、クエリ ストアのデータをクリーンアップする。

  ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
  

操作モードを明示的に読み取り/書き込みモードに戻す次のステートメントを実行することで、これらの手順のいずれかまたは両方を適用できます。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

操作モードが変更されないよう事前に対処するには、次の手順を実行します。

• 推奨事項を取り入れることで、操作モードが通知なしに変更されることを防ぐことができます。 クエリ ストアのサイズが許可される最大値を常に下回り、読み取り専用モードに移行される可能性が大幅に低くなるようにしてください。 クエリ ストアの構成に関するセクションの説明に従って、サイズ ベースのポリシーを有効にし、クエリ ストアでサイズが制限に近づくと自動的にデータがクリーンアップされるようにします。
• 最新のデータを確実に保持するには、古くなった情報を定期的に削除するように時間ベースのポリシーを構成します。
• 最後に、クエリ ストア キャプチャ モードを Auto に設定することを検討してください。そうすることで、通常はワークロードにとってあまり関連性のないクエリが除外されるためです。

エラー状態

クエリ ストアを復旧させるには、明示的に読み取り/書き込みモードに設定してみて、実際の状態を再度確認します。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

それでも問題が解決しない場合は、ディスクに破損したクエリ ストア データが保存されていることを示しています。

SQL Server 2017 (14.x) 以降では、影響を受けたデータベース内で sys.sp_query_store_consistency_check ストアド プロシージャを実行することで、クエリ ストアを復旧させることができます。 復旧操作を試みる前にクエリ ストアを無効にする必要があります。 QDS の整合性チェックと復旧を実行するために使用または変更するサンプル クエリを次に示します。

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

SQL Server 2016 (13.x) の場合は、示されているようにクエリ ストアからデータをクリアする必要があります。

復旧に失敗した場合は、読み取り/書き込みモードを設定する前にクエリ ストアをクリアしてみてください。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

パラメーター化されていないクエリを使用しない

パラメーター化されていないクエリを必要でないときに使用することは、ベスト プラクティスではありません。 たとえば、アドホック分析の場合です。 キャッシュされたプランは再利用できません。再利用すると、クエリ オプティマイザーによって一意のクエリ テキストごとにクエリが強制的にコンパイルされます。 詳細については、「Guidelines for using forced parameterization」 (強制パラメーター化使用のガイドライン) をご覧ください。

また、さまざまなクエリ テキストが多数ある可能性があり、その結果として、同じような形のさまざまな実行プランが多数存在することになるため、クエリ ストアでサイズ クォータがすぐに制限を超える可能性があります。 その結果、ワークロードのパフォーマンスが最適でなくなり、クエリ ストアが読み取り専用モードに切り替わったり、後続のクエリへの対応を試みるためにデータが常に削除されるようになる可能性があります。

次のオプションを検討してください。

• 必要に応じて、クエリをパラメーター化します。 たとえば、ストアド プロシージャまたは sp_executesql 内にクエリをラップします。 詳細については、「パラメーターと実行プランの再利用」をご覧ください。
• ワークロードに 1 回限りのアドホック バッチが多数含まれており、そこで異なるクエリ プランが使用されている場合は、アドホック ワークロードの最適化オプションを使用します。
  • 個々の query_hash 値の数と、sys.query_store_query 内のエントリの総数を比較します。 この比率が 1 に近い場合、アドホック ワークロードで異なるクエリが生成されます。
• 異なるクエリ プランの数が多くない場合は、データベースまたはクエリのサブセットに対して強制パラメーター化を適用します。
  • 選択したクエリに対してのみパラメータ化を強制するには、プラン ガイドを使用します。
  • ワークロード内の異なるクエリ プランの数が少ない場合は、パラメーター化データベース オプション コマンドを使用して、強制パラメーター化を構成します。 たとえば、個々の query_hash の数と sys.query_store_query 内のエントリの総数の比率が 1 よりもかなり小さい場合です。
• リソース消費の少ないアドホック クエリを自動的に除外するには、QUERY_CAPTURE_MODE を AUTO に設定します。

クエリ ストアでパラメーター化されていないクエリを検索する

次のクエリを使用してクエリ ストアに格納されているプランの数は、クエリ ストア DMV、SQL Server、Azure SQL Managed Instance、または Azure SQL Database を使用して確認できます。

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

次のサンプルを使用すると、拡張イベント セッションが作成されて、イベント query_store_db_diagnostics がキャプチャされます。これは、クエリ リソースの消費量を診断するのに役立ちます。 SQL Server では、この拡張イベント セッションによって、SQL Server ログ フォルダーにイベント ファイルが既定で作成されます。 たとえば、Windows の既定の SQL Server 2019 (15.x) インストールでは、フォルダー C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log にイベント ファイル (.xel ファイル) を作成する必要があります。 Azure SQL Managed Instance の場合は、代わりに Azure Blob Storage の場所を指定します。 詳細については、Azure SQL Managed Instance の XEvent event_file に関する記事を参照してください。 イベント "qds.query_store_db_diagnostics" は、Azure SQL Database には使用できません。

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

このデータを使用すると、クエリ ストアのプラン数だけでなく、他の多くの統計情報も見つけることができます。 クエリ ストアによって追跡されるメモリの使用量とプランの数を把握するために、イベント データ内の plan_count列、query_count 列、max_stmt_hash_map_size_kb 列、および max_size_mb 列を探します。 プラン数が通常よりも多い場合は、パラメーター化されていないクエリの増加を示している可能性があります。 次のクエリ ストア DMV クエリを使用して、クエリ ストア内のパラメーター化されたクエリとパラメーター化されていないクエリを確認します。

パラメーター化されたクエリ:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

パラメーター化されていないクエリ:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

コンテイン オブジェクトのために DROP と CREATE のパターンを使用しない

クエリ ストアでは、クエリ エントリと親オブジェクト (ストアド プロシージャ、関数、トリガー) を関連付けます。 親オブジェクトを再作成すると、同じクエリ テキストに対して新しいクエリ エントリが生成されます。 これにより、そのクエリのパフォーマンス統計情報を経時的に追跡し、プランの強制適用メカニズムを使用できなくなります。 この状況を回避するには、可能な限り、ALTER <object> プロセスを使用して親オブジェクトの定義を変更します。

強制適用されたプランの状態を定期的に確認する

プランの強制適用は、重要なクエリのパフォーマンスを修正してより正確な予測を可能にするための便利なメカニズムです。 プラン ヒントやプラン ガイドと同様に、プランの強制適用は、今後の実行で使用されることを保証するものではありません。 通常、実行プランによって参照されるオブジェクトが変更または削除されるような方法でデータベース スキーマが変更された場合、プランを強制的に適用できません。 その場合、SQL Server はクエリの再コンパイルに戻りますが、強制適用が失敗した実際の理由は sys.query_store_plan に示されます。 次のクエリは、強制適用されたプランに関する情報を返します。

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

理由の一覧については、「sys.query_store_plan」を参照してください。 query_store_plan_forcing_failed XEvent を使用して、プラン強制の失敗を追跡してトラブルシューティングすることもできます。

プランが強制適用されたクエリの場合はデータベースの名前を変更しない

実行プランでは、database.schema.object のような 3 つの部分で構成される名前を使用して、オブジェクトを参照します。

データベース名を変更すると、プランの強制適用が失敗し、その後のすべてのクエリ実行で再コンパイルが発生します。

ミッション クリティカルなサーバーでのクエリ ストアの使用

グローバル トレース フラグ 7745 と 7752 を使用すると、クエリ ストアを使ってデータベースの可用性を向上させることができます。 詳しくは、「トレース フラグ」をご覧ください。

• トレース フラグ 7745 では、SQL Server がシャットダウンされる前に、クエリ ストアによってディスクにデータを書き込む既定の動作が行われないようにします。 つまり、DATA_FLUSH_INTERVAL_SECONDS で定義された時間枠まで、収集されたもののディスクにはまだ保存されていないクエリ ストア データは失われます。
• トレース フラグ 7752 では、クエリ ストアの非同期読み込みが有効になります。 これにより、データベースをオンラインにすることができ、クエリ ストアが完全に復旧される前にクエリを実行できます。 既定の動作では、クエリ ストアの同期読み込みが行われます。 既定の動作では、クエリ ストアが復旧される前にクエリを実行することはできませんが、データ コレクションでクエリが失われることもありません。

Azure SQL データベースのアクティブ geo レプリケーションでクエリ ストアを使用する

Azure SQL Database のセカンダリ アクティブ geo レプリカのクエリ ストアは、プライマリ レプリカでのアクティビティの読み取り専用コピーになります。

Azure SQL Database geo レプリケーションで階層の不一致が起こらないようにしてください。 セカンダリ データベースは、プライマリ データベースと同じまたはそれに近いコンピューティング サイズで、プライマリ データベースと同じサービス レベルにある必要があります。 sys.dm_db_wait_stats で HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO という待機の種類を探します。これは、セカンダリ ラグが原因でプライマリ レプリカのトランザクション ログ レートを調整することを示します。

アクティブ geo レプリケーションのセカンダリ Azure SQL データベースのサイズの推定と構成の詳細については、「セカンダリ データベースの構成」を参照してください。

ワークロードに合わせてクエリ ストアをチューニングする

クエリ ストアの構成と管理に関するベスト プラクティスと推奨事項については、クエリ ストアを管理するためのベスト プラクティスに関する記事を参照してください。

関連するコンテンツ

• ALTER DATABASE SET オプション (Transact-SQL)
• クエリ ストアのカタログ ビュー (Transact-SQL)
• クエリ ストアのストアド プロシージャ (Transact-SQL)
• インメモリ OLTP でのクエリ ストアの使用
• クエリ処理アーキテクチャ ガイド
• クエリ ストアのヒント
• クエリ ストアを使用したパフォーマンスの監視
• クエリ ストアを使用したパフォーマンスの調整
• Azure Synapse Analytics での履歴クエリのストレージと分析