クエリ ストアでの最適化されたプラン強制
適用対象: SQL Server 2022 (16.x)
クエリ最適化は、"十分な" クエリ実行プランを生成する複数フェーズのプロセスです。 場合によっては、クエリ最適化の一部であるクエリ コンパイルは、クエリ実行時間全体の大部分を表し、大量のシステム リソースを消費することがあります。 最適化されたプラン強制は、 インテリジェントなクエリ処理機能ファミリの一部です。 プランの強制を最適化すると、強制クエリを繰り返す際のコンパイル オーバーヘッドが削減され、クエリ ストアを有効にして "読み取り書き込み" モードにする必要があります。 クエリ実行プランが生成されると、最適化再生スクリプトとして再利用するために特定のコンパイル手順が格納されます。 最適化再生スクリプトは、圧縮されたプラン表示 XML の一部としてクエリ ストアの非表示 OptimizationReplay
属性に保管されます。
最適化されたプラン強制の実装
クエリで最初にコンパイル プロセスが実行されるときに、(クエリ オプティマイザー入力ツリーに基づく) 最適化に費やされた時間の推定値に基づくしきい値によって、最適化再生スクリプトが作成されるかどうかが決定されます。
コンパイルの完了後に、以前の推定値が正しかったかどうかを評価するために、いくつかのランタイム メトリックが使用できるようになります。 しきい値を超えていることが確認された場合、最適化再生スクリプトは永続化の対象となります。 これらのランタイム メトリックには、アクセスされたオブジェクトの数、結合の数、最適化中に実行された最適化タスクの数、および実際の最適化時間が含まれます。
最適化再生スクリプトを使用する場合の潜在的な利点も、最適化再生スクリプトを格納するオーバーヘッドと比較されます。 最適化再生スクリプトを再生する相対的な時間の推定値は、最適化再生スクリプトに格納されている最適化タスクの数と、通常のコンパイル時に実行された最適化タスクの数に基づいて、通常の最適化プロセスの実行に費やされた時間と比較されます。 最適化再生スクリプトを再生すると、コンパイル時間の短縮に大きな利点がある場合、最適化再生スクリプトは保持されます。
考慮事項
最適化されたプラン強制機能が有効になっている場合、最適化されたプラン強制の適正条件は次のとおりです。
- 完全な最適化を実行するクエリ プランのみが対象となります。これは、
StatementOptmLevel="FULL"
プロパティの有無で確認できます。 - 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)になります。
例 C のsp_query_store_force_plan ストアド プロシージャに適切なパラメーターを適用する例を見つけます。
sys.query_store_plan
カタログ ビューには、プランに最適化再生スクリプトが関連付けられているかどうかを示す列が含まれており、関連する最適化再生スクリプトに固有の既存のエラー理由列に新しい状態が追加されます。 詳細については、「sys.query_store_plan (Transact-SQL)」を参照してください。
例
A. データベースに対してクエリ ストアと最適化されたプラン強制を有効にする
次のコードでは、データベースでクエリ ストアを有効にしてから、そのデータベースに対して最適化されたプラン強制を有効にします。 クエリ ストアを有効にするオプションの詳細については、「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。
コードを実行する前に、適切なユーザー データベースに接続します。
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 番目のパラメーター値として渡されることを想定しています。 これを使用すると、特定のレプリカで特定の強制プランに対する最適化されたプランの強制を無効にすることができます。 値 1 - @replica_group_id=1
はプライマリ レプリカの機能を無効にするために使用されます。
EXEC 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 (Transact-SQL)」を参照してください。
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
クエリ ヒントを使用して、クエリに対して最適化されたプラン強制を無効にします。 この例では、AdventureWorks サンプル データベースを使用します。
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
次のステップ
クエリ ストアと最適化されたプラン強制の詳細については、次の記事を参照してください。