パラメーター センシティビティ プラン最適化

適用対象: SQL Server (SQL Server 2022 (16.x) Preview 以降)

パラメーター センシティビティ プラン (PSP) 最適化は、インテリジェントなクエリ処理機能ファミリの一部です。 パラメーター化クエリに対してプランが 1 つキャッシュされているだけでは、入ってくる可能性があるあらゆるパラメーター値に最適ではないシナリオに対処します。 これは一様でないデータ分散のケースです。 詳細については、「パラメーターの感度」と「パラメーターと実行プランの再利用」を参照してください。

この問題のシナリオに対する既存の回避策の詳細については、「パラメーター依存プラン (PSP) の問題が発生しているクエリ」を参照してください。

PSP 最適化を使用すると、パラメーター化された 1 つのステートメントに対して複数のアクティブなキャッシュされたプランが自動的に有効になります。 キャッシュされた実行プランは、顧客が指定したランタイム パラメーター値に基づいて、さまざまなデータ サイズに対応します。

パラメーター化について

SQL Server データベース エンジンでは、Transact-SQL ステートメントでパラメーターまたはパラメーター マーカーを使用することで、新しい Transact-SQL ステートメントと既存のコンパイル済みの実行プランとを照合し、プランの再利用を促進するリレーショナル エンジンの機能が強化されています。 詳細については、「簡易パラメーター化」を参照してください。

データベースのすべての SELECTINSERTUPDATE、および DELETE ステートメントをパラメーター化するように指定することで、いくつかの制約はありますが SQL Server の簡易パラメーター化の既定動作をオーバーライドすることもできます。 詳細については、「強制パラメーター化」を参照してください。

PSP 最適化の実装

初回コンパイル時に、列統計ヒストグラムで一様でない分布が識別され、使用可能なすべての述語のうち、最も "リスクのある" パラメーター化された述語が最大 3 つまで評価されます。

対象となるプランの場合、ディスパッチャー式と呼ばれる PSP 最適化ロジックを含むディスパッチャー プランが初回コンパイルで生成されます。 ディスパッチャー プランは、カーディナリティ範囲の境界値の述語に基づいて、クエリ バリアントにマップされます。

それらは、選択された述語ごとに、ランタイム カーディナリティに基づいてクエリ プロセッサで述語カーディナリティ範囲にバケット化されます。次の図を参照してください。

Graphic of P S P boundaries.

データ分散が大幅に変更された場合、ディスパッチャー プランは自動的に再構築されます。 クエリ バリアント プランは、他の種類のクエリ プランと同じように、必要に応じて個別に再コンパイルされ、既定の再コンパイル イベントが適用されます。 再コンパイルの詳細については、「実行プランの再コンパイル」を参照してください。

特定のディスパッチャーへのクエリ バリアント マッピングごとに、次の手順を実行します。

  • query_plan_hash は一意です。 この列は、sys.dm_exec_query_stats と、その他の動的管理ビューおよびカタログ テーブルで使用できます。
  • plan_handle は一意です。 この列は、sys.dm_exec_query_statssys.dm_exec_sql_textsys.dm_exec_cached_plans と、その他の動的管理ビューと関数およびカタログ テーブルで使用できます。
  • query_hash は、同じディスパッチャーにマップされる他のバリアントに共通であるため、入力パラメーター値のみが異なるクエリを合計したリソース使用率を特定できます。 この列は、sys.dm_exec_query_statssys.query_store_query と、その他の動的管理ビューおよびカタログ テーブルで使用できます。
  • コンパイル時に特別な PSP 最適化識別子がクエリ テキストに追加されるため、sql_handle は一意です。 この列は、sys.dm_exec_query_statssys.dm_exec_sql_textsys.dm_exec_cached_plans と、その他の動的管理ビューと関数およびカタログ テーブルで使用できます。 sys.query_store_query カタログ テーブルの last_compile_batch_sql_handle 列と同じハンドル情報をクエリ ストアで使用できます。
  • query_id は、クエリ ストア内で一意です。 この列は、sys.query_store_query と、その他のクエリ ストア カタログ テーブルで使用できます。

考慮事項

PSP 最適化を有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 160 を有効にします。

PSP 最適化機能の追加の分析情報を得るには、クエリ ストア統合も有効にすることをお勧めします。それにはクエリ ストアを確実にオンにしてください。 次の例では、my_psp_database というデータベースに対してクエリ ストアを有効にします。

ALTER DATABASE my_psp_database SET QUERY_STORE (QUERY_CAPTURE_MODE = auto);

データベース レベルで PSP 最適化を無効にするには、ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF データベース スコープ構成を使用します。

  • Dispatcher 要素によって、統計ヒストグラムから導き出された述語境界の詳細が示されます。
<Dispatcher>
 <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
   <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
   <Predicate>
  <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
    <Compare CompareOp="EQ">
   <ScalarOperator>
     <Identifier>
    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
     </Identifier>
   </ScalarOperator>
   <ScalarOperator>
     <Identifier>
    <ColumnReference Column="@AgentId" />
     </Identifier>
   </ScalarOperator>
    </Compare>
  </ScalarOperator>
   </Predicate>
 </ParameterSensitivePredicate>
</Dispatcher>
  • クエリ バリアント識別のための QueryPlan 要素の QueryVariantID 属性。
<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="224" QueryVariantID="1">

クエリ レベルで PSP 最適化を無効にするには、DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION クエリ ヒントを使用します。

トレース フラグ 4136、PARAMETER_SNIFFING データベース スコープ構成、または USE HINT('DISABLE_PARAMETER_SNIFFING') クエリ ヒントによってパラメーター スニッフィングが無効になっている場合、関連付けられているワークロードと実行コンテキストに対して PSP 最適化が無効になります。 詳細については、「ヒント (Transact-SQL) - Query」と「ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)」を参照してください。

ディスパッチャーごとにプラン キャッシュ内に保存される一意のプラン バリアント数は、キャッシュの肥大化を回避するために制限されています。 内部しきい値は文書化されていません。

クエリのためにクエリ ストア内に保存される一意のプラン バリアント数は、max_plans_per_query 構成オプションによって制限されています。

拡張イベント

この機能では、次の XE を使用できます。

  • parameter_sensitive_plan_optimization_skipped_reason: パラメーター センシティビティ プラン機能がスキップされたときに発生します。 このイベントを使用して、パラメーターに依存するプランの最適化がスキップされる理由を監視します。
  • parameter_sensitive_plan_optimization: クエリでパラメーター依存プラン (PSP) 最適化機能が使用されている場合に発生します。 デバッグ チャネルのみ。
  • parameter_sensitive_plan_testing: パラメーターに依存するプランがテストされたときに発生します。 デバッグ チャネルのみ。

クエリ ストアにおけるプラン強制

同じ sp_query_store_force_plan および sp_query_store_unforce_plan ストアド プロシージャを使用して、ディスパッチャーまたはバリアント プランを操作します。

バリアントが強制されている場合、親ディスパッチャーは強制されません。 ディスパッチャーが強制されている場合、そのディスパッチャーのバリアントのみが使用に適していると見なされます。

  • 他のディスパッチャーから以前に強制されたバリアントは非アクティブになりますが、ディスパッチャーが再度強制されるまで、"強制" 状態を保持します
  • 非アクティブになっていたのと同じディスパッチャーで以前に強制されたバリアントが、再度強制されます

既知の問題

今後の CTP リリースで対処される現時点での既知の問題を次に示します。

  • dbo 以外のスキーマで作成されたストアド プロシージャがあり、そのストアド プロシージャ内に、スキーマのないテーブルを参照するクエリがある場合、エラー 208 が発生する可能性があります。
-- This fails
CREATE OR ALTER PROCEDURE my_schema.my_proc @var1 int
AS
SELECT c2 FROM my_table WHERE c1 = @var1;

-- This works
CREATE OR ALTER PROCEDURE my_schema.my_proc @var1 int
AS
SELECT c2 FROM my_schema.my_table WHERE c1 = @var1;

関連項目