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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

SQL Server クエリ オプティマイザーは、コストベースのオプティマイザーです。 つまり、実行のための推定処理コストが最も低いクエリ プランが選択されます。 クエリ オプティマイザーでは、主に次の 2 つの要素に基づいてクエリ プランを実行する際のコストが決定されます。

• クエリ プランの各レベルで処理される行の総数。これをプランのカーディナリティと呼びます。
• クエリ内の演算子で指示されているアルゴリズムのコスト モデル。

最初の要素であるカーディナリティは、2 番目の要素であるコスト モデルの入力パラメーターとして使用します。 そのため、カーディナリティが向上すると、推定コストが適切になり、その結果実行プランも高速化します。

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

次の例では、SQL Server を使用してカーディナリティを正確に計算することができません。 その場合コストが正しく計算されないので、最適なクエリ プランが選択されない場合があります。 次に示す構造をクエリで使用しないようにすることで、クエリのパフォーマンスが向上する場合があります。 別のクエリ式や他の方法で代替できることがあるので、それについても記載してあります。

• 同一テーブルの異なる列どうしを比較する比較演算子を述語で使用しているクエリ。
• 次のいずれかの条件に該当し、演算子を述語で使用しているクエリ。
  • 演算子の一方の側で使用されている列の統計が存在しない。
  • 統計内の値の分布が不均一であるにもかかわらず、クエリにより選択度の高い値セットがシークされる。 これに該当するのは、主に演算子が等号 (=) 以外である場合です。
  • 等しくない (!=) 比較演算子または NOT 論理演算子を述語で使用している。
• SQL Server の組み込み関数、または引数が定数値でないスカラー値関数かユーザー定義関数を使用するクエリ。
• 算術連結演算子または文字列連結演算子によって結合した列を含んでいるクエリ。
• クエリのコンパイル時または最適化時に値が確定しない変数を比較するクエリ。

この記事では、ご利用のシステムに最適な CE 構成を評価して選択する方法を示しています。 最も正確なので、ほとんどのシステムで最新の CE のメリットを享受できます。 CE はクエリが返す可能性のある行の数を予測します。 クエリ オプティマイザーではカーディナリティ予測を使用して、最適なクエリ プランを生成します。 推定が正確であるほど、通常はクエリ オプティマイザーでより最適なクエリ プランを生成できます。

アプリケーション システムには、バージョン全体の CE の変更が原因で低速のプランに変更された重要なクエリが含まれている可能性があります。 CE の問題によって低速で実行するクエリを識別するための手法とツールがあります。 また、その後のパフォーマンスの問題に対処する方法のオプションもあります。

CE のバージョン

1998 年には、CE の主要な更新プログラムは、互換性レベルが 70 の SQL Server 7.0 の一部でした。 このバージョンの CE モデルは、次の 4 つの基本的な前提条件で設定されています。

• 非依存性: 異なる列のデータ分布は、相関関係情報があって使用可能な場合を除き、相互に独立しているものと想定されます。

• 統一性: 個別の値は等間隔であり、すべて同じ頻度です。 具体的には、各ヒストグラム ステップ内では、個別の値は均等に分散し、各値は同じ頻度です。

• コンテインメント (単純): ユーザーは存在するデータをクエリします。 たとえば、2 つのテーブル間の等価結合では、結合の選択度を推定するためにヒストグラムを結合する前に、各入力のヒストグラムの述語選択度¹を考慮します。

• 包含:Column = Constant のフィルター述語では、定数が関連付けられた列に実際に存在すると想定されます。 対応するヒストグラム ステップが空ではない場合、ステップの個別値のいずれかは述語の値と一致するものと想定されます。

  ¹ 述語を満たす行数。

以降の更新プログラムは SQL Server 2014 (12.x) 以上 (互換性レベルが 120 以上) に含まれています。 レベル 120 以上の CE 更新プログラムには、最新のデータ ウェアハウスおよび OLTP ワークロードで適切に機能する更新された前提条件とアルゴリズムが組み込まれています。 CE 120 以降では、CE 70 の前提条件から次のモデル前提条件が変更されました。

• 非依存性が相関関係になります: 異なる列の値の組み合わせは必ずしも独立していません。 これはより実際のデータ クエリと似ている可能性があります。
• 簡単なコンテインメントはベース コンテインメントになります: ユーザーは存在しないデータをクエリする可能性があります。 たとえば、2 つのテーブル間の等価結合では、ベース テーブル ヒストグラムを使用して結合の選択度を推定した後、述語選択度を考慮します。

クエリストアを使用して CE のバージョンを評価する

SQL Server 2016 (13.x)から導入されたクエリ ストアは、クエリのパフォーマンスを確認する場合に便利なツールです。 クエリ ストアを有効にすると、実行プランが変更された場合でも、経時的なクエリのパフォーマンスの追跡が開始されます。 コストが高い場合や、クエリのパフォーマンスが低下したときに、クエリ ストアを監視します。 詳細については、「クエリ ストアを使用したパフォーマンスの監視」をご覧ください。

SQL Server へのアップグレードを準備する場合、または任意の SQL Server プラットフォームでデータベース互換レベルを上げる場合は、 クエリチューニングアシスタントを使用してデータベースをアップグレードすることを検討してください。これは、2 つの異なる互換性レベルでクエリのパフォーマンスを比較するのに役立ちます。

重要

クエリ ストアがデータベースとワークロードに対して正しく構成されていることを確認します。 詳細については、「クエリ ストアを使用するときの推奨事項」を参照してください。

拡張イベントを使用して CE のバージョンを評価する

カーディナリティ推定処理を追跡するための別のオプションは、query_optimizer_estimate_cardinality という名前の拡張イベントを使用することです。 次の Transact-SQL コード サンプルを SQL Server で実行します。 これは C:\Temp\ に .xel ファイルを書き込みます (ただし、パスを変更することができます)。 Management Studio で .xel ファイルを開くと、ユーザーにわかりやすい方法で詳細情報が表示されます。

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Note

イベント sqlserver.query_optimizer_estimate_cardinality は、Azure SQL データベースでは使用できません。

SQL Database 向けにチューニングされた拡張イベントの詳細については、「 SQL データベースの拡張イベント」を参照してください。

CE のバージョンを評価する手順

次に示す手順を使用して、最も重要なクエリの中に、最新の CE でパフォーマンスが低下するものがあるかどうかを評価できます。 一部の手順は、前のセクションで説明されたコード サンプルを実行すると実行されます。

1. SQL Server Management Studio (SSMS) を起動します。 SQL Server データベースが、利用できる最も高い互換性レベルに設定されていることを確認してください。

2. 次の準備作業を実行します。

   1. SQL Server Management Studio (SSMS) を起動します。

   2. Transact-SQL を実行して、SQL Server データベースが、利用できる最も高い互換レベルに設定されていることを確認します。

   3. データベースの LEGACY_CARDINALITY_ESTIMATION 構成が OFF になっていることを確認します。

   4. クエリ ストアをクリアします。 データベースでクエリ ストアが有効になっていることを確認します。

   5. SET NOCOUNT OFF; ステートメントを実行します。

3. SET STATISTICS XML ON; ステートメントを実行します。

4. 重要なクエリを実行します。

5. [メッセージ] タブの結果ウィンドウで、影響を受けた行の実際の数を確認してください。

6. [結果] タブの結果ウィンドウで、XML 形式の統計情報を含むセルをダブルクリックします。 グラフィックのクエリ プランが表示されます。

7. グラフィックのクエリ プランで最初のボックスを右クリックし、 [プロパティ]を選択します。

8. 別の構成と後で比較するために、次のプロパティの値を記録してください:

   • CardinalityEstimationModelVersion。

   • 推定される行の数。

   • I/O の推定コストと、行数の予測ではなく、実際のパフォーマンスを含む 推定 プロパティ。

   • 論理操作 と 物理操作。 並列処理 は有効な値です。

   • 実際の実行モード。 バッチ は、 行より適切で、有効な値です。

9. 行の推定数と行の実際の数とを比較します。 CE は 1% (前後)、または 10% 程度、不正確ですか?

10. SET STATISTICS XML OFF; を実行します。

11. Transact-SQL を実行して、データベースの互換レベルを 1 レベルずつ下げます (例: 130 から 120 に下げる)。

12. すべての非準備ステップを再実行します。

13. 2 つの実行からの CE プロパティ値を比較します。

    • 最新の CE での不正確さの割合は、以前の CE での不正確さの割合より小さいですか?

14. 最後に、2 つの実行からのさまざまなパフォーマンス プロパティ値を比較します。

    • クエリは 2 つの異なる CE 見積もりでの異なるプランを使用しましたか?

    • 最新の CE でのクエリ実行速度は低速でしたか?

    • 古い CE で別のプランを使用してクエリの動作が向上するのでない限り、恐らく最新の CE を使用したいと思われるでしょう。

    • ただし、クエリが古い CE でより高速なプランで実行している場合は、システムで高速なプランを強制的に使用し、CE を無視することを検討してください。 これが、1 つの例外で高速のプランを維持しながらも、すべてに対して最新の CE を保持できる方法です。

最適なクエリ プランをアクティブにする方法

CE 120 以降で効果の低いクエリ プランが、クエリで生成されるとします。 次に、よりよいプランをアクティブにするいくつかのオプションを、スコープが最大のものから最小のものの順に示します。

• データベース全体に対して、データベース互換レベルを利用可能な最新の値より低い値に設定できます。

  • たとえば、互換性レベル 110 以下に設定すると CE 70 がアクティブ化しますが、すべてのクエリは以前の CE モデルの対象になります。

  • さらに、低い互換レベルに設定すると、最新バージョンのクエリ オプティマイザーの多数の機能強化も利用できず、データベースに対するすべてのクエリに影響します。

• LEGACY_CARDINALITY_ESTIMATION データベース 範囲の構成オプションを使用すると、クエリ オプティマイザーの他の機能強化を維持したまま、データベース全体が古い CE を使用するようにできます。

• LEGACY_CARDINALITY_ESTIMATION クエリ ヒントを使用すると、クエリ オプティマイザーの他の機能強化を維持したまま、単一のクエリが古い CE を使用するようにできます。

• クエリ ストア ヒント機能を使用して LEGACY_CARDINALITY_ESTIMATION を強制することで、1 つのクエリでクエリを変更せずに古い CE を使用できます。

• クエリ ストアで異なるプランを強制します。

データベース互換性レベル

COMPATIBILITY_LEVEL に次の Transact-SQL コードを使用して、データベースが特定のレベルであることを確認します。

重要

SQL Server と Azure SQL Database のデータベース エンジンのバージョン番号は類似のものではありません。別個の製品に与えられる内部製造番号になっています。 SQL Server のデータベース エンジンは SQL Server データベース エンジンと同じコードに基づいています。 最も重要なことですが、Azure SQL Database のデータベース エンジンには常に最新の SQL データベース エンジン ビットが与えられます。 Azure SQL Database のバージョン 12 は SQL Server のバージョン 15 より新しくなります。 2019 年 11 月の時点で、Azure SQL データベース で新しく作成されたデータベースの既定の互換性レベルは 150 です。 Microsoft では、既存のデータベースに対してデータベース互換レベルを更新することはありません。 それは、お客様の独自の裁量にまかされます。

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

低い互換性レベルで実行されている既存のデータベースの場合、より高いデータベース互換レベルでのみ使用できる拡張機能をアプリケーションで使用する必要がない限り、前のデータベース互換レベルを維持することは有効なアプローチです。 新しい開発作業の場合、またはインテリジェント クエリ処理のような新しい機能と一部の新しい Transact-SQL を既存のアプリケーションで使用する必要があるときは、データベース互換性レベルを利用できる最新のレベルにアップグレードすることを計画します。 詳細については、「互換性レベルとデータベース エンジンのアップグレード」を参照してください。

注意事項

データベース互換レベルを変更する前に、「データベース互換性レベルのアップグレードのベスト プラクティス」を確認してください。

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

互換性レベルが 120 以上に設定されている SQL Server データベースの場合、トレース フラグ 9481 をアクティブにすると、システムは強制的に CE バージョン 70 を使用します。

レガシ カーディナリティ推定機能

互換性レベルが 120 以上に設定されている SQL Server データベースの場合、 ALTER DATABASE SCOPED CONFIGURATION を使用して、データベース レベルでレガシ カーディナリティ推定機能 (CE バージョン 70) をアクティブにすることができます。

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

ヒントを使用するようにクエリを変更する

SQL Server 2016 (13.x) SP1 以降では、クエリ ヒント USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') を使用するようにクエリを変更します。

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

クエリ ストア ヒントを設定する

クエリ ストア ヒント 機能を使用すると、クエリを変更せずにレガシ カーディナリティ推定機能を使用するように、クエリを強制することができます。

1. sys.query_store_query_text および sys.query_store_query クエリ ストア カタログ ビューで、クエリを識別します。 たとえば、次のテキスト フラグメントで実行されたクエリを検索します。

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. 次の例では、クエリ ストア ヒントを適用して、クエリを変更することなく、query_id 39 でレガシ カーディナリティ推定機能を強制的に実行します。

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Note

詳細については、「クエリ ストア ヒント (プレビュー)」を参照してください。 現在、この機能は Azure SQL データベースでのみ使用できます。

特定のクエリ プランを強制的に実行する方法

詳細に制御するために、システムがテスト中に CE 70 で生成されたプランを "強制的に" 使用するように指定できます。 優先するプランを 固定 した後、データベース全体が最新の互換性レベルと CE を使用するように設定できます。 このオプションについて、次に詳しく説明します。

クエリ ストアでは、特定のクエリ プランを使用するようシステムを強制するさまざまな方法を示します。

• sys.sp_query_store_force_plan を実行します。

• SQL Server Management Studio (SSMS) で、 [クエリ ストア] ノードを展開し、 [トップ リソース コンシューマー ノード] を右クリックして、 [トップ リソース コンシューマー ノードの表示] を選択します。 [プランの強制] と [プランを強制しない]というラベルのボタンが表示されます。

クエリ ストアの詳細については、「クエリのストアを使用した、パフォーマンスの監視」を参照してください。

カーディナリティ推定中の定数のたたみ込みと式の評価

データベース エンジン では、クエリのパフォーマンスを向上させるため、いくつかの定数式が前もって評価されます。 これを定数のたたみ込みと呼びます。 定数は Transact-SQL リテラル (3、'ABC'、'2005-12-31'、1.0e3、0x12345678 など) です。 詳細については、定数のたたみ込みに関する記事を参照してください。

また、定数のたたみ込みは行われませんが、引数がコンパイル時に確定する式は、引数がパラメーターと定数のどちらでも、最適化のときにクエリ オプティマイザーの一部である結果セットのサイズ (カーディナリティ) 推定機能によって評価されます。 詳細については、「式の評価」を参照してください。

ベスト プラクティス: 定数のたたみ込みおよびコンパイル時の式の評価を使用した最適なクエリ プランの生成

最適なクエリ プランを生成するには、クエリ オプティマイザーによってデータの分散に関する統計を基にクエリ内の条件の選択度を正確に推定することができるように、クエリ、ストアド プロシージャ、バッチを設計するのが最善です。 そうしないと、クエリ オプティマイザーでは選択度の推定に既定の推定を使用する必要があります。

クエリ オプティマイザーのカーディナリティ推定機能で良い推定結果を得るには、AUTO_CREATE_STATISTICS および AUTO_UPDATE_STATISTICS データベースの SET オプションが ON (既定値) に設定されていること、またはクエリの条件で参照しているすべての列の統計を手動で作成してあることを最初に確認してください。 その上で、クエリの条件をデザインするときに、できる限り次のことを守ってください。

• クエリでローカル変数を使用しないようにします。 代わりに、クエリでパラメーター、リテラル、または式を使用します。

• パラメーターを含むクエリに埋め込まれる演算子と関数の使用を、カーディナリティ推定のコンパイル時式の評価に記載されているものに制限します。

• クエリの条件で定数のみの式が定数折りたたみ可能であるか、コンパイル時に評価できることを確認します。

• ローカル変数を使用してクエリで使用する式を評価する必要がある場合は、クエリとは異なるスコープで評価することを検討してください。 たとえば、次のいずれかのオプションを実行するとよい場合があります。

  • 評価するクエリを含むストアド プロシージャに変数の値を渡し、ローカル変数の代わりにプロシージャ パラメーターを使用します。

  • ローカル変数の値を一部に使用したクエリから構成される文字列を作成し、動的 SQL (EXEC またはできれば sp_executesql) を使用してこの文字列を実行します。

  • クエリをパラメーター化して sp_executesql によって実行し、変数の値をパラメーターとしてクエリに渡します。

CE の強化機能の例

このセクションでは、最近のリリースで CE に実装された拡張機能からメリットを享受できるクエリの例について説明します。 これは、ユーザー側で特定の操作が要求されていない背景情報です。

例 A。CE は、統計が最後に収集されたときよりも、最大値が高くなる可能性があることを理解しています

統計は 2016-04-30 に OrderTable について最後に収集され、最大 OrderAddedDate は 2016-04-30 であったものとします。 CE 120 (およびより高いレベル) は、"昇順" データを持つ OrderTable の列が、統計によって記録された最大値よりも大きい値を持つ可能性があることを理解しています。 これを理解すると、次のような Transact-SQL SELECT ステートメントのクエリ プランの機能を改善できます。

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

例 B。CE は、同じテーブル上のフィルターにかけられた述語が頻繁に関連付けられることを理解しています

次の SELECT では、フィルターにかけられた述語が Model と ModelVariant に表示されます。 Model が "Xbox" である場合は、ModelVariant が "One" の可能性があることを直感的に理解できます (Xbox に One というバリアントがある場合)。

CE 120 以降では、SQL Serverは同じテーブルの 2 つの列、Model と ModelVariant の間に相関関係がある可能性があることを理解します。 CE はクエリで返される行数のより正確な見積もりを行い、クエリ オプティマイザーがより最適なプランを生成します。

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

例 C。CE は異なるテーブルからフィルターにかけられた述語の間に、もはや相関関係がないと想定します

最新のワークロードと実際のビジネス データについて広範囲に実施された最近の調査によると、異なるテーブルの述語フィルターは通常、相関関係がないことが明らかになりました。 次のクエリでは、CE は s.type と r.date 間の相関関係がないと想定します。 そのため CE は、返される行数の推定値を低く指定します。

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 = '2016-05-11';  

関連するコンテンツ

• カーディナリティ推定 (CE) のフィードバック
• パフォーマンスの監視とチューニング
• SQL Server 2014 のカーディナリティ推定機能によるクエリプランの最適化
• ヒント (Transact-SQL) - Query
• USE HINT クエリ ヒント
• クエリ チューニング アシスタントを使用したデータベースのアップグレード
• クエリ ストアを使用したパフォーマンスの監視
• クエリ処理アーキテクチャ ガイド
• クエリ ストアのヒント
• SQL データベースでのインテリジェントなクエリ処理
• DBCC TRACEON - トレース フラグ (Transact-SQL)
• sys.query_store_query_hints