新しいカーディナリティ推定器での結合コンテインメントの前提条件により、クエリのパフォーマンスが低下する

この記事は、新しいカーディナリティ推定機能を使用してクエリをコンパイルするときに、SQL Server 2014 以降のバージョンで発生する可能性があるパフォーマンスの問題を解決するのに役立ちます。

元の製品バージョン: SQL Server
元の KB 番号: 3189675

現象

以下のシナリオについて考えてみます。

• SQL Server 2014 以降のバージョンを使用しています。
• 結合と非結合フィルター述語を含むクエリを実行します。
• 新しい Cardinality Estimation (SQL Server) (新しい CE) を使用してクエリをコンパイルします。

このシナリオでは、クエリのパフォーマンスが低下します。

レガシ CE を使用してクエリをコンパイルする場合、この問題は発生しません。

原因

SQL Server 2014 から、データベース互換性レベル 120 以上の新しいカーディナリティ推定機能 (新しい CE) が導入されました。 新しい CE では、クエリ オプティマイザーが異なる演算子と述語のカーディナリティを推定するときに使用されるモデルのレガシ CE からいくつかの前提条件が変更されます。

これらの変更の 1 つは、結合包含の前提条件に関連しています。

レガシ CE モデルでは、ユーザーが常に存在するデータを照会することを前提としています。 つまり、2 つのテーブルに対して等結合操作を伴う結合述語の場合、結合列は結合の両側に存在します。 結合テーブルに対して追加の非結合フィルター述語が存在する場合、レガシ CE では、結合述語と非結合フィルター述語に対してある程度の相関関係が想定されます。 この暗黙の相関関係は、単純包含と呼ばれます。

または、新しい CE では、関連付けとしてベースコンテインメントが使用されます。 新しい CE モデルでは、ユーザーが存在しないデータを照会する可能性があることを前提としています。 これは、個別のテーブルのフィルター述語が相互に関連付けられない可能性があることを意味します。 したがって、確率論的アプローチを使用します。

多くの実用的なシナリオでは、基本包含の前提条件を使用すると、より適切な見積もりが作成されます。 これにより、より効率的なクエリ プランの選択が作成されます。 ただし、状況によっては、単純包含の前提条件を使用すると、より良い結果が得られます。 この場合、レガシ CE の代わりに新しい CE を使用すると、クエリ プランの選択の効率が低下する可能性があります。

新しい CE に関連する問題のトラブルシューティング方法の詳細については、「sql Server 2012 以前から 2014 以降にアップグレードした後のdecreased クエリのパフォーマンスを参照してください。

解決方法

SQL Server 2014 以降のバージョンでは、 trace フラグ 9476 を使用して 既定の基本包含の前提条件ではなく、単純な包含の前提条件を SQL Server に強制的に使用させることができます。 アプリケーション クエリを変更できる場合は、SQL Server 2016 (13.x) SP1 より後 ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS クエリ ヒントを使用することをお勧めします。 詳細については、 USE HINT を参照してください。 例えば次が挙げられます。

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

次の条件に該当する場合、このトレース フラグを有効にするか、クエリ ヒントを使用すると、レガシ CE モデルに完全に戻すことなく、クエリ プランの選択が向上する可能性があります。

• 最適ではないクエリ プランを選択すると、結合と非結合フィルター述語を含むクエリの全体的なパフォーマンスが低下します。
• "結合カーディナリティ" 推定で有意な不正確さを確認できます (つまり、実際の行数と推定される行数が大きく異なります)。
• レガシ CE を使用してクエリをコンパイルする場合、この不正確さは存在しません。

このトレース フラグは、グローバル、セッション レベル、またはクエリ レベルで有効にすることができます。