次の方法で共有

クエリ ストアでの最適化されたプラン強制

  • [アーティクル]

適用対象: SQL Server 2022 (16.x) Azure SQL Database

クエリ最適化は、"十分な" クエリ実行プランを生成する複数フェーズのプロセスです。 場合によっては、クエリ最適化の一部であるクエリ コンパイルは、クエリ実行時間全体の大部分を表し、大量のシステム リソースを消費することがあります。 最適化されたプラン強制は、 インテリジェントなクエリ処理機能ファミリの一部です。 プランの強制を最適化すると、強制クエリを繰り返す際のコンパイル オーバーヘッドが削減され、クエリ ストアを有効にして "読み取り書き込み" モードにする必要があります。 クエリ実行プランが生成されると、最適化再生スクリプトとして再利用するために特定のコンパイル手順が格納されます。 最適化再生スクリプトは、圧縮されたプラン表示 XML の一部としてクエリ ストアの非表示 OptimizationReplay 属性に保管されます。

最適化されたプラン強制の実装

クエリが最初にコンパイル プロセスを通過するとき、最適化に費やされた時間の見積もりに基づくしきい値 (クエリ オプティマイザーの入力ツリーに基づく) によって、最適化再生スクリプトが作成されるかどうかが決まります。

コンパイルの完了後に、以前の推定値が正しかったかどうかを評価するために、いくつかのランタイム メトリックが使用できるようになります。 データベース エンジンがしきい値を超えたと確認した場合、最適化再生スクリプトは永続化の対象となります。 これらのランタイム メトリックには、アクセスされたオブジェクトの数、結合の数、最適化中に実行された最適化タスクの数、および実際の最適化時間が含まれます。

最適化再生スクリプトを使用する場合の潜在的な利点も、最適化再生スクリプトを格納するオーバーヘッドと比較されます。 最適化再生スクリプトを再生する相対時間の推定は、通常の最適化プロセスの実行に費やされた時間と比較されます。 この見積もりは、最適化再生スクリプトに格納されている最適化タスクの数と、通常のコンパイル中に実行される最適化タスクの数に基づいています。 最適化再生スクリプトを再生すると、コンパイル時間の短縮に大きな利点がある場合、最適化再生スクリプトは保持されます。

考慮事項

最適化されたプラン強制機能が有効になっている場合、最適化されたプラン強制の適正条件は次のとおりです。

  1. 完全な最適化を実行するクエリ プランのみが対象となります。これは、StatementOptmLevel="FULL" プロパティの有無で確認できます。

  2. RECOMPILE ヒントと分散クエリを含むステートメントは使用できません。

ただし、最適化されたプラン強制によってスコープ指定されたクエリ プランをクエリ ストアが個別にキャプチャする場合、最適化再生スクリプトは、既定の再コンパイル イベントに従って、同じクエリの 2 回目の再コンパイル用に作成されます。 再コンパイルの詳細については、「実行プランの再コンパイル」を参照してください。

最適化再生スクリプトが生成された場合でも、構成されたクエリ ストアキャプチャ ポリシーの条件が満たされていない場合は、クエリ ストアに保持されない可能性があります。特に、そのステートメントの実行数と、コンパイルと実行の累積時間です。 この場合、無効な最適化再生スクリプトがメモリから非同期的に削除されます。

最適化されたプラン強制を有効または無効にする

データベースに対して最適化されたプラン強制を有効または無効にすることができます。 最適化されたプラン強制がデータベースに対して有効になっている場合は、クエリ ヒントを使用して個々のクエリに DISABLE_OPTIMIZED_PLAN_FORCING 対して無効にすることができます。 クエリ ストアで強制されるクエリ プランに対する最適化されたプラン強制を無効にすることもできます。

データベースに対して最適化されたプラン強制を有効または無効にする

最適化されたプラン強制は、SQL Server 2022 (16.x) 以降で作成された新しいデータベースに対して既定で有効になります。 最適化されたプラン強制が使用されるすべてのデータベースに対して、クエリ ストアを有効にする必要があります。 既存のデータベースを含むアップグレードされたインスタンス、または下位バージョンの SQL Server から復元されたデータベースでは、最適化されたプラン強制が既定で有効になっています。

データベース レベルで最適化されたプラン強制を有効にするには、ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON データベース スコープの構成を使用します。 クエリ ストアがまだ有効になっていない場合は、有効にする必要があります。 「例 A」でコード例を見つけるか、「クエリ ストアを使用したパフォーマンスの監視」でクエリ ストアの詳細を確認してください。

データベース レベルで最適化されたプラン強制を無効にするには、ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF データベース スコープの構成を使用します。

クエリ ヒントを使用して最適化されたプラン強制を無効にする

最適化されたプラン強制機能がデータベースで有効になっている場合は、DISABLE_OPTIMIZED_PLAN_FORCING クエリ ヒントを使用して、個々のクエリに対して最適化されたプラン強制を無効にすることができます。

「例 E」で、このクエリ ヒントを適用する例を見つけてください。

クエリ ストアを使用してプランを強制するが、最適化されたプラン強制を無効にする

sp_query_store_force_plan プロシージャにはdisable_optimized_plan_forcingパラメーターが含まれています。 このパラメーターを使用するには、ストアド プロシージャに追加のパラメーターが sp_query_store_force_plan 必要です。 追加のパラメーターが呼び出されます @replica_group_id。 既定では、セカンダリ レプリカが構成されていない場合でも、プライマリ @replica_group_id の値は 1 です1。

例 C のストアド プロシージャに適切なパラメーターを適用するsp_query_store_force_plan例を見つけます。

sys.query_store_plan カタログ ビューには、プランに最適化再生スクリプトが関連付けられているかどうかを示す列が含まれており、関連する最適化再生スクリプトに固有の既存のエラー理由列に新しい状態が追加されます。 詳細については、sys.query_store_planを参照してください。

例

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

A. データベースに対してクエリ ストアと最適化されたプラン強制を有効にする

次のコードでは、データベースでクエリ ストアを有効にしてから、そのデータベースに対して最適化されたプラン強制を有効にします。 ALTER DATABASE SET オプションでクエリ ストアを有効にするオプションについて説明します。

コードを実行する前に、適切なユーザー データベースに接続します。

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. 最適化再生スクリプトがあるすべてのクエリを選択する

次のコード例では、クエリ ストアに最適化再生スクリプトがあるすべての query_ids を選択します。 サンプル コードを実行する前に、適切なユーザー データベースに接続します。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C: クエリ ストアでプランを強制し、最適化されたプランの強制を無効にする

次のコードでは、クエリ ストアでプランを強制しますが、最適化されたプラン強制は無効にします。 次のコードを実行する前に、@query_id と @plan_id をインスタンスに適した組み合わせに置き換えます。 ストアド プロシージャはsp_query_store_force_plan、@replica_group_idクエリ ストアで最適化されたプランの強制を無効にしようとしたときに、パラメーターが 3 番目のパラメーター値として渡されることを想定しています。 これを使用すると、特定のレプリカで特定の強制プランに対する最適化されたプランの強制を無効にすることができます。 値 @replica_group_id = 1 は、プライマリ レプリカの機能を無効にするために使用されます。

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

詳細については、sp_query_store_force_planを参照してください。

D. クエリ ストアによって最適化されたプラン強制が無効になっている場合はすべてのクエリを選択する

次の例では、設定されているクエリ ストアis_optimized_plan_forcing_disabledで強制されたすべてのプランに対してクエリを実行します1。 コードを実行する前に、適切なユーザー データベースに接続します。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. クエリに対して最適化されたプラン強制を無効にする

次の例では、DISABLE_OPTIMIZED_PLAN_FORCING クエリ ヒントを使用して、クエリに対して最適化されたプラン強制を無効にします。

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

関連するコンテンツ