クエリのストアを使用した、パフォーマンスの監視Monitoring performance by using the Query Store

このトピックに適用されますはいSQL ServerはいAzure SQL DatabaseありませんAzure SQL Data Warehouseなし。並列データ ウェアハウスTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server のクエリのストア機能により、クエリ プランの選択やパフォーマンスを把握できます。The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. これにより、クエリ プランの変更によって生じるパフォーマンスの違いがすばやくわかるようになり、パフォーマンス上のトラブルシューティングを簡略化できます。It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. クエリのストアは、自動的にクエリ、プラン、および実行時統計の履歴をキャプチャし、確認用に保持します。Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. データは時間枠で区分されるため、データベースの使用パターンを表示して、サーバー上でクエリ プランが変わった時点を確認することができます。It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. ALTER DATABASE SET オプションを使用してクエリ ストアを構成できます。You can configure query store using the ALTER DATABASE SET option.

Azure SQL データベースSQL Database におけるクエリ ストアの運用について詳しくは、「Azure SQL Database でクエリ ストアを運用する」をご覧ください。For information about operating the Query Store in Azure SQL データベースSQL Database, see Operating the Query Store in Azure SQL Database.

クエリのストアを有効にするEnabling the Query Store

既定では、クエリのストアは新しいデータベースに対してアクティブではありません。Query Store is not active for new databases by default.

SQL Server Management StudioSQL Server Management Studio の [クエリ ストア] ページを使うUse the Query Store Page in SQL Server Management StudioSQL Server Management Studio

  1. オブジェクト エクスプローラーで、データベースを右クリックし、 [プロパティ] をクリックします。In Object Explorer, right-click a database, and then click Properties.

    注意

    Management StudioManagement Studio のバージョン 16 以降が必要です。Requires at least version 16 of Management StudioManagement Studio.

  2. [データベースのプロパティ] ダイアログ ボックスで、 [クエリのストア] ページをクリックします。In the Database Properties dialog box, select the Query Store page.

  3. [操作モード (要求)] ボックスで、 [オン] を選択します。In the Operation Mode (Requested) box, select On.

Transact-SQL ステートメントを使用するUse Transact-SQL Statements

ALTER DATABASE ステートメントを使用してクエリのストアを有効にします。Use the ALTER DATABASE statement to enable the query store. 例 :For example:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;  

クエリ ストアに関連する構文オプションの詳細については、「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。For more syntax options related to the query store, see ALTER DATABASE SET Options (Transact-SQL).

注意

マスター データベースまたは tempdb データベースに対しては、クエリ ストアを有効にできません。You cannot enable the query store for the master or tempdb database.

クエリのストア内の情報Information in the Query Store

SQL ServerSQL Server のどの特定のクエリの実行プランも、通常、統計情報やスキーマの変更、インデックスの作成または削除などのさまざまな理由により、時間の経過とともに進化します。プロシージャ キャッシュ (ここにキャッシュされたクエリ プランが格納される) には、最新の実行プランのみ格納されます。Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. メモリ負荷が原因で、プランがプラン キャッシュから削除されることもあります。Plans also get evicted from the plan cache due to memory pressure. その結果、実行プランの変更によるクエリ パフォーマンスの低下が深刻なレベルになり、解決に時間を要する場合があります。As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

クエリのストアには、1 つのクエリにつき複数の実行プランが保持されるため、クエリの特定の実行プランを使用するようクエリ プロセッサに指示するポリシーを強制できます。Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query. これをプラン強制と呼びます。This is referred to as plan forcing. クエリのストアのプラン強制は、 USE PLAN クエリ ヒントに似たメカニズムを使用して提供されますが、ユーザー アプリケーションを変更する必要はありません。Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. プラン強制を使用することで、プラン変更によるクエリ パフォーマンスの低下をきわめて短時間に解決できます。Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

待機統計は、SQL Server のパフォーマンスのトラブルシューティングに役立つもう 1 つの情報源です。Wait stats are another source of information that helps to troubleshoot performance in SQL Server. 長い間、待機統計はインスタンス レベルでしか使うことができず、実際のクエリにバックトラックするのは困難でした。For a long time, wait statistics were available only on instance level, which made it hard to backtrack it to the actual query. SQL Server 2017 と Azure SQL Database では、待機統計を追跡する別のディメンションがクエリ ストアに追加されました。In SQL Server 2017 and Azure SQL Database we added another dimension in Query Store that tracks wait stats.

このクエリのストアの機能を使用する一般的なシナリオは次のとおりです。Common scenarios for using the Query Store feature are:

  • 前のクエリ プランを強制的に適用することにより、プラン パフォーマンスの低下をすばやく発見し修正します。Quickly find and fix a plan performance regression by forcing the previous query plan. 実行プランの変更によって最近パフォーマンスが低下したクエリを修正します。Fix queries that have recently regressed in performance due to execution plan changes.
  • 特定の時間枠内にクエリが実行された回数を確認し、パフォーマンス リソースの問題に関するトラブルシューティングにおいて DBA を支援します。Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • 上位 n クエリ (過去 x 時間内) を、実行時間やメモリ消費量などを基に識別します。Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • 指定したクエリのクエリ プランの履歴を監査します。Audit the history of query plans for a given query.
  • 特定のデータベースのリソース (CPU、I/O、メモリ) の使用パターンを分析します。Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
  • リソースで待機している上位 n クエリを識別します。Identify top n queries that are waiting on resources.
  • 特定のクエリまたはプランの待機の性質を理解します。Understand wait nature for a particular query or plan.

クエリ ストアには 3 つのストアが含まれます。The query store contains three stores:

  • プラン ストアは、実行プラン情報の保存用です。a plan store for persisting the execution plan information.
  • ランタイム統計ストアは、実行統計情報の保存用ですa runtime stats store for persisting the execution statistics information.
  • 待機統計ストアは、待機統計情報の保存用ですa wait stats store for persisting wait statistics information.

クエリのためにプラン ストア内に格納できる一意のプラン数は、 max_plans_per_query 構成オプションによって制限されています。The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. パフォーマンスを向上させるために、この情報はストアに非同期的に書き込まれます。To enhance performance, the information is written to the stores asynchronously. 領域使用量を最小にするため、ランタイム統計情報ストアのランタイム実行統計情報は、一定の時間枠で集計されます。To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. これらのストア内の情報は、クエリのストアのカタログ ビューに対してクエリを実行することによって表示できます。The information in these stores is visible by querying the query store catalog views.

次のクエリは、クエリのストア内のクエリとプランに関する情報を返します。The following query returns information about queries and plans in the query store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*  
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 ;  

機能低下したクエリ機能を使用するUse the Regressed Queries Feature

クエリのストアを有効にしてから、[オブジェクト エクスプローラー] ペインのデータベースの部分を更新して、 [クエリ ストア] セクションを追加します。After enabling the query store, refresh the database portion of the Object Explorer pane to add the Query Store section.

オブジェクト エクスプローラーのクエリ ストア ツリーQuery store tree in Object Explorer

[機能低下したクエリ] を選択し、 [機能低下したクエリ] Management StudioManagement Studioペインを開きます。Select Regressed Queries to open the Regressed Queries pane in Management StudioManagement Studio. [機能低下したクエリ] ペインにクエリと、クエリのストア内のプランが表示されます。The Regressed Queries pane shows you the queries and plans in the query store. 上部にあるドロップダウン ボックスを使用して、さまざまな条件に合わせてクエリを選択します。Use the drop down boxes at the top to select queries based on various criteria. プランを選択して、グラフィカルなクエリ プランを表示します。Select a plan to see the graphical query plan. ソース クエリの表示、クエリ プランの強制と強制解除、表示の更新に使用できるボタンが用意されています。Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

オブジェクト エクスプローラーの機能低下したクエリRegressed queries in object explorer

プランを強制的に適用するには、クエリとプランを選択してから、 [プランの強制] をクリックします。To force a plan, select a query and plan, and then click Force Plan. 強制できるプランは、クエリ プランの機能によって保存され、クエリ プランのキャッシュに保持されているプランのみです。You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

待機クエリの検索Finding wait queries

SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 2.0 および SQL データベースSQL Database 以降では、クエリごとの時系列の待機統計情報をクエリ ストアで使用できます。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CTP 2.0 and SQL データベースSQL Database, wait statistics per query over time are available in Query Store. クエリ ストアでは、待機の種類が待機カテゴリに組み合わされます。In Query Store, wait types are combined into wait categories. 待機カテゴリから待機の種類へのマッピングは、sys.query_store_wait_stats (Transact-SQL) で使用できます。The mapping of wait categories to wait types is available in sys.query_store_wait_stats (Transact-SQL).

待機カテゴリでは、異なる待機種類が性質の類似性によってバケットに組み合わされます。Wait categories are combining different wait types into buckets similar by nature. 問題の解決に必要なフォローアップ分析は待機カテゴリによって異なりますが、同じカテゴリの待機種類からは非常によく似たトラブルシューティング エクスペリエンスが得られ、待機の先頭に影響受けたクエリを提供することは、このような調査の大部分を正常に完了するために不足している部分です。Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

クエリ ストアに待機カテゴリが導入される前後でのワークロードの詳細情報の取得方法の例を次に示します。Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

以前のエクスペリエンスPrevious experience 新しいエクスペリエンスNew experience 操作Action
データベースごとの高い RESOURCE_SEMAPHORE 待機High RESOURCE_SEMAPHORE waits per database 特定のクエリに対するクエリ ストアでの高いメモリ待機High Memory waits in Query Store for specific queries クエリ ストアでメモリ消費量の多いクエリを探します。Find the top memory consuming queries in Query Store. これらのクエリは、影響を受けるクエリの進行をさらに遅らせる可能性があります。These queries are probably delaying further progress of the affected queries. これらのクエリまたは影響を受けるクエリに、MAX_GRANT_PERCENT クエリ ヒントを使うことを検討します。Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
データベースごとの高い LCK_M_X 待機High LCK_M_X waits per database 特定のクエリに対するクエリ ストアでの高いロック待機High Lock waits in Query Store for specific queries 影響を受けるクエリのクエリ テキストを確認し、ターゲット エンティティを明らかにします。Check the query texts for the affected queries and identify the target entities. クエリ ストアで同じエンティティを変更している他のクエリを探します。これらは、頻繁に実行されていたり、実行時間が長くなったりします。Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. これらのクエリを特定した後、同時実行が向上するようにアプリケーション ロジックを変更するか、制限の低い分離レベルを使うことを検討します。After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
データベースごとの高い PAGEIOLATCH_SH 待機High PAGEIOLATCH_SH waits per database 特定のクエリに対するクエリ ストアでの高いバッファー IO 待機High Buffer IO waits in Query Store for specific queries クエリ ストアで、物理読み取り数が多いクエリを検索します。Find the queries with a high number of physical reads in Query Store. それらが IO 待機の長いクエリと一致する場合は、スキャンではなくシークを行うように基になるエンティティにインデックスを導入して、クエリの IO オーバーヘッドを最小限に抑えることを検討します。If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
データベースごとの高い SOS_SCHEDULER_YIELD 待機High SOS_SCHEDULER_YIELD waits per database 特定のクエリに対するクエリ ストアでの高い CPU 待機High CPU waits in Query Store for specific queries クエリ ストアで CPU 消費量の多いクエリを探します。Find the top CPU consuming queries in Query Store. それらの中で、高い CPU 傾向が影響を受けるクエリの高い CPU 待機と関連性のあるクエリを特定します。Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. それらのクエリの最適化に注目します。プラン回帰または欠落インデックスが存在する可能性があります。Focus on optimizing those queries – there could be a plan regression, or perhaps a missing index.

構成オプションConfiguration Options

次のオプションは、クエリ ストア パラメーターの構成に使用できます。The following options are available to configure query store parameters.

OPERATION_MODEOPERATION_MODE
READ_WRITE (既定値) または READ_ONLY。Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
STALE_QUERY_THRESHOLD_DAYS 引数を構成して、クエリのストア内にデータを保持する日数を指定します。Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. 既定値は、30 です。The default value is 30. SQL データベースSQL Database Basic エディションの場合、既定の日数は 7 日です。For SQL データベースSQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
クエリに書き込まれるデータ ストアが永続化する頻度を決定をディスクにします。Determines the frequency at which data written to the query store is persisted to disk. パフォーマンスを最適化するには、クエリのストアで収集したデータ非同期的にディスクに書き込まれます。To optimize for performance, data collected by the query store is asynchronously written to the disk. この非同期転送が発生する頻度は DATA_FLUSH_INTERVAL_SECONDS で構成されています。The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. 既定値は 900 (15 分) です。The default value is 900 (15 min).

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
クエリのストアの最大サイズを構成します。Configures the maximum size of the query store. クエリのストア内のデータが MAX_STORAGE_SIZE_MB の上限に達すると、クエリのストアは自動的に状態を読み取り/書き込みから読み取り専用に変更し、新しいデータの収集を停止します。If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. 既定値は 100 MB です。The default value is 100 MB. SQL データベースSQL Database Premium Edition の既定値は 1 GBSQL データベースSQL Database Basic エディションの既定値は 10 MB です。For SQL データベースSQL Database Premium edition, default is 1 GB and for SQL データベースSQL Database Basic edition, default is 10 MB.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
クエリのストアにランタイムの実行の統計データを集計する時間間隔を決定します。Determines the time interval at which runtime execution statistics data is aggregated into the query store. 領域使用量を最適化するため、ランタイム統計情報ストアのランタイム実行統計情報は、一定の時間枠で集計されます。To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. この固定された時間枠は、INTERVAL_LENGTH_MINUTES 引数を介して構成されます。This fixed time window is configured via INTERVAL_LENGTH_MINUTES. 既定値は 60です。The default value is 60.

SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE
データの総量が最大サイズに近付いたときにクリーンアップ プロセスを自動的にアクティブにするかどうかを制御します。Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. AUTO (既定値) または OFF。Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE
クエリのストアが、すべてのクエリをキャプチャするか、実行数とリソース消費量に基づいて関連するクエリをキャプチャするか、または新しいクエリの追加を停止して現在のクエリのみを追跡するかを指定します。Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. ALL (すべてのクエリをキャプチャする)、AUTO (不定期で、不必要なコンパイルと実行期間を持つクエリを無視する) または NONE (新しいクエリのキャプチャを停止する)。Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) から SQL Server 2017SQL Server 2017) での既定値は ALL ですが、Azure SQL データベースSQL Database では AUTO です。The default value on SQL ServerSQL Server (from SQL Server 2016 (13.x)SQL Server 2016 (13.x) to SQL Server 2017SQL Server 2017) is ALL, while on Azure SQL データベースSQL Database is AUTO.

MAX_PLANS_PER_QUERYMAX_PLANS_PER_QUERY
各クエリに対して保持の計画の最大数を表す整数。An integer representing the maximum number of plans maintained for each query. 既定値は 200 です。The default value is 200.

WAIT_STATS_CAPTURE_MODEWAIT_STATS_CAPTURE_MODE
クエリ ストアが待機統計情報をキャプチャするかどうかを制御します。Controls if Query Store captures wait statistics information. OFF または ON (既定値) にすることができます。Can be OFF or ON (default).

sys.database_query_store_options ビューにクエリを実行し、クエリ ストアの現在のオプションを確認します。Query the sys.database_query_store_options view to determine the current options of the query store. 値に関する詳細については、「sys.database_query_store_options」を参照してください。For more information about the values, see sys.database_query_store_options.

Transact-SQLTransact-SQL ステートメントを使用してオプションを設定する方法の詳細については、「 オプション管理」をご覧ください。For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

クエリのストアは、 Management StudioManagement Studio か、次のビューとプロシージャを使用して表示および管理します。View and manage Query Storethrough Management StudioManagement Studio or by using the following views and procedures.

クエリ ストア関数Query Store Functions

関数は、クエリ ストアの操作に役立ちます。Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

クエリのストアのカタログ ビューQuery Store Catalog Views

カタログ ビューはクエリのストアの情報を提供します。Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings (Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query (Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval (Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

クエリのストアのストアド プロシージャQuery Store Stored Procedures

ストアド プロシージャはクエリのストアを構成します。Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats (Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan (Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query (Transact-SQL)sp_query_store_remove_query (Transact-SQL)

基本的な使用シナリオKey Usage Scenarios

オプション管理Option Management

このセクションでは、クエリのストアの機能自体を管理する方法に関するガイドラインを示します。This section provides some guidelines on managing Query Store feature itself.

クエリのストアが現在アクティブかIs Query Store currently active?

クエリのストアはユーザー データベース内にデータを格納するため、サイズに上限が設定されています (MAX_STORAGE_SIZE_MB で構成)。Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). クエリのストア内のデータがその上限に達すると、クエリのストアは自動的に状態を読み取り/書き込みから読み取り専用に変更し、新しいデータの収集を停止します。If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

sys.database_query_store_options のクエリを実行して、クエリのストアが現在アクティブであるか、また、ランタイム統計情報を現在収集しているかを確認します。Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

SELECT actual_state, actual_state_desc, readonly_reason,   
    current_storage_size_mb, max_storage_size_mb  
FROM sys.database_query_store_options;  

クエリのストアの状態は、actual_state 列によって決定されます。Query Store status is determined by actual_state column. 目的の状態と異なる場合は、readonly_reason 列で詳しい情報が得られます。If it’s different than the desired status, the readonly_reason column can give you more information.
クエリのストアのサイズがクォータを超える場合、この機能は readon_only モードに切り替わります。When Query Store size exceeds the quota, the feature will switch to readon_only mode.

クエリのストアのオプションを取得するGet Query Store options

クエリのストアの状態に関する詳細情報については、ユーザー データベースで次を実行します。To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

クエリのストアの時間間隔を設定するSetting Query Store interval

クエリのランタイム統計情報を集計する時間間隔 (既定では 60 分) を上書きできます。You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>   
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);  

注意

INTERVAL_LENGTH_MINUTES に任意の値を使用することはできません。Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. 1、5、10、15、30、60、または 1440 分のいずれかを使用します。Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

間隔の新しい値は、 sys.database_query_store_options ビューで明らかになります。New value for interval is exposed through sys.database_query_store_options view.

クエリのストアの使用領域Query Store space usage

現在のクエリのストアのサイズと制限をチェックするには、ユーザー データベースで次のステートメントを実行します。To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb   
FROM sys.database_query_store_options;  

クエリのストアの記憶域がいっぱいの場合は、次のステートメントを使用して記憶域を拡張します。If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>   
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);  

クエリのストアのオプションをすべて設定するSet all Query Store options

単一の ALTER DATABASE ステートメントで、クエリのストアの複数のオプションを一度にまとめて設定できます。You can set multiple Query Store options at once with a single ALTER DATABASE statement.

ALTER DATABASE <database name>   
SET QUERY_STORE (  
    OPERATION_MODE = READ_WRITE,  
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),  
    DATA_FLUSH_INTERVAL_SECONDS = 3000,  
    MAX_STORAGE_SIZE_MB = 500,  
    INTERVAL_LENGTH_MINUTES = 15,  
    SIZE_BASED_CLEANUP_MODE = AUTO,  
    QUERY_CAPTURE_MODE = AUTO,  
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON 
);  

領域のクリーンアップCleaning up the space

クエリのストアの内部テーブルは、データベースの作成時に PRIMARY ファイル グループに作成され、その構成を後で変更することはできません。Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. 領域が不足している場合は、次のステートメントを使用して、古いクエリのストアのデータを消去できます。If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;  

または、アドホック クエリ データはクエリの最適化やプラン分析との関連性が低く、ただ場所を占有するだけなので、アドホック クエリ データのみを削除することもできます。Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

アドホック クエリの削除 : この操作は、24 時間以上前に 1 回実行しただけのクエリを削除します。Delete ad-hoc queries This deletes the queries that were only executed only once and that are more than 24 hours old.

DECLARE @id int  
DECLARE adhoc_queries_cursor CURSOR   
FOR   
SELECT q.query_id  
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan AS p   
    ON p.query_id = q.query_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  

OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @id  
        EXEC sp_query_store_remove_query @id  
        FETCH NEXT FROM adhoc_queries_cursor INTO @id  
    END   
CLOSE adhoc_queries_cursor ;  
DEALLOCATE adhoc_queries_cursor;  

不要になったデータを消去する別のロジックを利用した独自のプロシージャを定義できます。You can define your own procedure with different logic for clearing up data you no longer want.

上記の例では、 sp_query_store_remove_query 拡張ストアド プロシージャを使用して不要なデータを削除しています。The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. 次のプロシージャを使用することもできます。You can also use:

  • sp_query_store_reset_exec_stats。指定されたプランの実行時統計をクリアします。sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
  • sp_query_store_remove_plan。1 つのプランを削除します。sp_query_store_remove_plan to remove a single plan.

パフォーマンスの監査とトラブルシューティングPerformance Auditing and Troubleshooting

クエリのストアには、コンパイルの履歴とクエリの実行全体に関するランタイム メトリックスが保持されており、ワークロードに関連した質問の答えを見つけることができます。Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

データベースで最近実行された n 個のクエリ。Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,   
    qt.query_text_id, p.plan_id, rs.last_execution_time  
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 p.plan_id = rs.plan_id  
ORDER BY rs.last_execution_time DESC;  

各クエリの実行回数。Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
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 p.plan_id = rs.plan_id  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

去&1; 時間で平均実行時間が長かったクエリの上位。The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    rs.last_execution_time   
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 p.plan_id = rs.plan_id  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

過去 24 時間で平均物理 IO 読み取り数が多かったクエリの上位と、対応する平均行数および実行カウント。The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
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 p.plan_id = rs.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, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

複数のプランを持つクエリ。Queries with multiple plans? これらは、プラン選択の変更による機能低下の原因になりうるため、特に興味深いクエリです。These queries are especially interesting because they are candidates for regressions due to plan choice change. 次のクエリは、これらのクエリとすべてのプランを識別します。The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, q.query_id   
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 p.query_id = q.query_id  
GROUP BY q.query_id  
HAVING COUNT(distinct plan_id) > 1  
)  

SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans AS qm  
JOIN sys.query_store_query AS q  
    ON qm.query_id = q.query_id  
JOIN sys.query_store_plan AS p  
    ON q.query_id = p.query_id  
JOIN sys.query_store_query_text qt   
    ON qt.query_text_id = q.query_text_id  
ORDER BY query_id, plan_id;  

最近パフォーマンスが低下したクエリ (別の時点との比較)。Queries that recently regressed in performance (comparing different point in time)? 次のクエリの例では、プラン選択の変更により、過去 48 時間で実行時間が 2 倍になったすべてのクエリを返します。The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. 次のクエリは、すべてのランタイム統計情報の時間間隔を並べて比較します。Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
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 p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

プラン選択の変更に関連するものだけでなく、パフォーマンス低下に関するすべての情報を表示する場合、前のクエリから条件 AND p1.plan_id <> p2.plan_id を削除します。If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

最も待機時間の長いクエリ。Queries that are waiting the most? このクエリは、待機が最も多い上位 10 個のクエリを返します。This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

最近パフォーマンスが低下したクエリ (最近の実行と履歴の実行を比較)。Queries that recently regressed in performance (comparing recent vs. history execution)? 次のクエリは、実行期間に基づいてクエリの実行を比較します。The next query compares query execution based periods of execution. この例では、クエリは、最近の期間 (1 時間) と履歴の期間 (過去&1; 日間) とで実行を比較し、 additional_duration_workloadの原因となったものを識別します。In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. このメトリックは、最近の平均実行と履歴の平均実行に最近実行の数を掛けた値の間の差として計算されます。This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. これは、履歴と比較して、最近の実行でどれほどの期間が追加されたかを表します。It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  

--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  

WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time \<= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time \<= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time \<= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time \<= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

クエリ パフォーマンスの安定性を維持するMaintaining Query Performance Stability

複数回実行されるクエリでは、 SQL ServerSQL Server が異なるプランを使用した結果、リソースの使用の仕方や期間が異なっていることに気付く場合があります。For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. クエリのストアを使用すると、クエリ パフォーマンスが低下している時点を検出し、対象期間の最適なプランを特定できます。With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. こうすることで、将来のクエリの実行で最適なプランを強制的に適用できます。You can then force that optimal plan for future query execution.

パラメーターを持つクエリ (自動的にパラメーター化されたもの、または手動でパラメーター化されたもののいずれか) に関して、クエリ パフォーマンスが一定ではないものを特定することもできます。You can also identify inconsistent query performance for a query with parameters (either auto- parameterized or manually parameterized). さまざまなプランの中で、ほとんどすべてのパラメーター値に対して高速で最適なプランを特定し、そのプランを強制的に適用できます。これにより、より一層多様なユーザー シナリオに対して、予測可能なパフォーマンスを維持できます。Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

クエリに対してプランを強制する (強制ポリシーの適用)。Force or a plan for a query (apply forcing policy). 特定のクエリに対して つのプランを強制すると、クエリが実行されるたびに、強制されているプランが使われて実行されます。When a plan is forced for a certain query, every time a query comes to execution it will be executed with the plan that is forced.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

sp_query_store_force_plan を使用する場合は、クエリ ストアによってそのクエリのプランとして記録されたプランのみを強制できます。When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. つまり、クエリで使用できるプランは、クエリのストアがアクティブであったときにそのクエリを実行するために既に使用されているプランのみです。In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

クエリに対するプランの強制を削除する。Remove plan forcing for a query. もう一度 SQL ServerSQL Server クエリ オプティマイザーを利用して最適なクエリ プランを計算するには、クエリに対して選択されていたプランの強制を sp_query_store_unforce_plan を使用して解除します。To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

参照See Also

クエリ ストアを使用する際の推奨事項 Best Practice with the Query Store
インメモリ OLTP でのクエリ ストアの使用 Using the Query Store with In-Memory OLTP
クエリ ストアの使用シナリオ Query Store Usage Scenarios
クエリ ストアがデータを収集するしくみ How Query Store Collects Data
クエリ ストアのストアド プロシージャ (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
クエリ ストアのカタログ ビュー (Transact-SQL) Query Store Catalog Views (Transact-SQL)
パフォーマンスの監視とチューニング Monitor and Tune for Performance
パフォーマンス監視およびチューニング ツール Performance Monitoring and Tuning Tools
利用状況モニターを開く方法 (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
ライブ クエリ統計 Live Query Statistics
利用状況モニター Activity Monitor
sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL)
Azure SQL Database でクエリ ストアを運用するOperating the Query Store in Azure SQL Database