次の方法で共有

カーディナリティ推定 (SQL Server)

  • [アーティクル]

カーディナリティ推定器と呼ばれるカーディナリティ推定ロジックは、クエリ プランの品質を向上させ、クエリ パフォーマンスを向上させるために、2014 年SQL Serverに再設計されています。 新しいカーディナリティ推定機能には、現在の OLTP ワークロードとデータ ウェアハウス ワークロードで適切に機能する想定とアルゴリズムが組み込まれています。 この機能は、現在のワークロードを対象とするカーディナリティ推定に関する詳細な調査、および SQL Server のカーディナリティ推定機能を向上させるための過去 15 年にわたる研究を土台としています。 お客様からのフィードバックによると、大半のクエリは今回の変更によって性能が向上するか、何も変化しないこと、一方で、少数のクエリは以前のカーディナリティ推定機能と比較すると性能が低下する可能性があることが示されています。

Note

カーディナリティ推定とは、クエリの結果に含まれる行の数を予測することです。 クエリ オプティマイザーはこれらの推定を使用して、クエリを実行するプランを選択します。 クエリ プランの品質は、クエリ パフォーマンスの向上に直接的な影響を及ぼします。

パフォーマンス テストとチューニングに関する推奨事項

新しいカーディナリティ推定機能は、SQL Server 2014 で作成されたすべての新しいデータベースに対して有効になります。 ただし、SQL Server 2014 にアップグレードするだけでは、既存のデータベースでは新しいカーディナリティ推定機能は有効になりません。

実稼動システムで新しいカーディナリティ推定機能を有効にする前に、最適なクエリ パフォーマンスが達成できることを確認するために、次の推奨事項を使用して実際のワークロードをテストしてください。

  1. 新しいカーディナリティ推定機能を使用するように、すべての既存のデータベースをアップグレードします。 これを行うには、 ALTER DATABASE 互換性レベル (Transact-SQL) を使用して、データベース互換性レベルを 120 に設定します。

  2. 新しいカーディナリティ推定機能を使用してテスト ワークロードを実行し、パフォーマンス上の問題に対してトラブルシューティングを現在実行しているのと同じ方法で、パフォーマンスの新しい問題に対するトラブルシューティングを実行します。

  3. ワークロードが新しいカーディナリティ推定器 (データベース互換性レベル 120 (SQL Server 2014) で実行され、特定のクエリが後退したら、トレース フラグ 9481 でクエリを実行して、SQL Server 2012 以前で使用されたカーディナリティ推定器のバージョンを使用できます。 トレース フラグを指定してクエリを実行するには、サポート技術情報の資料「 特定のクエリ レベルに対応するさまざまなトレース フラグを使用して制御できる、プランが影響を及ぼす SQL Server クエリ オプティマイザーの動作の有効化」を参照してください。

  4. すべてのデータベースを一度に変更して新しいカーディナリティ推定器を使用できない場合は、 ALTER DATABASE 互換性レベル (Transact-SQL) を使用してデータベース互換性レベルを 110 に設定することで、すべてのデータベースに対して以前のカーディナリティ推定機能を使用できます。

  5. データベース互換性レベル 110 を使用してワークロードを実行し、新しいカーディナリティ推定機能を使用して特定のクエリをテストまたは実行しようとする場合は、SQL Server 2014 バージョンのカーディナリティ推定機能を使用するために、トレース フラグ 2312 を指定してそのクエリを実行することができます。 トレース フラグを指定してクエリを実行するには、サポート技術情報の資料「 特定のクエリ レベルに対応するさまざまなトレース フラグを使用して制御できる、プランが影響を及ぼす SQL Server クエリ オプティマイザーの動作の有効化」を参照してください。

新しい XEvent

新しいクエリ プランをサポートする、2 つの新しい query_optimizer_estimate_cardinality XEvent が存在します。

  • query_optimizer_estimate_cardinality は、クエリ オプティマイザーが関係式のカーディナリティを推定するときに発生します。

  • query_optimizer_force_both_cardinality_estimationの動作が発生するのは、トレース フラグ 2312 と 9481 の両方が有効になっていて、古いカーディナリティ推定動作と新しいカーディナリティ推定動作の両方を同時に適用しようとする場合です。

例

次の例では、新しいカーディナリティ推定で加えられた変更の一部を示します。 カーディナリティを推定するコードを書き直しました。 ロジックは複雑であり、すべての変更を網羅した完全な一覧をここに示すことはできません。

Note

これらの例は、概念を示す情報として掲載したものです。 管理者や開発者が、データベースとクエリを設計する方法などに変更を加える必要はありません。

例 A。新しいカーディナリティ推定機能は、最近追加された昇順データに関して、平均のカーディナリティを使用します。

この例では、最新の統計を更新する際に、テーブル内の最大値を上回る昇順データに関するカーディナリティ推定を、新しいカーディナリティ推定機能がどのように向上させるかを示します。

SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';

この例では、Sales テーブルに新しい行を毎日追加し、クエリは 12/19/2013 (2013 年 12 月 19 日) に発生した行と、12/18/2013 に最後に更新された統計を要求します。 12/19/2013 という日付がテーブル内の最大値を上回っていて、この値を含む形で統計の更新が実行されていないため、以前のカーディナリティ推定機能はこの値が存在しないことを想定していました。 昇順キーの問題と呼ばれるこの状況は、その日のうちにデータを読み込んだ後、統計が更新される前にクエリがデータを要求する場合に発生します。

この動作は変更されました。 現在は、最後に統計を更新し、その後に追加された最新の昇順データを含む形で統計が更新されていない場合でも、新しいカーディナリティ推定機能はそれらの値が存在することを想定し、カーディナリティ推定を行う際に列内にある各値として平均カーディナリティを使用します。

例 B。新しいカーディナリティ推定機能は、同じテーブル内にあるフィルター処理された複数の述語の間に、何かの相関関係があることを想定します。

この例では、Cars テーブルに 1,000 の行が存在することを想定します。200 の行では Make (メーカー) は "Honda" になっており、50 の行では Model (車種) は "Civic" になっており、"Civic" の行すべてで Make は "Honda" になっています。 したがって、Make の列では値の 20% が "Honda" になっており、Model の列では値の 5% が "Civic" になっており、"Honda Civic" の実際の数は 50 です。 以前のカーディナリティ推定機能では、Make と Model の各列にある値が互いに独立していることを想定していました。 以前のクエリ オプティマイザーは、1,000 行のうち Honda Civic は 10 行存在する (0.05 * 0.20 * 1,000 = 10 行) と推定していました。

SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';

この動作は変更されました。 現在の新しいカーディナリティ推定機能は、Make 列と Model 列の間に「何かの」 相関関係が存在することを想定します。 クエリ オプティマイザーは、推定式に指数成分を追加することにより、以前の推定を超えるカーディナリティを推定します。 クエリ オプティマイザーは、この述語に合わせて、22.36 行 (0.05 * SQRT (0.20) * 1,000 行 = 22.36 行) という推定を行います。 このシナリオと特定のデータ分布の場合は、クエリが返す 22.36 行という値は、実際の値である 50 行に近づきます。

新しいカーディナリティ推定機能のロジックは、述語選択度を並べ替え、指数を大きくすることに注意してください。 たとえば、述語選択度が 0.05、0.20、0.25 である場合は、カーディナリティ推定は (0.05 * SQRT(0.20) * SQRT(SQRT(0.25))) になります。

例 C。新しいカーディナリティ推定機能は、異なるテーブル上でフィルター処理された述語の間で、互いに依存関係がないことを想定しています。

この例では、以前のカーディナリティ推定機能は、述語フィルター s.type と r.date が相関していることを想定しています。 ただし、最近のワークロードに対するテストの結果は、異なるテーブル上にある列に対する述語フィルターが互いに相関しないことを示しています。

SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';

この動作は変更されました。 現在の新しいカーディナリティ推定機能のロジックは、s.type が r.date に相関しないことを想定しています。 つまり、玩具の返品は特定の 1 日ではなく、毎日発生することを想定しています。 この例では、新しいカーディナリティ推定機能は、以前のカーディナリティ推定機能未満の数を推定します。

参照

パフォーマンスの監視とチューニング