クエリ ストアを使用するときの推奨事項Best Practice with the Query Store

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

この記事では、ワークロードでクエリ ストアを使用するためのベスト プラクティスについて説明します。This article outlines the best practices for using the Query Store with your workload.

最新の SQL Server Management StudioSQL Server Management Studio を使用するUse the latest SQL Server Management StudioSQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio には、クエリ ストアを構成するためのユーザー インターフェイスと、ワークロードについて収集されたデータを使用するためのユーザー インターフェイスが用意されています。has set of user interfaces designed for configuring Query Store as well as for consuming collected data about your workload.
最新バージョンの Management StudioManagement Studioここからダウンロードしてください。Download the latest version of Management StudioManagement Studio here.

クエリ ストアに関する @Azure ブログの記事で、トラブルシューティングのシナリオでクエリ ストアを使用する方法について簡単に説明しています。For a quick description on how to use Query Store in troubleshooting scenarios refer to Query Store @Azure Blogs.

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

SQL DatabaseSQL Database でクエリ ストアを実行する場合、 Query Performance Insight を使用して、時間の経過に応じた DTU 消費量を分析できます。If you run Query Store in SQL DatabaseSQL Database you can use Query Performance Insight to analyze DTU consumption over time.
Management StudioManagement Studio を使用してすべてのクエリの詳細なリソースの消費量を取得することもできますが (CPU、メモリ、I/O など)、Query Performance Insight を使用すると、迅速かつ効率的な方法で、データベースの全体的な DTU 消費量に与える影響を判断できます。While you can use Management StudioManagement Studio to get detailed resource consumption for all your queries (CPU, memory, I/O, etc.), Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database.
詳細については、「 Azure SQL Database Query Performance Insight」を参照してください。For more information, see Azure SQL Database Query Performance Insight.

エラスティック プール データベースでクエリ ストアを使用するUsing Query Store with Elastic Pool Databases

クエリ ストアは、すべてのデータベースで (高密度でパックされたプールであっても) 問題なく使用できます。You can use Query Store in all databases without concerns, in even densely packed pools. エラスティック プール内の多数のデータベースに対してクエリ ストアを有効にすると発生する可能性があった、リソースの過剰使用に関連するすべての問題は解決されました。All issues related to excessive resource usage, that might have occurred when Query Store was enabled for the large number of databases in the Elastic Pools, have been resolved.

ワークロードに合わせてクエリ ストアを調整するKeep Query Store adjusted to your workload

ワークロードとパフォーマンスのトラブルシューティングの要件に基づいて、クエリ ストアを構成します。Configure Query Store based on your workload and performance troubleshooting requirements.
最初は既定のパラメーターを使用してもよいですが、時間の経過と共にクエリ ストアの動作がどのように変化するかを監視して、必要に応じて構成を調整する必要があります。The default parameters are good enough to start, but you should monitor how Query Store behaves over time and adjust its configuration accordingly:

クエリ ストア プロパティquery-store-properties

次に、パラメーター値を設定する際のガイドラインを示します。Here are guidelines to follow for setting parameter values:

最大サイズ (MB): クエリ ストアが使用するデータベース内のデータ領域の制限を指定します。Max Size (MB): Specifies the limit for the data space that Query Store will take inside your database. これは、クエリ ストアの操作モードに直接影響する最も重要な設定です。This is the most important setting that directly affects operation mode of the Query Store.

クエリ ストアがクエリや実行プラン、統計情報を収集する間、データベース内でクエリ ストアのサイズが増え続けます。While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. サイズが制限に達すると、クエリ ストアの操作モードが自動的に読み取り専用に切り替わり、新しいデータの収集が停止します。以降、パフォーマンス分析は正確ではなくなります。When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

既定値は 100 MB ですが、ワークロードが多数のクエリとプランを生成する場合や、クエリ履歴を長期間保持する必要がある場合は、より大きなサイズが必要になる可能性があります。The default value (100 MB) may not be sufficient if your workload generates large number of different queries and plans or if you want to keep query history for a longer period of time. クエリ ストアが読み取り専用モードに移行しないよう、現在の使用量を追跡して最大サイズ (MB) を増やしてください。Keep track of current space usage and increase the Max Size (MB) to prevent Query Store from transitioning to read-only mode. クエリ ストアのサイズに関する最新の情報を取得するには、 Management StudioManagement Studio を使用するか、次のスクリプトを実行します。Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

USE [QueryStoreDB];  
GO  
  
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;  

次のスクリプトは、新しい最大サイズ (MB) を設定します。The following script sets a new Max Size (MB):

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);  

データ フラッシュ間隔: 収集されたランタイム統計をディスクに保存する間隔 (秒単位) を定義します (既定値は 900 秒 (15 分))。Data Flush Interval: Defines frequency in seconds to persist collected runtime statistics to disk (the default is 900 seconds, which is 15 minutes). ワークロードで生成される異なるクエリとプランの数が多くない場合、またはデータベースをシャットダウンする前にデータを長時間保持できる場合は、大きい値を使用することを検討します。Consider using higher value if your workload does not generates large number of different queries and plans, or if you can withstand longer time to persist data before a database shutdown.

注意

トレース フラグ 7745 を使用すると、フェールオーバーまたはシャットダウン コマンドが発生したとき、クエリ ストアのデータはディスクに書き込まれません。Using trace flag 7745 will prevent Query Store data from being written to disk in case of a failover or shutdown command. 詳しくは、「 ミッション クリティカルなサーバーにトレース フラグを使用して、障害からの回復を向上させる」セクションをご覧ください。See the Use trace flags on mission critical servers to improve recovery from disaster section for more detail.

データ フラッシュ間隔に異なる値を設定するには、SQL Server Management StudioSQL Server Management Studio または Transact-SQLTransact-SQL を使用します。Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set different value for Data Flush Interval:

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);  

統計情報の収集間隔: 実行時統計情報を収集する間隔を定義します (既定値は 60 分)。Statistics Collection Interval: Defines level of granularity for the collected runtime statistic (the default is 60 minutes). この間隔を短くして、問題を検出して軽減するまでの時間を短縮したい場合は、値を小さくすることを検討してください。ただし、クエリ ストアのデータのサイズに直接影響があるので注意が必要です。Consider using lower value if you require finer granularity or less time to detect and mitigate issues but keep in mind that it will directly affect the size of Query Store data. 統計情報の収集間隔に別の値を設定するには、SQL Server Management StudioSQL Server Management Studio または Transact-SQLTransact-SQL を使用します。Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set different value for Statistics Collection Interval:

ALTER DATABASE [QueryStoreDB] 
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);  

古いクエリのしきい値 (日): 保存する実行時統計と非アクティブ クエリの保有期間を制御する、時間ベースのクリーンアップ ポリシーです。Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
既定では、クエリ ストアはデータを 30 日間保持するよう構成されていますが、シナリオによっては必要以上に長すぎることもあります。By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

使用予定のない履歴データは保持しないようにしてください。Avoid keeping historical data that you do not plan to use. そうすることで、クエリ ストアが読み取り専用モードになる可能性を減らせます。This will reduce changes to read-only status. また、クエリ ストアのデータのサイズと、問題を検出して軽減するまでの時間を予測しやすくなります。The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. 時間ベースのクリーンアップ ポリシーを構成するには、 Management StudioManagement Studio または次のスクリプトを使用します。Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));  

サイズ ベースのクリーンアップ モード: クエリ ストアのデータ サイズが制限に達したときに、データの自動クリーンアップを行うかどうかを指定します。Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

クエリ ストアが常に読み取り/書き込みモードで実行して最新のデータを収集するよう、サイズ ベースのクリーンアップを有効にすることを強くお勧めします。It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);  

クエリ ストアのキャプチャ モード: クエリ ストアのクエリ キャプチャ ポリシーを指定します。Query Store Capture Mode: Specifies the query capture policy for the Query Store.

  • All - すべてのクエリをキャプチャします。All - Captures all queries. 既定のオプションです。This is the default option.

  • Auto - 頻度の低いクエリと、コンパイルと実行時間の短いクエリは無視されます。Auto - Infrequent queries and queries with insignificant compile and execution duration are ignored. 実行回数、コンパイル、実行時間のしきい値は内部的に決定されます。Thresholds for execution count, compile and runtime duration are internally determined.

  • None - クエリ ストアが新しいクエリのキャプチャを停止します。None - Query Store stops capturing new queries.

次のスクリプトは、クエリ キャプチャ モードを Auto に設定します。The following script sets the Query Capture mode to Auto:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);  

クエリ パフォーマンスのトラブルシューティングを開始する方法How to start with query performance troubleshooting

次の図に示すように、クエリ ストアでのトラブルシューティングのワークフローはシンプルです。Troubleshooting workflow with Query Store is simple, as shown on the following diagram:

クエリ ストアのトラブルシューティングquery-store-troubleshooting

前のセクションで説明したように、 Management StudioManagement Studio を使用してクエリ ストアを有効にするか、次の Transact-SQLTransact-SQL ステートメントを実行します。Enable Query Store by using Management StudioManagement Studio as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;  

クエリ ストアがワークロードを正確に表すデータ セットを収集するまでに、しばらく時間がかかります。It will take some time until Query Store collects the data set that accurately represents your workload. 通常は、非常に複雑なワークロードの場合でも 1 日で十分です。Usually, one day is enough even for very complex workloads. ただし、機能を有効にした後すぐにデータの探索を開始して、注意が必要なクエリを特定することもできます。However, you can start exploring the data and identify queries that need your attention immediately after you enabled the feature.
Management StudioManagement Studio のオブジェクト エクスプローラーでデータベース ノードの下にある Query Store サブ フォルダーに移動し、特定のシナリオのトラブルシューティングのビューを開きます。Navigate to the Query Store sub-folder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.
Management StudioManagement Studio のクエリ ストア ビューの操作には、一連の実行メトリックを使用します。メトリックはそれぞれ、次のいずれかの統計関数で表されます。Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

SQL ServerSQL Server のバージョンversion 実行メトリックExecution metric 統計関数Statistic function
SQL Server 2016 (13.x)SQL Server 2016 (13.x) CPU 時間、実行時間、実行回数、論理読み取り、論理書き込み、メモリ消費量、物理読み取り、CLR 時間、並列処理の次数 (DOP)、行数CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), and Row count Average、Maximum、Minimum、Standard Deviation、TotalAverage, Maximum, Minimum, Standard Deviation, Total
SQL Server 2017 (14.x)SQL Server 2017 (14.x) CPU 時間、実行時間、実行回数、論理読み取り、論理書き込み、メモリ消費量、物理読み取り、CLR 時間、並列処理の次数 (DOP)、行数、ログ メモリ、TempDB メモリ、待機時間CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), Row count, Log memory, TempDB memory, and Wait times Average、Maximum、Minimum、Standard Deviation、TotalAverage, Maximum, Minimum, Standard Deviation, Total

次の図は、クエリ ストアのビューの場所を示しています。The following graphic shows how to locate Query Store views:

クエリ ストア ビューQuery Store views

次の表では、各クエリ ストア ビューの用途を説明します。The following table explains when to use each of the Query Store views:

SSMS ビューSSMS view シナリオScenario
機能低下したクエリRegressed Queries 実行メトリックが最近低下した (悪化した) クエリを特定します。Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse).
このビューを使用して、アプリケーションで確認されたパフォーマンスの問題と、実際に修正や改善の必要があるクエリを関連付けます。Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.
全体的なリソース消費量Overall Resource Consumption 実行メトリックのいずれかについて、データベースの全体的なリソース消費量を分析します。Analyze the total resource consumption for the database for any of the execution metrics.
このビューを使用して、リソースのパターン (日中または夜間のワークロード) を特定し、データベースの全体的な消費量を最適化します。Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
最もリソースを消費するクエリTop Resource Consuming Queries 対象となる実行メトリックを選択し、対象期間内で最も極端な値を持つクエリを特定します。Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval.
このビューを使用して、データベースのリソース消費量に最も大きな影響を与えているクエリに焦点を絞ります。Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.
強制適用されたプランのあるクエリQueries With Forced Plans クエリ ストアを使って以前に強制適用されたプランを一覧表示します。Lists previously forced plans using Query Store.
このビューを使って、現在強制適用されているすべてのプランに簡単にアクセスします。Use this view to quickly access all currently forced plans.
高バリエーションのクエリQueries With High Variation 目的の期間内の継続時間、CPU 時間、IO、メモリ使用率など、使用可能なディメンションのいずれかに関連して実行バリエーションが高いクエリを分析します。Analyze queries with high execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage in the desired time interval.
このビューを使用して、アプリケーション全体のユーザー エクスペリエンスに影響を及ぼす可能性のある、パフォーマンスの差異が大きいクエリを特定します。Use this view to identify queries with widely variant performance that can be impacting user experience across your applications.
クエリ待機統計Query Wait Statistics データベースで最もアクティブな待機カテゴリ、および選択した待機カテゴリに対して最も影響を与えているクエリを分析します。Analyze wait categories that are most active in a database, and which queries contribute most to the selected wait category.
このビューを使用して待機統計を分析し、アプリケーション全体のユーザー エクスペリエンスに影響を及ぼしている可能性のあるクエリを特定します。Use this view to analyze wait statistics and identify queries that may be impacting user experience across your applications.

適用対象: SQL Server Management StudioSQL Server Management Studio v18.0 以降および SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: Starting with SQL Server Management StudioSQL Server Management Studio v18.0 and SQL Server 2017 (14.x)SQL Server 2017 (14.x)
追跡対象のクエリTracked Queries 最も重要なクエリの実行をリアルタイムで追跡します。Track the execution of the most important queries in real time. このビューは通常、強制適用されたプランを持つクエリがあり、クエリのパフォーマンスを安定させる必要がある場合に使用します。Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

ヒント

Management StudioManagement Studio を使用して最もリソースを消費するクエリを特定し、プラン変更により機能低下したクエリを修正する方法の詳細については、クエリ ストアに関する @Azure ブログの記事を参照してください。For a detailed description how to use Management StudioManagement Studio to identify the top resource consuming queries and fix those that regressed due to the change of a plan choice, see Query Store @Azure Blogs.

パフォーマンスが低下したクエリを特定する際に必要なアクションは、問題の性質によって異なります。When you identify a query with sub-optimal performance, your action depends on the nature of the problem.

  • クエリの実行プランが複数あり、最後のプランのパフォーマンスが前のプランよりも大幅に悪いような場合は、プランの強制適用メカニズムを使用することができます。If the query was executed with multiple plans and the last plan is significantly worse than previous plan, you can use the plan forcing mechanism to force it. SQL ServerSQL Server がオプティマイザーのプランを強制しようとします。tries to force the plan in the optimizer. プランの適用に失敗した場合、XEvent が発生し、オプティマイザーは通常の方法で最適化するように指示します。If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

    クエリ ストアのプランの強制query-store-force-plan

注意

上の図では特定のクエリ プランに異なる図形が使われている場合があり、考えられる各状態の意味を次に示します。The above graphic may feature different shapes for specific query plans, with the following meanings for each possible status:

|図形Shape|意味Meaning|
|-------------------|-------------| |CircleCircle|クエリ完了 (通常の実行が正常に終了しました)Query Completed (Regular Execution successfully finished)| |SquareSquare|キャンセル (クライアントが開始した実行中止)Cancelled (Client initiated aborted execution)| |TriangleTriangle|失敗 (例外による実行中止)Failed (Exception aborted execution)| また、図形のサイズは指定期間内でのクエリ実行回数を反映し、実行回数が多いほどサイズが大きくなります。Also, the size of the shape reflects query execution count within the specified time interval, increasing in size with a higher number of executions.

  • クエリが最適に実行するために必要なインデックスが欠落している場合があります。You may conclude that your query is missing an index for optimal execution. この情報は、クエリの実行プラン内で確認できます。This information is surfaced within the query execution plan. クエリ ストアを使用して欠落しているインデックスを作成し、クエリのパフォーマンスを確認します。Create the missing index and check the query performance using the Query Store.

    クエリ ストアのプラン表示query-store-show-plan

    SQL DatabaseSQL Databaseでワークロードを実行している場合、 SQL DatabaseSQL Database Index Advisor にサインアップすると、推奨されるインデックスを自動的に取得できます。If you run your workload on SQL DatabaseSQL Database, sign up for SQL DatabaseSQL Database Index Advisor to automatically receive index recommendations.

  • 実行プランの推定行数と実際の行数に大きな差がある場合は、統計情報を強制的に再コンパイルすることもできます。In some cases you may enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.

  • 問題のあるクエリを書き直します。Rewrite problematic queries. たとえば、クエリのパラメーター化を利用したり、より最適なロジックを実装したりする場合などです。For example to take advantages of query parameterization or to implement more optimal logic.

クエリ ストアがクエリ データを収集していることを定期的に確認するVerify Query Store is collecting query data continuously

クエリ ストアの操作モードは、通知なしに変更されることがあります。Query Store can silently change operations mode. クエリ ストアの状態を定期的に監視して、クエリ ストアが問題なく動作していることを確認し、回避できたはずのエラーが発生しないようにしてください。You should regularly monitor the state of the Query Store to ensure that the Query Store is operating, and to take action to avoid failures due to preventable causes. 操作モードを確認して最も重要なパラメーターを表示するには、次のクエリを実行します。Execute the following query to determine the operation mode and view the most relevant parameters:

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_descdesired_state_desc が一致していない場合、それは操作モードが自動的に変更されていることを示します。The difference between the actual_state_desc and desired_state_desc indicates that a change of operations mode occurred automatically. 最も一般的な変更は、クエリ ストアが通知なしで読み取り専用モードに切り替わることです。The most common change is for the Query Store to silently switch to read-only mode. 非常にまれなケースとしては、クエリ ストアが内部エラーにより最終的にエラー状態になることがあります。In extremely rarely circumstances, Query Store can end up in the ERROR state because of internal errors.

実際の状態が読み取り専用になっている場合は、 readonly_reason 列で根本原因を調べます。When the actual state is read-only, use the readonly_reason column to determine the root cause. 通常は、クエリ ストアがサイズの制限を超えたために読み取り専用モードに移行したことが原因です。Typically you will find that Query Store transitioned to read-only mode because the size quota was exceeded. その場合、readonly_reason は 65536 に設定されています。In that case the readonly_reason is set to 65536. 他の理由については、「sys.database_query_store_options (Transact-SQL)」を参照してください。For other reasons, see sys.database_query_store_options (Transact-SQL).

クエリ ストアを読み取り/書き込みモードに戻してデータの収集を再開するには、次の手順を実行します。Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • ALTER DATABASEMAX_STORAGE_SIZE_MBオプションを使用して、ストレージの最大サイズを増やします。Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • 次のステートメントを使用して、クエリ ストアのデータをクリーンアップする。Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;  
    

操作モードを明示的に読み取り/書き込みモードに戻す次のステートメントを実行することで、これらの手順のいずれか (または両方) を適用できます。You can apply one or both of these steps by the executing the following statement that explicitly changes operation mode back to read-write:

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

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

  • 推奨事項を取り入れることで、操作モードが通知なしに変更されることを防ぐことができます。You can prevent silent changes of operation mode by applying best practices. クエリ ストアのサイズが許可される最大値を常に下回っていれば、読み取り専用モードに変更される可能性が大幅に下がります。If you ensure that Query Store size is always below the maximally allowed value that will dramatically reduce a chance of transitioning to read-only mode. クエリ ストアの構成 に関するセクションの説明に従ってサイズ ベースのポリシーを有効にし、クエリ ストアがサイズ制限に近づくと自動的にデータがクリーンアップされるようにします。Activate size-based policy as described in the Configure Query Store section, so that the Query Store automatically cleans data when the size approaches the limit.

  • 最新のデータを確実に保持するには、時間ベースのポリシーを構成して古くなった情報を定期的に削除します。In order to make sure that most recent data is retained, configure time-based policy to remove stale information regularly.

  • 最後に、クエリ キャプチャ モードを Auto に設定することを検討してください。そうすれば、ワークロードにとって重要でないクエリを除外できます。Finally, you should consider setting Query Capture Mode to Auto as it filters out queries that are usually less relevant for your workload.

エラー状態Error State

クエリ ストアを復旧するには、明示的に読み取り/書き込みモードに設定し、実際の状態を再度確認します。To recover Query Store try explicitly setting the read-write mode and check actual state again.

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;  

それでも問題が解決しない場合、ディスクに破損したクエリ ストア データが存在しています。If the problem persists, it indicates corruption of the Query Store data is persisted on the disk.

SQL 2017 以上では、影響を受けたデータベース内で sp_query_store_consistency_check ストアド プロシージャを実行することで、クエリ ストアを復旧させることができます。For SQL 2017 and later, Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. 2016 の場合は、次に示すように、クエリ ストアからデータをクリアする必要があります。For 2016, you will need to clear the data from the Query Store as shown below.

復旧できない場合は、読み取り/書き込みモードを要求する前にクエリ ストアのクリアを試すことができます。If that did not help, you can try to clear Query Store before requesting read-write mode.

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;  

最適なクエリ ャプチャ モードを設定するSet the optimal query capture mode

最も重要なデータをクエリ ストアに保存します。Keep the most relevant data in Query Store. 次の表では、各クエリ キャプチャ モードの一般的なシナリオについて説明します。The following table describes typical scenarios for each Query Capture Mode:

クエリ キャプチャ モードQuery Capture Mode シナリオScenario
AllAll すべてのクエリの形式とその実行頻度やその他の統計情報の観点から、ワークロードを詳しく分析します。Analyze your workload thoroughly in terms of all queries shapes and their execution frequencies and other statistics.

ワークロードの新しいクエリを特定します。Identify new queries in your workload.

ユーザーまたは自動パラメーター化の機会を識別するためにアドホック クエリが使用されているかどうかを検出します。Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.
AutoAuto 定期的に実行されるクエリや大量にリソースを消費するクエリなど、対応が必要な重要なクエリに焦点を絞ります。Focus your attention on relevant and actionable queries; those queries that execute regularly or that have significant resource consumption.
NoneNone 実行時に監視する必要があるクエリ セットを既にキャプチャしており、他のクエリによる影響を受けたくない場合に使用します。You have already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries may introduce.

このモードは、テストやベンチマークの環境に適しています。None is suitable for testing and bench-marking environments.

このモードは、アプリケーションのワークロードを監視するよう構成したクエリ ストアの構成を販売するソフトウェア ベンダーにも適しています。None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

重要な新しいクエリを追跡して最適化する機会を見逃す可能性があるので、このモードを使用する際は注意してください。None should be used with caution as you might miss the opportunity to track and optimize important new queries. シナリオで必要な特別な場合を除き、このモードは使用しないでください。Avoid using None unless you have a specific scenario that requires it.

最も重要なデータをクエリ ストアに保存するKeep the most relevant data in Query Store

重要なデータのみを格納するようクエリ ストアを構成すると、通常のワークロードへの影響も最小限に抑えながら、効果的にトラブルシューティングを行えます。Configure the Query Store to contain only the relevant data and it will run continuously providing great troubleshooting experience with a minimal impact on your regular workload.
次の表に、推奨事項を示します。The following table provides best practices:

推奨事項Best Practice 設定Setting
保存する履歴データに制限を設ける。Limit retained historical data. 時間ベースのポリシーを構成して自動クリーンアップを有効にします。Configure time-based policy to activate auto-cleanup.
重要でないクエリを除外する。Filter out non-relevant queries. クエリ キャプチャ モードを Auto に設定します。Configure Query Capture Mode to Auto.
最大サイズに達したときに、重要でないクエリを削除する。Delete less relevant queries when maximum size is reached. サイズ ベースのクリーンアップ ポリシーを有効にします。Activate size-based cleanup policy.

パラメーター化されていないクエリを使用しないAvoid using non-parameterized queries

アドホック分析など必ずしも必要でない場面でパラメーター化されていないクエリを使用することはお勧めしません。Using non-parameterized queries when that is not absolutely necessary (for example in case of ad-hoc analysis) is not a best practice. キャッシュされたプランは再利用できません。再利用すると、クエリ オプティマイザーによって一意のクエリ テキストごとにクエリが強制的にコンパイルされます。Cached plans cannot be reused which forces Query Optimizer to compile queries for every unique query text. 詳細については、「強制パラメーター化使用のガイドライン」をご覧ください。For more information, see Guidelines for Using Forced Parameterization.
また、クエリ テキストの数が増えると類似する実行プランの数も増えるため、クエリ ストアのサイズがすぐに制限を超えてしまう可能性があります。Also, Query Store can rapidly exceed the size quota because of potentially a large number of different query texts and consequently a large number of different execution plans with similar shape.
その結果、ワークロードのパフォーマンスが最適化されず、クエリ ストアが読み取り専用モードに切り替わったり、後続のクエリに対応するためにデータが常に削除されるようになったりする可能性があります。As a result, performance of your workload will be sub-optimal and Query Store might switch to read-only mode or might be constantly deleting the data trying to keep up with the incoming queries.

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

  • 可能であればクエリをパラメーター化します (例: sp_executesql などのストアド プロシージャ内にクエリをラップする)。Parameterize queries where applicable, for example wrap queries inside a stored procedure or sp_executesql. 詳細については、「パラメーターと実行プランの再利用」をご覧ください。For more information, see Parameters and Execution Plan Reuse.

  • ワークロードに 1 回限りのアドホック バッチが多数含まれており、そこで異なるクエリ プランが使用されている場合は、 [アドホック ワークロードの最適化] オプションを使用します。Use the Optimize for Ad Hoc Workloads option if your workload contains many single use ad-hoc batches with different query plans.

    • 個々の query_hash 値の数と、sys.query_store_query 内のエントリの総数を比較します。Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. この比率が 1 に近い場合、アドホック ワークロードは異なるクエリを生成します。If the ratio is close to 1 your ad-hoc workload generates different queries.
  • 異なるクエリ プランの数がそれほど多くない場合は、データベースまたはクエリのサブセットに対して強制パラメーター化を適用します。Apply forced parameterization, for the database or for a subset of queries if the number of different query plans is not large.

    • 選択したクエリに対してのみパラメータ化を強制するには、プラン ガイドを使用します。Use plan guide to force parameterization only for the selected query.

    • sys.query_store_query の個々の query_hash の数と、エントリの総数の比率が 1 を下回り、ワークロードに含まれる異なるクエリ プランが少ない場合は、パラメーター化データベース オプションを使用して強制パラメーター化を構成します。Configure forced parameterization as using the Parameterization database option command, if there are a small number of different query plans in your workload: when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.

  • リソース消費の少ないアドホック クエリを自動的に除外するには、 クエリ キャプチャ モード を AUTO に設定します。Set the Query Capture Mode to AUTO to automatically filter out ad-hoc queries with small resource consumption.

クエリの親オブジェクトを保持する場合は DROP と CREATE のパターンを避けるAvoid a DROP and CREATE pattern when maintaining containing objects for the queries

クエリ ストアは、クエリ エントリと親オブジェクト (ストアド プロシージャ、関数、トリガー) を関連付けます。Query Store associates query entry with a containing object (stored procedure, function, and trigger). 親オブジェクトを再作成すると、同じクエリ テキストに対して新しいクエリ エントリが生成されます。When you recreate a containing object, a new query entry will be generated for the same query text. これにより、クエリのパフォーマンス統計情報を継続して追跡できなくなるので、プランの強制適用メカニズムを使用することになります。This will prevent you from tracking performance statistics for that query over time and use plan forcing mechanism. これを回避するには、可能な限り ALTER <object> プロセスを使用して親オブジェクトの定義を変更します。To avoid this, use the ALTER <object> process to change a containing object definition whenever it is possible.

強制適用されたプランの状態を定期的に確認するCheck the status of Forced Plans regularly

プランの強制適用は、重要なクエリのパフォーマンスを修正してより正確な予測を可能にするための便利なメカニズムです。Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. ただし、プラン ヒントやプラン ガイドと同様に、強制的に適用されたプランがその後の実行でも確実に使用されるとは限りません。However, as with plan hints and plan guides, forcing a plan is not a guarantee that it will be used in future executions. 通常、実行プランによって参照されるオブジェクトが変更または削除されたことによってデータベース スキーマが変更された場合、プランを強制的に適用できなくます。Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing will start failing. その場合、SQL ServerSQL Server はクエリの再コンパイルに戻りますが、強制適用が失敗した実際の理由は sys.query_store_plan に示されます。In that case SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. 次のクエリは、強制適用されたプランに関する情報を返します。The following query returns information about forced plans:

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」を参照してください。For full list of reasons, refer to sys.query_store_plan. query_store_plan_forcing_failed XEvent を使用して、プラン強制の失敗を追跡してトラブルシューティングすることもできます。You can also use the query_store_plan_forcing_failed XEvent to track and troubleshoot plan forcing failures.

強制適用されたプランを持つクエリがある場合はデータベースの名前を変更しないAvoid renaming databases if you have queries with Forced Plans

実行プランは、3 つの要素で構成される名前 (database.schema.object) を使用してオブジェクトを参照します。Execution plans reference objects using three-part names database.schema.object.

データベース名を変更すると、プランの強制適用が失敗し、その後のすべてのクエリ実行で再コンパイルが発生します。If you rename a database, plan forcing will fail which will cause recompilation in all subsequent query executions.

ミッション クリティカルなサーバーでトレース フラグを使用するUse trace flags on mission critical servers

グローバル トレース フラグ 7745 と 7752 を使用すると、クエリ ストアを使用するデータベースの可用性を向上させることができます。The global trace flags 7745 and 7752 can be used to improve availability of databases using Query Store. 詳しくは、トレース フラグに関する記事をご覧ください。For more information, refer to Trace Flags.

  • トレース フラグ 7745 では、SQL ServerSQL Server がシャットダウンされる前に、クエリ ストアによってディスクにデータを書き込む既定の動作が行われないようにします。Trace flag 7745 will prevent the default behavior where Query Store writes data to disk before SQL ServerSQL Server can be shut down. つまり、収集されただけでディスクにまだ保存されていないクエリ ストア データは失われます。This means that Query Store data that has been collected but not been yet persisted to disk will be lost.

  • トレース フラグ 7752 では、クエリ ストアの非同期読み込みが有効になります。Trace flag 7752 enables asynchronous load of Query Store. これにより、データベースをオンラインにすることができ、クエリ ストアが完全に復旧される前にクエリを実行できます。This allows a database to become online and queries to be executed before the Query Store has been fully recovered. 既定の動作では、クエリ ストアの同期読み込みが行われます。The default behavior is to do a synchronous load of Query Store. 既定の動作では、クエリ ストアが復旧される前にクエリを実行することはできませんが、コレクション内でクエリが失われることもありません。The default behavior prevents queries from executing before the Query Store has been recovered but also prevents any queries from being missed in the data collection.

重要

SQL Server 2016 (13.x)SQL Server 2016 (13.x) の Just In Time ワークロード分析情報のためにクエリ ストアを使用している場合は、KB 4340759 におけるパフォーマンスのスケーラビリティの修正を、できるだけ早くインストールするよう計画します。If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

参照See Also

クエリ ストアのカタログ ビュー (Transact-SQL) Query Store Catalog Views (Transact-SQL)
クエリ ストアのストアド プロシージャ (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
インメモリ OLTP でのクエリ ストアの使用 Using the Query Store with In-Memory OLTP
クエリのストアを使用した、パフォーマンスの監視 Monitoring Performance By Using the Query Store
クエリ処理アーキテクチャ ガイドQuery Processing Architecture Guide