カーディナリティ推定 (CE) のフィードバック

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

SQL Server 2022 (16.x) 以降、カーディナリティ推定 (CE) フィードバックは、インテリジェント クエリ処理機能ファミリの一部です。これは、これらの問題が不適切な CE モデルの前提条件に起因している場合に、クエリを繰り返す最適でないクエリ実行プランに対処するためのものです。 このシナリオは、古いバージョンのデータベース エンジンからアップグレードするときに、既定の CE に関連する回帰リスクを軽減するのに役立ちます。

単一セットの CE モデルと前提条件で膨大なワークロードとデータ分散に対応することはできないため、CE フィードバックによって、クエリ ランタイムの特性に基づいた、適応可能なソリューションが提供されます。 CE フィードバックでは、クエリ実行プランの品質を向上させるために、特定のクエリとデータ分散に適合するモデルの前提条件が特定され、使用されます。 現在、CE フィードバックでは、推定行数と実際の行数が大きく異なるプラン演算子を特定できます。 フィードバックは、重要なモデル推定エラーが発生し、実行可能な代替モデルがある場合に適用されます。

その他のクエリ フィードバック機能については、「メモリ許可フィードバック」と「並列処理の次数 (DOP) のフィードバック」を参照してください。

カーディナリティ推定 (CE) フィードバックについて

カーディナリティ推定 (CE) は、クエリ プランの各レベルで処理される行の合計数を見積もるためにクエリ オプティマイザーで使用される方法です。 SQL Server でのカーディナリティ推定は、インデックスまたは統計を作成するときに手動か自動で作成されたヒストグラムから主に取得されます。 また、SQL Server では、クエリの制約情報および論理再書き込みを使用して、カーディナリティが決定されることもあります。

データベース エンジンの各バージョンでは、データの分散方法とクエリ方法に基づいて、異なる CE モデルの前提条件が使用されます。 詳細については、「CE のバージョン」を参照してください。

カーディナリティ推定 (CE) フィードバックの実装

カーディナリティ推定 (CE) フィードバックでは、どの CE モデル前提条件が最適であるかが時間の経過に応じて学習され、履歴から見て最も正しい前提条件が適用されます。

1. CE フィードバックによって、モデル関連の前提条件が識別され、それらがクエリの繰り返しに対して正確であるかどうかが評価されます。

2. 前提条件が正しくないと思われる場合は、影響を受ける CE モデル前提条件を調整し、それが役立つかどうかを確認するクエリ プランを使用して、同じクエリの後続の実行がテストされます。 プラン演算子の実際の行と推定される行を調べることで、誤りを特定します。 CE フィードバックで利用できるモデルバリアントによって、すべてのエラーを修正できるわけではありません。

3. プランの品質が向上した場合、古いクエリ プランは、推定モデルを調整する適切な USE HINT クエリ ヒント (クエリ ストア ヒント メカニズムを通じて実装されます) を使用するクエリ プランに置き換えられます。

検証済みのフィードバックのみが保持されます。 調整されたモデル前提条件によってパフォーマンスの低下が生じる場合、そのクエリには CE フィードバックは使用されません。 このコンテキストでは、ユーザーが取り消したクエリも回帰として認識されます。

カーディナリティ推定 (CE) フィードバックのシナリオ

カーディナリティ推定 (CE) フィードバックは、既定の CE (CE120 以上) を使用する場合に、不適切な CE モデル前提条件によって発生する回帰の問題に対処するものです。これにより、異なるモデル前提条件を選択的に使用できます。 シナリオには、相関関係、結合の包含、行の目標最適化が含まれます。

カーディナリティ推定 (CE) フィードバックの相関関係

クエリ オプティマイザーでは、特定のテーブルやビューに対する述語の選択性、またはその述語を満たす行数を見積もる際に、相関モデルの前提条件が使用されます。 これらの前提条件では、次の述語が考えられます。

• 完全に独立 (CE70 の既定値)。カーディナリティは、すべての述語の選択性を乗算して計算されます。

• 部分的に相関 (CE120 以降の既定値)。カーディナリティは指数バックオフの変動を使用して計算されます。選択性は、最も選択的な述語から最も選択的でない述語への順で並べ替えられます。

• 完全に相関。カーディナリティは、すべての述語に対して最小の選択性を使用してが計算されます。

次の例では、データベースの互換性が 120 以上に設定されている場合に、部分的相関が使用されます。

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

データベースの互換性が 160 に設定されていて、既定の相関関係が使用されている場合、CE フィードバックでは、推定カーディナリティが実際の行数と比較して過小評価されたか過大評価されたかに基づいて、相関を一度に 1 ステップずつ正しい方向に移動することが試行されます。 実際の行数が推定カーディナリティを超える場合は、完全相関を使用します。 実際の行数が推定カーディナリティよりも小さい場合は、完全独立を使用します。

詳細については、「CE のバージョン」を参照してください。

カーディナリティ推定 (CE) フィードバックの結合含有

クエリ オプティマイザーでは、結合述語と適用可能なフィルター述語の選択性を推定する際、包含モデルの前提条件が使用されます。 前提条件は次のとおりです。

• 単純包含 (CE70 の既定値)。結合述語が完全に相関していることが前提とされます。最初にフィルターの選択性が計算され、その後結合の選択性が考慮されます。

• 基本包含 (CE120 以上の既定値)。結合述語とダウンストリーム フィルターの間に相関関係がないことが前提とされます。最初に結合の選択性が計算され、その後フィルターの選択性が考慮されます。

次の例では、データベースの互換性が 120 以上に設定されている場合に、基本包含が使用されます。

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

詳細については、「CE のバージョン」を参照してください。

カーディナリティ推定 (CE) フィードバックとクエリ オプティマイザーの行目標

クエリ オプティマイザーで実行プランのカーディナリティが推定される際、通常は、すべてのテーブルのすべての修飾行を処理する必要があると想定されます。 ただし、一部のクエリ パターンでは、I/O を削減するために、より少ない数の行を返すプランがクエリ オプティマイザーによって検索されます。 クエリで、実行時に予期される行数 (行目標) が、TOP、IN、または EXISTS キーワード、FAST クエリ ヒント、または SET ROWCOUNT ステートメントを使用して指定された場合は、その行目標が、次の例のようにクエリ最適化プロセスの一部として使用されます。

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

行目標プランが適用された場合、クエリ オプティマイザーでは、行目標を達成するために処理する必要がある行の数が少なくなると想定されるため、クエリ プラン内の推定行数が減ります。

行目標は特定のクエリ パターンについては有益な最適化戦略ですが、データが均一に分散されていない場合は、予測よりも多くのページがスキャンされる可能性もあります。つまり、行目標が非効率なものとなります。 CE フィードバックでは、この非効率性が検出されたときに行目標スキャンを無効にし、シークを有効にすることができます。

実行プランには CE フィードバックに固有の属性はありませんが、クエリ ストア ヒントの属性が一覧表示されます。 QueryStoreStatementHintSourceをCE feedbackであるとして探します。

カーディナリティ推定 (CE) フィードバックに関する考慮事項

• カーディナリティ推定 (CE) フィードバックを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 160 を有効にします。 CE フィードバックが使用されるすべてのデータベースに対して、READ_WRITE モードでクエリ ストアを有効にする必要があります。

• データベース レベルで CE フィードバックを無効にするには、 CE_FEEDBACKデータベース スコープ構成を使用します。 たとえば、ユーザー データベースで次のコマンドを実行します。

  ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
  

• クエリ レベルで CE フィードバックを無効にするには、DISABLE_CE_FEEDBACK クエリ ヒントを使用します。

CE フィードバック アクティビティは、query_feedback_analysis および query_feedback_validation XEvent を介して表示できます。

CE フィードバックによって設定されたヒントは、sys.query_store_query_hints カタログ ビューを使用して追跡できます。

フィードバック情報は、 sys.query_store_plan_feedback カタログ ビューを使用して追跡できます。

クエリ ストアを通じて強制されたクエリ プランがクエリに含まれている場合、そのクエリに CE フィードバックを使用することはできません。

クエリで、ハードコーディングされたクエリ ヒントが使用されている場合や、ユーザーによって設定されたクエリ ストア ヒントが使用されている場合、そのクエリに CE フィードバックを使用することはできません。 詳細については、「ヒント (Transact-SQL) - Query」と「クエリ ストア ヒント」を参照してください。

SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になっている場合、CE フィードバックは可用性グループ内のセカンダリ レプリカに対してレプリカに対応していません。 現在、CE フィードバックはプライマリ レプリカにのみメリットがあります。 フェールオーバー時に、プライマリ レプリカまたはセカンダリ レプリカに適用されたフィードバックは失われます。 詳細については、「セカンダリ レプリカ のクエリ ストア」を参照してください。

カーディナリティ推定 (CE) フィードバックの永続化

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

カーディナリティ推定 (CE) フィードバックでは、行目標の最適化を永続化する必要があるシナリオを検出し、クエリ ストア ヒントの形式でクエリ ストアに保持することで、この変更を保持できます。 新しい最適化は、クエリの今後の実行に使用されます。 CE フィードバックは、フィードバック シナリオで詳しく説明しているように、行目標の最適化クエリ パターン以外の他のシナリオを保持します。 CE フィードバックは現在、CE の相関モデルで使用される述語選択度シナリオと、CE の包含モデルで処理される結合述語シナリオを処理します。

この機能は SQL Server 2022 (16.x) で導入されましたが、このパフォーマンスの向上は、データベース互換レベル 160 以上、または QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ヒント 160 以上で動作するクエリ、およびクエリ ストアがデータベースに対して有効になっており、"読み取り書き込み" 状態である場合に使用できます。

カーディナリティ推定 (CE) フィードバックに関する既知の問題

問題点	検出した日	Status	解決した日
特定の条件下で SQL Server 2022 (16.x) 用の累積的な更新プログラム 8 を適用した後に SQL Server のパフォーマンスが低下します。 CE フィードバックを有効にすると、プラン キャッシュ メモリ使用率が大幅に増加し、CPU 使用率が予期せず増加する場合があります。	2023 年 12 月	回避策あり

既知の問題の詳細

特定の条件下で SQL Server 2022 の累積的な更新プログラム 8 を適用した後に SQL Server のパフォーマンスが低下する

SQL Server 2022 (16.x) 累積的な更新プログラム 8 以降では、SQL Server で CPU とメモリの使用率が予期せず増加する場合があります。 また、RESOURCE_SEMAPHORE_QUERY_COMPILE 待機の増加も観測される場合があります。 使用中のプラン キャッシュ オブジェクトの数が着実に増加し、プラン キャッシュの制限に近づいていることに気付く場合もあります。この場合、ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE、DBCC FREESYSTEMCACHE、DBCC FREEPROCCACHE などの手法でプラン キャッシュを手動で消去しても解決できません。 この動作は、少数の顧客でのみ確認されています。

この問題は、すべてのワークロードに影響を与えるわけではなく、生成されたプランのユニーク数と、CE フィードバック機能が関与する対象であったプランの数に依存します。 重要なモデルの誤った見積もりが発生したプラン演算子を CE フィードバックが分析している期間中、この分析フェーズ時に発生したシナリオとして、参照されていたプランがメモリ内で逆参照されるようになり、その後、通常の最も長く使われていない (LRU) アルゴリズムを使用してプランをメモリから削除することができなくなった可能性があります。 LRU メカニズムは、SQL Server がプランの削除ポリシーを適用する 1 つの方法です。 また、システムにメモリ負荷がある場合、SQL Server はメモリからプランを削除します。 SQL Server が不適切に逆参照されたプランを削除しようとすると、プラン キャッシュからそれらのプランを削除できないため、キャッシュが拡大し続けます。 キャッシュが増えると追加のコンパイルが発生することになり、最終的にはさらに CPU とメモリを使用することになる可能性があります。 詳細については、「Plan Cache Internals (プラン キャッシュの内部)」を参照してください

現象: 使用中のプラン キャッシュ エントリの数が、SQL プランまたはオブジェクト プランからダーティとしてマークされると、時間の経過と伴って 50,000 以上に増加します。 プラン キャッシュエントリがこのレベルに近づき始め、CPU 使用率に予期しない増加が生じていることを確認した場合は、システムでこの問題が発生します。 関連する修正プログラムは、SQL Server 2022 (16.x) 累積的な更新プログラム 9 で提供されました。 KB5030731 を参照してください。 この修正では、カーディナリティ推定 (CE) フィードバックが関連するプロファイルを取得しようとしたときにプラン キャッシュ エントリが削除され、メモリ破損が発生する問題に対処しようと試みました。 この問題のその他の修正プログラムは、今後の累積的な更新プログラムで入手できるようになります。

システムが使用しているプラン キャッシュ エントリの数を監視するには、存在するプラン キャッシュ エントリ数の特定時点のビューとして、以下の例を使用できます。 たとえば、ダーティとしてマークされたプラン キャッシュ エントリの数を定期的に見ることは、この現象を監視する 1 つの方法です。

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp 
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

また、クエリの別のセットでは、前の例と同じ情報が提供されますが、追加のパフォーマンス メトリックを監視することもできます。 プラン キャッシュ ヒット率とコンパイル数は、1 秒当たりのバッチ要求数に対応して減少します。次のクエリを使用すると、システムを経時的に監視できます。 Cache Hit Ratio (予期しない低下)、使用中の Cache Object (減少せずに 50,000 に近いレベルへの数の増加) と、Compilations/sec の増加に比べた Batch Requests/sec の比率が予想より低いことに注目します。

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec' 
);

回避策: 前に説明した症状が発生している場合、追加の修正プログラムが使用可能になるまで、CE フィードバック機能はデータベース レベルで無効にすることができます。 この問題によって占有されていたプラン キャッシュ メモリを再利用するには、SQL Server インスタンスを再起動する必要があります。 この再起動アクションは、CE フィードバック機能が無効になった後に実行できます。 データベース レベルで CE フィードバックを無効にするには、 CE_FEEDBACKデータベース スコープ構成を使用します。 たとえば、ユーザー データベースで次のコマンドを実行します。

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

フィードバックと問題の報告

フィードバックや質問については、メール (CEFfeedback@microsoft.com) でお問い合わせください

関連するコンテンツ

• ブログ: Cardinality Estimation Feedback in SQL Server 2022 (SQL Server 2022 のカーディナリティ推定フィードバック)
• SQL データベースでのインテリジェントなクエリ処理
• インテリジェントなクエリ処理の機能の詳細
• カーディナリティ推定 (SQL Server)
• RECONFIGURE (Transact-SQL)
• パフォーマンスの監視とチューニング
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)