パラメーターに依存するプランの最適化
適用対象: 
SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、Azure SQL Managed Instance
パラメーター センシティビティ プラン (PSP) 最適化は、インテリジェントなクエリ処理機能 ファミリの一部です。 パラメーター化クエリに対してプランが 1 つキャッシュされているだけでは、受け取る可能性があるあらゆるパラメーター値に対して最適ではないシナリオに対処します。 これは一様でないデータ分散のケースです。 詳細については、「パラメーターの感度」と「パラメーターと実行プランの再利用」を参照してください。
この問題のシナリオの既存の回避策の詳細については、次を参照してください。
PSP 最適化を使用すると、パラメーター化された 1 つのステートメントに対して複数のアクティブなキャッシュされたプランが自動的に有効になります。 キャッシュされた実行プランは、顧客が指定したランタイム パラメーター値に基づいて、さまざまなデータ サイズに対応します。
パラメーター化について
SQL Server データベース エンジンでは、Transact-SQL (T-SQL) ステートメントでパラメーターまたはパラメーター マーカーを使用することで、新しい Transact-SQL ステートメントと既存のコンパイル済みの実行プランとを照合し、プランの再利用を促進するリレーショナル エンジンの機能が強化されています。 詳細については、「簡易パラメーター化」を参照してください。
データベースのすべての SELECT、INSERT、UPDATE、および DELETE ステートメントをパラメーター化するように指定することで、いくつかの制約はありますが SQL Server の簡易パラメーター化の既定動作をオーバーライドすることもできます。 詳細については、「強制パラメーター化」を参照してください。
PSP 最適化の実装
初回コンパイル時に、列統計ヒストグラムで一様でない分布が識別され、使用可能なすべての述語のうち、最も "リスクのある" パラメーター化された述語が最大 3 つまで評価されます。 つまり、同じクエリ内の複数の述語が条件を満たしている場合、PSP 最適化では上位 3 つが選択されます。 PSP 機能では、プラン キャッシュとクエリ ストア (クエリ ストアが有効な場合) が多すぎるプランの肥大化を回避するために、評価される述語の数が制限されます。
対象となるプランの場合、ディスパッチャー式と呼ばれる PSP 最適化ロジックを含むディスパッチャー プランが初回コンパイルで生成されます。 ディスパッチャー プランは、カーディナリティ範囲の境界値の述語に基づいて、クエリ バリアントにマップされます。
用語
ディスパッチャー式
ランタイム パラメーター値に基づいて述語のカーディナリティを評価し、実行をさまざまなクエリバリアントにルーティングします。
Dispatcher プラン
ディスパッチャー式を含むプランは、元のクエリ用にキャッシュされます。 ディスパッチャー プランは、基本的に、機能によって選択された述語のコレクションに、いくつかの追加の詳細を加えたものです。 選択された述語ごとに、ディスパッチャー計画に含まれる詳細の一部は、高および低境界値です。 これらの値は、パラメーター値を異なるバケットまたは範囲に分割するために使用されます。 ディスパッチャー プランには、境界値の計算に使用された統計も含まれています。
クエリバリアント
ディスパッチャー プランは、ランタイム パラメーター値に基づいて述語のカーディナリティを評価する際に、それらの値を分類し、コンパイルして実行する個別の子クエリを生成します。 これらの子クエリは、"クエリ バリアント" と呼ばれます。 クエリ バリアントには、プラン キャッシュとクエリ ストアに独自のプランがあります。
述語カーディナリティの範囲
実行時に、各述語のカーディナリティがランタイム パラメーター値に基づいて評価されます。 ディスパッチャーは、コンパイル時にカーディナリティ値を 3 つの述語カーディナリティ範囲に分類します。 たとえば、PSP 最適化機能では、次の図に示すように、低、中、高のカーディナリティ範囲を表す 3 つの範囲を作成できます。
つまり、パラメーター化されたクエリが最初にコンパイルされると、PSP 最適化機能によってディスパッチャー プランと呼ばれるシェル プランが生成されます。 ディスパッチャー式には、パラメーターのランタイム値に基づいてクエリ バリアントにクエリを分類するロジックがあります。 実際の実行が開始されると、ディスパッチャーは次の 2 つの手順を実行します。
ディスパッチャーは、指定されたパラメーター セットのディスパッチャー式を評価してカーディナリティ範囲を計算します。
ディスパッチャーは、これらの範囲を特定のクエリバリアントにマップし、バリアントをコンパイルして実行します。 複数のクエリバリアントを持つことにより、PSP 最適化機能は、1 つのクエリに対して複数のプランを持つことを実現します。
カーディナリティ範囲の境界は、ディスパッチ プランの ShowPlan XML 内で確認できます。
<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>
PSP 最適化によって生成されたヒントは、クエリバリアントの ShowPlan XML の SQL ステートメントに追加されます。 ヒントを直接使用することはできません。手動で追加した場合は解析 されません 。 ヒントには、次の要素が含まれます。
オプション ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[schemaName]。[tableName]。[columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) )
- ObjectID は、現在のステートメントが含まれているモジュール (つまり、ストアド プロシージャ、関数、トリガー) から取得されます。これは、ステートメントがモジュールから生成されたことを前提とします。 ステートメントが動的 SQL またはアドホック SQL (つまり、
sp_executesql) の結果である場合、ObjectID 要素は等しくなります0。 - QueryVariantID は、PSP 最適化が選択したすべての述語の範囲の組み合わせとほぼ同じです。 たとえば、クエリに PSP の対象となる 2 つの述語があり、各述語に 3 つの範囲がある場合、9 個のクエリバリアント範囲の番号は 1 から 9 になります。
- 述語範囲 は、ディスパッチャー式から生成される述語カーディナリティ範囲情報です。
また、クエリバリアントの ShowPlan XML 内 (Dispatcher 要素内)は以下です:
<Batch>
<Statements>
<StmtSimple StatementText="SELECT PropertyId,
 AgentId,
 MLSLinkId,
 ListingPrice,
 ZipCode,
 Bedrooms,
 Bathrooms
FROM dbo.Property
WHERE AgentId = @AgentId
ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<Dispatcher>
<ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
<StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
<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>
</StmtSimple>
</Statements>
</Batch>
解説
現在、PSP 最適化機能は等価述語でのみ機能します。
データ分散が大幅に変更された場合、ディスパッチャー プランは自動的に再構築されます。 クエリ バリアント プランは、他の種類のクエリ プランと同じように、必要に応じて個別に再コンパイルされ、既定の再コンパイル イベントが適用されます。 再コンパイルの詳細については、「実行プランの再コンパイル」を参照してください。
通常のコンパイル済みプラン、ディスパッチャー プラン、およびクエリバリアント プランを区別するために、 sys.query_store_plan (Transact-SQL) クエリ ストア システム カタログ ビューが変更されました。 新しいクエリ ストア システム カタログ ビュー (sys.query_store_query_variant (Transact-SQL)) には、元のパラメーター化されたクエリ (親クエリとも呼ばれます)、ディスパッチャー プラン、およびその子クエリ バリアント間の親子リレーションシップに関する情報が含まれています。
同じテーブルの一部である述語が複数ある場合、PSP 最適化では、基になる統計ヒストグラムに基づいて、データ スキューが最も多い述語が選択されます。 たとえば、
SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2で、両方のcolumn1 = @predicate1とcolumn2 = @predicate2が同じテーブルからのものであり、table1は、最も歪んだ述語のみが特徴によって評価されます。 ただし、クエリ例に演算子 (例:UNION、ひとつ) が含まれている場合、PSP は複数の述語を評価します。 たとえば、クエリに類似した特性がある場合、SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicatePSP は、このシナリオを 2 つの異なるテーブルであるかのように扱うため、このケースでは最大 2 つの述語を選択します。 テーブルのエイリアスを使用して自己結合するクエリでも、同じ動作を確認できます。クエリバリアントの ShowPlan XML は次の例のようになります。選択された両方の述語には、それぞれの情報が
PLAN PER VALUE PSP-関連 ヒントに追加されています。<Batch> <Statements> <StmtSimple StatementText="SELECT b.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty a join PropertyDetails b on a.PropertyId = b.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id
UNION
 SELECT c.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty c join PropertyDetails d on c.PropertyId = d.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <Dispatcher> <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06"> <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" /> <Predicate> <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@AgentId" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </ParameterSensitivePredicate> <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06"> <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" /> <Predicate> <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@AgentId" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </ParameterSensitivePredicate> </Dispatcher> <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">PSP 最適化機能で使用される現在のスキューしきい値に影響を与えることができます。次の方法は 1 つ以上です。
カーディナリティ推定 (CE) トレース フラグ (トレース フラグ 9481 など) (グローバル、セッション、またはクエリ レベル)
使用中の CE モデルを下げようとする、または複数の述語の独立に関して CE モデルが行う前提に影響を与える、データベース スコープの構成 オプション。 これは、複数列の統計が存在しない場合に特に役立ちます。これは、PSP 最適化がこれらの述語の立候補性を評価する能力に影響します。
詳細については、「SQL Server 2014 カーディナリティ推定機能を使用したクエリ プランの最適化」ホワイト ペーパーの「複数述語の相関の増加の前提条件」セクションを参照してください。 新しい CE モデルでは、述語の結合と結合に対して、ある程度の相関関係とより低い独立性を想定しようとします。 従来の CE モデルを使用すると、複数列結合シナリオでの述語の選択度を計算する方法に影響を与える可能性があります。 このアクションは特定のシナリオでのみ考慮する必要があり、ほとんどのワークロードでレガシ CE モデルを使用することはおすすめしません。
PSP 最適化では、現在、各クエリ バリアントが新しい準備済みステートメントとしてコンパイルおよび実行されています。これは、
object_idディスパッチャー プランがモジュール (ストアド プロシージャ、トリガー、関数、ビューなど) に基づいている場合に、クエリ バリアントが親モジュールとの関連付けを失う理由の 1 つです。 準備されたステートメントとして、object_idは、sys.objectsのオブジェクトに直接マップできるものではありませんが、基本的にはバッチ テキストの内部ハッシュに基づく計算値です。 詳細については、sys.dm_exec_plan_attributesDMV ドキュメントの 「返される テーブル」セクションを 参照してください。クエリ バリアント プランはプラン キャッシュ オブジェクト ストア (
CACHESTORE_OBJCP) に配置され、ディスパッチャー プランは SQL Plans キャッシュ ストア (CACHESTORE_SQLCP) に配置されます。 ただし、PSP 機能は、クエリ バリアントのobject_id親を ObjectID 属性内に格納 します。これは、親クエリが動的またはアドホック T-SQL ではなく、モジュールの一部である場合に PSP が ShowPlan XML に追加する PLAN PER VALUE ヒントの一部です。 キャッシュされたプロシージャ、関数、トリガーの集計パフォーマンス統計は、それぞれの目的で引き続き使用できます。sys.dm_exec_query_statsDMV のようなビューで見つかるようなより詳細な実行関連の統計には、クエリ バリアントのデータがまだ含まれていますが、object_idテーブル内の クエリ バリアントのsys.objectsとオブジェクトの関連付けは現在一致しません。ただし、より詳細なランタイム統計が必要な各クエリ バリアントに対して ShowPlan XML を追加で処理する必要はありません。 クエリ ストアが有効になっている場合、クエリ バリアントのランタイムと待機の統計情報は、追加の ShowPlan XML 解析手法なしでクエリ ストアから取得できます。PSP クエリ バリアントは新しい準備済みステートメントとして実行されるため、
object_idは、ShowPlan XML を細分化してテキスト パターン マッチング手法 (つまり、追加の XQuery 処理) を適用することなく、プラン キャッシュ関連sys.dm_exec_*のさまざまな DMV に自動的に公開されることはありません。 現在、適切な親オブジェクト ID を出力するのは、PSP 最適化ディスパッチャー プランだけです。object_idはクエリ ストアではプラン キャッシュ階層が提供するよりも多くのリレーショナル モデルが許可されるため、クエリ ストア内で公開されます。 詳細については、クエリ ストア のシステム カタログ ビュー sys.query_store_query_variant (Transact-SQL) を参照してください。
考慮事項
PSP 最適化を有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 160 を有効にしてください。
PSP 最適化機能の追加の分析情報を得るには、クエリ ストア統合も有効にすることをおすすめします。それにはクエリ ストアを確実にオンにしてください。 次の例では、以下のように
MyNewDatabaseと呼ばれる既存のデータベースに対してクエリ ストアを有効にします。
ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO
);
Note
クエリ ストアは、SQL Server 2022 (16.x) 以降の時点で、新しく作成されたデータベースに対して既定で有効になっています。
データベース レベルで PSP 最適化を無効にするには、
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFFデータベース スコープ構成を使用します。クエリ レベルで PSP 最適化を無効にするには、
DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATIONクエリ ヒントを使用します。トレース フラグ 4136、
PARAMETER_SNIFFINGデータベース スコープ構成、またはUSE HINT('DISABLE_PARAMETER_SNIFFING')クエリ ヒントによってパラメーター スニッフィングが無効になっている場合、関連付けられているワークロードと実行コンテキストに対して PSP 最適化が無効になります。 詳細については、「ヒント (Transact-SQL) - Query」と「ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)」を参照してください。ディスパッチャーごとにプラン キャッシュ内に保存される一意のプラン バリアント数は、キャッシュの肥大化を回避するために制限されています。 内部しきい値は文書化されていません。 各 SQL バッチには複数のプランを作成する可能性があり、各クエリ バリアント プランにはプラン キャッシュに独立したエントリがあるため、許可されるプラン エントリの既定の最大数に達する可能性があります。 プラン キャッシュの削除率が著しく高い場合、または
CACHESTORE_OBJCPとCACHESTORE_SQLCPキャッシュ ストアのサイズが過剰な場合は、トレース フラグ 174 の適用を検討する必要があります。クエリのためにクエリ ストア内に保存される一意のプラン バリアント数は、
max_plans_per_query構成オプションによって制限されています。 クエリ バリアントには複数のプランを含めることができるので、クエリ ストア内のクエリごとに合計 200 個のプランを使用できます。 この数値には、親クエリに属するすべてのディスパッチャーのすべてのクエリ バリアント プランが含まれます。max_plans_per_queryクエリ ストアの構成オプションを増やすことを検討してください。- 一意のプランの数が既定のクエリ ストア
max_plans_per_queryの制限を超える可能性がある例は、次の動作が発生するシナリオです。 クエリ ID が 10 のクエリがあり、ディスパッチャー プランが 2 つあり、各ディスパッチャー プランにそれぞれ 20 個のクエリ バリアント (合計で 40 個のクエリ バリアント) があるとします。 クエリ ID 10 のプランの合計数は、クエリ バリアントと 2 つのディスパッチャー プランの 40 プランです。 また、親クエリ自体 (クエリ ID 10) に 5 つの標準 (ディスパッチャー以外) プランを含めることもできます。 これにより、47 個のプラン (クエリ バリアントから 40 個、ディスパッチャー 2 個、PSP 以外のプランが 5 つ) になります。 さらに、各クエリ バリアントに平均 5 つのプランがある場合、このシナリオでは、親クエリのクエリ ストアに 200 を超えるプランを含めることもできます。 これは、この例の親クエリが参照している可能性があるデータセットのデータ スキューの大きい値にも依存します。
- 一意のプランの数が既定のクエリ ストア
特定のディスパッチャーへのクエリ バリアント マッピングごとに、次の手順を実行します。
query_plan_hashは一意です。 この列は、sys.dm_exec_query_statsと、その他の動的管理ビューおよびカタログ テーブルで使用できます。plan_handleは一意です。 この列は、sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plansと、その他の動的管理ビューと関数およびカタログ テーブルで使用できます。query_hashは、同じディスパッチャーにマップされる他のバリアントに共通であるため、入力パラメーター値のみが異なるクエリを合計したリソース使用率を特定できます。 この列は、sys.dm_exec_query_stats、sys.query_store_queryと、その他の動的管理ビューおよびカタログ テーブルで使用できます。- コンパイル時に特別な PSP 最適化識別子がクエリ テキストに追加されるため、
sql_handleは一意です。 この列は、sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plansと、その他の動的管理ビューと関数およびカタログ テーブルで使用できます。sys.query_store_queryカタログ テーブルのlast_compile_batch_sql_handle列と同じハンドル情報をクエリ ストアで使用できます。 query_idは、クエリ ストア内で一意です。 この列は、sys.query_store_queryと、その他のクエリ ストア カタログ テーブルで使用できます。
クエリ ストアにおけるプラン強制
同じ sp_query_store_force_plan および sp_query_store_unforce_plan ストアド プロシージャを使用して、ディスパッチャーまたはバリアント プランを操作します。
バリアントが強制されている場合、親ディスパッチャーは強制されません。 ディスパッチャーが強制されている場合、そのディスパッチャーのバリアントのみが使用に適していると見なされます。
- 他のディスパッチャーから以前に強制されたバリアントは非アクティブになりますが、ディスパッチャーが再度強制されるまで、"強制" 状態を保持します
- 非アクティブになっていたのと同じディスパッチャーで以前に強制されたバリアントが、再度強制されます
クエリ ストアのクエリ ヒントの動作
クエリ ストア ヒントがクエリ バリアント (子クエリ) に追加されると、ヒントは PSP 以外のクエリと同じ方法で適用されます。 クエリ ストアの親クエリにもヒントが適用されている場合、クエリ バリアント ヒントの優先順位が高くなります。
クエリ ストア ヒントが親クエリに追加され、子クエリ (クエリバリアント) に既存のクエリ ストア ヒントがない場合、子クエリ (クエリバリアント) は親クエリからヒントを継承します。
クエリ ストアのクエリ ヒントが親クエリから削除された場合、子クエリ (クエリ バリアント) にもヒントが削除されます。
RECOMPILEヒントが親クエリに追加された場合、PSP 機能はRECOMPILEヒントを持つクエリに対して動作しないため、既存のクエリバリアント プランがプラン キャッシュから削除された後に、システムによって PSP 以外のプランが生成されます。クエリ ストア ヒントの結果は、拡張イベント
query_store_hints_application_successとquery_store_hints_application_failedイベントを使用して確認できます。 sys.query_store_query_hints テーブルには、適用されたクエリ ヒントに関する情報が含まれています。 ヒントが親クエリにのみ適用されている場合、システム カタログには親クエリのヒント情報が含まれますが、子クエリは親クエリのヒントを継承しますが、子クエリのヒントは含まれません。
クエリ ヒントとプラン強制動作を含む PSP を次の表にまとめることができます。
| クエリ バリアント ヒントまたはプラン | 親にユーザーが適用したヒントがある | 親にフィードバックが適用されたヒントがある | 親が手動で計画を強制している | 親に APC 1 強制プランがある |
|---|---|---|---|---|
| ユーザーによるヒント | クエリバリアントヒント | クエリバリアントヒント | クエリバリアントヒント | 該当なし |
| フィードバックによるヒント | クエリバリアントヒント | クエリバリアントヒント | クエリバリアントヒント | 該当なし |
| ユーザーによる強制的な計画 | クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
| APC によって強制される計画 | クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
クエリバリアント 強制プラン |
| ヒントまたは強制プランなし | 親ユーザーのヒント | ヒントなし | アクションなし | アクションなし |
1 自動チューニング機能の自動プラン修正コンポーネント
拡張イベント
parameter_sensitive_plan_optimization_skipped_reason: パラメーター センシティビティ プラン機能がスキップされたときに発生します。 このイベントを使用して、PSP の最適化がスキップされる理由を監視します。次のクエリは、PSP がスキップされた可能性のあるすべての理由を示しています。
SELECT map_value FROM sys.dm_xe_map_values WHERE [name] ='psp_skipped_reason_enum' ORDER BY map_key;parameter_sensitive_plan_optimization: クエリで PSP 最適化機能が使用されている場合に発生します。 デバッグ チャネルのみ。 関心のあるフィールドには、次の項目があります。- is_query_variant: ディスパッチャー プラン (親) またはクエリバリアント プラン (子) のどちらであるかについて説明します
- predicate_count: PSP によって選択された述語の数
- query_variant_id: クエリバリアント ID が表示されます。 値 0 は、オブジェクトがディスパッチャー プラン (親) であることを意味します。
SQL Server 監査のビヘイビアー
PSP の最適化では、ディスパッチャー プラン ステートメントと、ディスパッチャーに関連付けられているクエリバリアントの監査データが提供されます。 SQL Server 監査内の additional_information 列には、クエリバリアントに対する適切な T-SQL スタック情報も用意されています。 MyNewDatabase データベースを例として使用すると、このデータベースに T2 と呼ばれるテーブルが呼び出され、usp_test の名前がストアド プロシージャである場合、usp_test ストアド プロシージャの実行後に、監査ログに次のエントリが含まれる場合があります。
| action_id | object_name | statement | additional_information |
|---|---|---|---|
| AUSC | <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info> |
||
| EX | usp_test | exec usp_test 300 | |
| SL | T2 | select * from dbo.t2 where ID=@id | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| SL | T2 | select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo]。[T2]。[ID] = @id, 100.0, 100000.0)) | tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| EX | usp_test | exec usp_test 60000 | |
| SL | T2 | select * from dbo.t2 where ID=@id | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| SL | T2 | select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo]。[T2]。[ID] = @id, 100.0, 100000.0)) | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
既知の問題
| 問題 | 検出した日 | Status | 解決した日 |
|---|---|---|---|
| 「特定の条件下で SQL Server 2022 (16.x) のクエリ ストアで発生するアクセス違反の例外」。 PSP 最適化クエリ ストア統合が有効になっていると、アクセス違反の例外が発生する可能性があります。 詳細については、「パラメーターに依存するプランの最適化」の最新情報を参照してください。 | 2023 年 3 月 | 解決済み | 2023 年 8 月(CU 7) |
解決済み
「特定の条件下で SQL Server 2022 のクエリ ストアで発生するアクセス違反の例外」
Note
SQL Server 2022 (16.x) 累積的な更新プログラム 7 以降では、アクセス違反につながる可能性がある競合状態に関するいくつかの修正プログラムがリリースされました。 SQL Server 2022 (16.x) の累積的な更新プログラム 7 を適用した後に、クエリ ストア統合での PSP の最適化に関連するアクセス違反が発生する場合は、次の回避策のセクションを検討してください。
この問題は、実行されたクエリの実行時統計がクエリ ストアのメモリ内表現 (MEMORYCLERK_QUERYDISKSTORE_HASHMAP メモリ クラークにある) からディスク上のバージョンのクエリ ストアに保持されている場合に発生する可能性がある競合状態が原因で発生します。 ランタイム統計情報として示されるランタイム統計は、SET QUERY_STORE ステートメントの DATA_FLUSH_INTERVAL_SECONDS オプションで定義された一定期間、メモリ内に保持されます。（既定値は15分です）。 Management Studio クエリ ストア ダイアログ ボックスを使用して、 [データ フラッシュ間隔 (分)] の値を入力できます。これは、内部的に秒に変換されます。 メモリ不足が発生した場合、DATA_FLUSH_INTERVAL_SECONDS オプションで定義されている時間より前に、ランタイム統計がディスクにフラッシュされる可能性があります。 クエリ ストアのクエリ プランのクリーンアップ (つまり、 STALE_QUERY_THRESHOLD_DAYS および/または MAX_STORAGE_SIZE_MB クエリ ストア のオプション) に関連する追加のクエリ ストアのバックグラウンド スレッドがクエリ ストアからのクエリである場合、クエリバリアントおよび/またはその関連するディスパッチャー ステートメントが途中で逆参照される可能性があるシナリオがあります。 これにより、クエリ ストアへのクエリ バリアントの挿入または削除操作中にアクセス違反が発生する可能性があります。
クエリ ストアの操作の詳細については、「クエリ ストアでデータを収集する方法」の「 解説 」セクションを参照してください。
回避策: SQL Server 2022 (16.x) 用の累積的な更新プログラム 7 を適用した後、PSP 統合が有効になっているクエリ ストアでアクセス違反がシステムで発生している場合は、追加の修正プログラムが利用可能になるまで、クエリ ストア内にあるクエリ バリアントを削除するか、PSP 機能をクエリまたはデータベース レベルで一時的に無効にすることができます。
- データベース レベルで PSP 最適化を無効にするには、
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFFデータベース スコープ構成を使用します。 - クエリ レベルで PSP 最適化を無効にするには、
DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATIONクエリ ヒントを使用します。
sys.query_store_query_variant (Transact-SQL) カタログ ビューに表示されるクエリ バリアントだけでなく、クエリ ストアからすべてのクエリ バリアントを削除するには、次のようなクエリを使用できます。 問題が発生していた適切なデータベースに [<database>] を置き換えます。
USE master;
GO
--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO
USE [<database>];
GO
DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
-- Getting the cursor for query IDs for query variant plans
SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
SELECT query_id
FROM sys.query_store_plan
WHERE plan_type = 2 --query variant plans
ORDER BY query_id;
-- Using a non-set based method for this example query
OPEN @QueryIDsCursor
FETCH NEXT FROM @QueryIDsCursor
INTO @QueryID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Deleting query variant(s) from the Query Store
EXEC sp_query_store_remove_query @query_id = @QueryID;
FETCH NEXT FROM @QueryIDsCursor
INTO @QueryID
END;
CLOSE @QueryIDsCursor ;
DEALLOCATE @QueryIDsCursor;
END;
--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO
クエリ ストアが大きい場合や、システムに相当量のワークロードがある場合、またはクエリ ストアによるキャプチャの対象となるアドホックの非パラメーター化クエリがシステムに多数ある場合、クエリ ストアをオフにするまでに時間がかかる場合があります。 これらのシナリオでクエリ ストアを強制的にオフにするには、前のサンプル T-SQL で代わりに ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) コマンドを使用します。 パラメーター化されていないクエリを検索するには、「クエリ ストアでパラメーター化されていないクエリを検索する」を参照してください。