統計

適用対象: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

クエリ オプティマイザーでは、クエリのパフォーマンスを向上させるクエリ プランを作成するために統計を使用します。 ほとんどのクエリでは、高品質のクエリ プランに必要な統計がクエリ オプティマイザーによって既に生成されていますが、最適な結果を得るために追加の統計情報を作成したり、クエリのデザインを変更したりする必要がある場合もあります。 この記事では、統計の概念について説明し、クエリ最適化の統計を効果的に使用するためのガイドラインを提供します。

コンポーネントおよび概念

統計

クエリ最適化に関する統計は、テーブルまたはインデックス付きビューの 1 つまたは複数の列の値の分布に関する統計情報を格納するバイナリ ラージ オブジェクト (BLOB) です。 クエリ オプティマイザーでは、これらの統計を使用してクエリ結果のカーディナリティ、つまり行数を推定します。 これらのカーディナリティの推定に基づいて、クエリ オプティマイザーでは高品質なクエリ プランを作成できます。 たとえば、ご利用の述語によっては、クエリ オプティマイザーがカーディナリティの推定を使用して、リソース消費の多い Index Scan 操作ではなく Index Seek 操作を選択することがあります。そうすることで、クエリのパフォーマンスが高まります。

統計オブジェクトは 1 つ以上のテーブル列で構成されるリストごとに作成され、それぞれに最初の列の値の分布を示すヒストグラムが含まれます。 複数列の統計オブジェクトには、さらに、列間の値の相関関係に関する統計情報も格納されます。 これらの相関関係の統計情報 ( 密度) は、個別の列値を持つ行の数から得られます。

ヒストグラム

ヒストグラムでは、データセットの個別の値ごとに出現頻度を測定します。 クエリ オプティマイザーでは、統計オブジェクトの最初のキー列の列値に基づいてヒストグラムを計算し、行を統計的にサンプリングするかテーブルまたはビュー内のすべての行でフル スキャンを実行することによって列値を選択します。 サンプリングされた行のセットからヒストグラムを作成する場合、格納される行の総数および個別の値の数は推定値であり、必ずしも整数にはなりません。

ヒストグラムを作成するには、クエリ オプティマイザーで列値を並べ替え、個別の列値ごとに一致する値の数を計算し、列値を最大 200 の連続したヒストグラム区間に集計します。 各ヒストグラム区間には、列値の範囲と上限の列値が順番に含まれます。 この範囲には、境界値の間 (境界値自体は除く) のすべての有効な列値が含まれます。 格納される最小の列値は、最初のヒストグラム区間の上限境界値になります。

具体的には、SQL Serverは、次の 3 つの区間で、並べ替えられた列値のセットからヒストグラムを作成します。

• ヒストグラムの初期化: 最初の区間で、並べ替えられたセットの先頭から始まる値のシーケンスが処理され、range_high_key、equal_rows、range_rows、distinct_range_rows の最大 200 個の値が収集されます (この区間の間、range_rows と distinct_range_rows は常にゼロです)。 最初の区間は、すべての入力が使用されたとき、または 200 個の値が見つかったときに終了します。
• バケットのマージを使用したスキャン: 2 つ目の手順では、統計キーの先頭の列から追加された各値が並び順で処理されます。連続する各値は最後の範囲に追加されるか、末尾に新しい範囲が作成されます (これは、入力値が並べ替えられているため可能です。)。 新しい範囲が作成されると、既存の隣接する範囲の 1 組が 1 つの範囲に折りたたまれます。 情報の損失を最小限に抑えるために、この範囲の組が選択されます。 この方法では、区間幅を最大にするアルゴリズムを使用して境界値の差を最大にし、ヒストグラムの区間の数を最小限に抑えます。 範囲を折りたたんだ後の手順の数は、この手順全体で 200 個のままです。
• ヒストグラムの統合: 3 番目の区間では、失われる情報の量が少なければ、より多くの範囲が折りたたまれる可能性があります。 ヒストグラムの区間の数は、境界点が 200 より少ない列でも、個別の値の数より少なくなることがあります。 そのため、列に 200 を超える一意の値が含まれていても、ヒストグラムの区間の数は 200 未満となることがあります。 一意の値のみで構成される列の場合、統合されたヒストグラムには最小で 3 つの区間が存在します。

次の図は、6 つの区間があるヒストグラムを示しています。 最初の上限境界値の左側にある領域が最初の区間です。

上記のヒストグラムの各区間は、以下のように表されます。

• 太線は、上限境界値 (range_high_key) およびその出現回数 (equal_rows) を表します。

• range_high_key の左にある領域は、列値の範囲、およびそれぞれの列値の平均出現回数 (average_range_rows) を表します。 最初のヒストグラム区間の average_range_rows は常に 0 です。

• 点線は、範囲内にある個別の値の総数 (distinct_range_rows) および範囲内の値の総数 (range_rows) を推定するために使用されるサンプリングされた値を表します。 クエリ オプティマイザーでは、range_rows および distinct_range_rows を使用して average_range_rows を計算します。サンプリングされた値は格納されません。

密度ベクトル

密度とは、特定の列または列の組み合わせにおける重複の数に関する情報であり、1/(個別の値の数) の式で計算されます。 クエリ オプティマイザーでは、同一のテーブルまたはインデックス付きビューから複数の列を返すクエリに対するカーディナリティの推定を向上させるために密度を使用します。 密度が減少するにつれて、値の選択度が高くなります。 たとえば、車を表すテーブルの場合、同メーカーの車がいくつもあります。ただし、VIN (車両番号) はそれぞれの車両固有のものです。 VIN 上のインデックスは、製造元でのインデックスより選択度が高くなります。これは VIN の密度が製造元の場合より低いからです。

密度ベクトルには、統計オブジェクトの列のプレフィックスごとに 1 つの密度が格納されます。 たとえば、統計オブジェクトに CustomerId、ItemId、Price というキー列がある場合、以下の列プレフィックスごとに密度が計算されます。

列プレフィックス	密度の計算対象
(CustomerId)	CustomerId の値が一致する行
(CustomerId, ItemId)	CustomerId および ItemId 値が一致する行
(CustomerId, ItemId, Price)	CustomerId、ItemId、および Price の値が一致する行

フィルター選択された統計情報

適切に定義されたデータのサブセットから選択するクエリでは、フィルター選択された統計情報を使用するとクエリのパフォーマンスを向上させることができます。 フィルター選択された統計情報では、統計情報に含まれるデータのサブセットを選択するためにフィルター述語を使用します。 統計情報を適切にフィルター選択すると、テーブル全体の統計情報を使用する場合と比べて、クエリ実行プランが向上します。 フィルター述語の詳細については、「CREATE STATISTICS (Transact-SQL)」を参照してください。 フィルター選択された統計情報を作成する場合の詳細については、このトピックの「統計を作成する場合」を参照してください。

統計オプション

統計の作成と更新のタイミングおよび方法を指定するオプションがあります。 これらのオプションは、データベース レベルでのみ設定できます。

AUTO_CREATE_STATISTICS オプション

統計の自動作成オプション AUTO_CREATE_STATISTICS がオンの場合、クエリ プランのカーディナリティの推定を向上させるために、クエリ オプティマイザーによってクエリ述語内の個々の列に関する統計が必要に応じて作成されます。 これらの 1 列ずつの統計は、既存の統計オブジェクトにまだヒストグラムがない列について作成されます。 AUTO_CREATE_STATISTICS オプションでは、インデックスに対する統計を作成するかどうかは判断されません。 また、フィルター選択された統計情報も生成されません。 このオプションは、テーブル全体の 1 列ずつの統計にのみ適用されます。

AUTO_CREATE_STATISTICS オプションを使用した結果としてクエリ オプティマイザーによって統計が作成された場合、その統計名は _WA で始まります。 次のクエリを使用すると、クエリ オプティマイザーでクエリ述語列の統計が作成されたかどうかを判断できます。

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

AUTO_UPDATE_STATISTICS オプション

統計の自動更新オプション AUTO_UPDATE_STATISTICS がオンの場合、古くなっている可能性がある統計がクエリ オプティマイザーによって判断され、それらがクエリで使用されると更新されます。 このアクションは、統計の再コンパイルとも呼ばれます。 挿入、更新、削除、またはマージ操作による変更によって、テーブルまたはインデックス付きビューのデータの分布が変わると、統計は古くなったと判断されます。 クエリ オプティマイザーでは、統計が前回更新されてから発生した行の変更の数をカウントし、その行の変更の数をしきい値と比較することで、統計が古くなっている可能性がないかを判断します。 しきい値はテーブルのカーディナリティに基づきます。これは、テーブルまたはインデックス付きビューに含まれる行数として定義できます。

行の変更に基づいて統計が古くなっているとマークする処理は、AUTO_UPDATE_STATISTICS オプションが OFF の場合でも発生します。 AUTO_UPDATE STATISTICS オプションが OFF になっている場合、統計は、古くなっているとマークされていても、更新されません。 プランでは、古い統計オブジェクトが引き続き使用されます。 AUTO_UPDATE_STATISTICS を OFF に設定すると、最適ではないクエリ プランが作成されて、クエリのパフォーマンスが低下することがあります。 AUTO_UPDATE STATISTICS オプションを ON に設定することをお勧めします。

• SQL Server 2014 (12.x)までは、データベース エンジンによって、統計が評価された時点でのテーブルまたはインデックス付きビュー内の行数に基づく再コンパイルのしきい値が使用されます。 しきい値は、テーブルが一時的か永続的かによって異なります。

  テーブルの種類です。	テーブルのカーディナリティ (n)	再コンパイルのしきい値 (# 変更数)
  一時	n< 6	6
  一時	6 <= n<= 500	500
  永続的	n<= 500	500
  一時的または永続的	n> 500	500 + (0.20 * n)

  たとえば、テーブルに 2 万行が含まれている場合、計算は 500 + (0.2 * 20,000) = 4,500 となり、統計は 4,500 回の変更ごとに更新されます。

• SQL Server 2016 (13.x)以降で、データベース互換レベルが 130 の場合、データベース エンジンは、統計が評価された時点でのテーブルのカーディナリティに基づいて調整される、減少する動的な統計の再コンパイルのしきい値も使用します。 この変更により、大規模なテーブルの統計がより頻繁に更新されます。 ただし、データベースの互換性レベルが 130 未満の場合、SQL Server 2014 (12.x) のしきい値が適用されます。

  テーブルの種類です。	テーブルのカーディナリティ (n)	再コンパイルのしきい値 (# 変更数)
  一時	n< 6	6
  一時	6 <= n<= 500	500
  永続的	n<= 500	500
  一時的または永続的	n> 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  たとえば、テーブルに 200 万行含まれている場合、計算は 500 + (0.20 * 2,000,000) = 400,500 と SQRT(1,000 * 2,000,000) = 44,721 の小さい方となります。 これは、44,721 の変更ごとに統計が更新されることを意味します。

すべてのシナリオで推奨されますが、トレース フラグ 2371 を有効にすることは省略可能です。 ただし、 SQL Server 2016 (13.x)より前の環境でトレース フラグ 2371 を有効にしたい場合、次のガイダンスを使用できます。

• SAP システムを使用している場合は、このトレースを有効にします。 詳細については、こちらのトレース フラグ 2371 に関するブログを参照してください。
• 現在の自動更新が十分頻繁にトリガーされないため、夜間ジョブを使用して統計を更新する必要がある場合は、トレース フラグ 2371 を有効にして、しきい値をテーブルのカーディナリティに合わせることを検討してください。

クエリ オプティマイザーによる古い統計の確認は、クエリをコンパイルする前と、キャッシュされたクエリ プランを実行する前に行われます。 クエリをコンパイルする前は、クエリ オプティマイザーで、クエリ述語内の列、テーブル、およびインデックス付きビューを使用して古くなっている可能性がある統計が判断されます。 キャッシュされたクエリ プランを実行する前は、データベース エンジンで、クエリ プランが最新の統計を参照しているかどうかが確認されます。

AUTO_UPDATE_STATISTICS オプションは、インデックスに対して作成された統計オブジェクト、クエリ述語内の列に対して 1 列ずつ作成された統計オブジェクト、および CREATE STATISTICS ステートメントを使用して作成された統計に適用されます。 また、フィルター選択された統計情報にも適用されます。

sys.dm_db_stats_properties を使用すると、テーブルで変更された行数を正確に追跡し、統計を手動で更新するかどうかを判断できます。

AUTO_UPDATE_STATISTICS はメモリ最適化テーブルでは常に OFF です。

AUTO_UPDATE_STATISTICS_ASYNC

統計の非同期更新オプション AUTO_UPDATE_STATISTICS_ASYNC によって、クエリ オプティマイザーで統計の同期更新と非同期更新のどちらを使用するかが決まります。 既定では、統計の非同期更新オプションはオフであり、クエリ オプティマイザーによる統計の更新は同期更新になります。 AUTO_UPDATE_STATISTICS_ASYNC オプションは、インデックスに対して作成された統計オブジェクト、クエリ述語内の列に対して 1 列ずつ作成された統計オブジェクト、および CREATE STATISTICS ステートメントを使用して作成された統計に適用されます。

統計の更新には、同期更新 (既定) と非同期更新があります。

• 統計の同期更新では、クエリには常に最新の統計が使用され、コンパイルおよび実行されます。 統計が古い場合、クエリ オプティマイザーでは、統計が更新されるのを待機してからクエリがコンパイルされ、実行されます。

• 統計の非同期更新では、既存の統計が古い場合でも、既存の統計を使用してクエリがコンパイルされます。 クエリ オプティマイザーは、クエリをコンパイルするときに統計が古い場合、最適ではないクエリ プランを選択する場合があります。 通常、統計はその後すぐに更新されます。 統計の更新の完了後にコンパイルされるクエリには、更新された統計が使用されることでメリットが得られます。

テーブルの切り捨てや大部分の行の一括更新を行うなど、データの分布が変わる操作を実行する場合は、同期統計を使用することを検討してください。 操作の完了後に手動で統計を更新していない場合、同期統計を使用すれば、確実に、変更されたデータに対するクエリが実行される前に統計が最新になります。

次のような場合は、非同期統計を使用してクエリの応答時間を予測しやすくすることを検討してください。

• アプリケーションで同じクエリ、類似のクエリ、またはキャッシュされた類似のクエリ プランを頻繁に実行する場合。 クエリの応答時間は、統計の同期更新を使用するよりも非同期更新を使用した方が予測しやすくなります。非同期更新の場合、クエリ オプティマイザーでは、統計が最新になるまで待機せずに着信クエリを実行できるためです。 これにより、一部のクエリの遅延については回避することができます。

• アプリケーションで統計の更新を待機している 1 つ以上のクエリによって、クライアント要求がタイムアウトする場合。 場合によっては、同期統計を待機していることが原因で、厳しいタイムアウト時間が設定されたアプリケーションが失敗することがあります。

統計の非同期更新は、バックグラウンド要求によって実行されます。 要求は、更新された統計情報をデータベースに書き込む準備ができた時点で、統計メタデータ オブジェクトに対するスキーマ変更ロックの取得を試みます。 別のセッションが同じオブジェクトに対して既にロックを保持している場合、スキーマ変更ロックを取得できるようになるまで、非同期統計の更新がブロックされます。 同様に、クエリをコンパイルするために統計メタデータ オブジェクトに対するスキーマ安定性 (Sch-S) ロックを取得する必要があるセッションは、既にスキーマ変更ロックの取得を保持しているか待機している非同期統計更新のバックグラウンド セッションによってブロックされる可能性があります。 したがって、クエリのコンパイルや統計の更新が非常に頻繁に行われるワークロードでは、非同期統計を使用すると、ロックのブロックによる同時実行の問題が起きる可能性が高くなる場合があります。

Azure SQL Database、Azure SQL Managed Instance、および SQL Server 2022 (16.x) 以降で ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY データベース スコープ構成を有効にすると、統計の非同期更新の使用によって発生する潜在的な同時実行の問題を回避できます。 この構成を有効にすると、他の要求が既存の統計情報を使用してクエリをコンパイルしている間、バックグラウンド要求では、優先度の低い別のキューでスキーマ修正 (Sch-M) ロックの取得が待機され、更新された統計が保持されます。 他のセッションが統計メタデータ オブジェクトのロックを保持しなくなると、バックグラウンド要求はそのスキーマ変更ロックおよび更新統計を取得します。 まれに、バックグラウンド要求が数分のタイムアウト期間内にロックを取得できない場合、非同期統計の更新は中止されます。統計は、別の自動統計更新がトリガーされるか、手動で更新されるまで更新されません。

AUTO_DROP オプション

適用対象:*: Azure SQL Database、Azure SQL Managed Instance、SQL Server 2022 (16.x) 以降

SQL Server 2022 (16.x) より前のSQL Serverでは、統計がユーザーによって手動で、またはユーザー データベース上にあるサード パーティ製ツールによって作成される場合、その統計オブジェクトを使用すると、顧客が目的とするスキーマの変更が、ブロックされたり、干渉されたりする可能性があります。

SQL Server 2022 (16.x) 以降の オート ドロップ オプションは、新しいデータベースと移行されたすべてのデータベースで既定で有効になります。 AUTO_DROP プロパティを使用すると、モード内での統計オブジェクトの作成が可能になり、後続のスキーマの変更が統計オブジェクトによってブロック "されなくなります"。ただし、代わりに、必要に応じて統計が削除されます。 このように、オート ドロップ が有効になっている手動で作成された統計は、自動作成された統計と同様に動作します。

たとえば、dbo.DatabaseLogテーブルに統計オブジェクトを手動で作成するには、次のようにします。

CREATE STATISTICS [mystats] ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser]) WITH AUTO_DROP = ON;

たとえば、dbo.DatabaseLogテーブルの統計オブジェクトのオート ドロップ設定を更新するには、次のようにしますす。

UPDATE STATISTICS [dbo].[DatabaseLog] [mystats] WITH AUTO_DROP = ON;

既存の統計の自動削除設定を評価するには、sys.stats の auto_drop 列を使用します。

SELECT object_id, [name], auto_drop
FROM sys.stats;

詳細については、「 CREATE STATISTICS (Transact-SQL)」をご覧ください。

INCREMENTAL

適用対象: SQL Server 2014 (12.x) 以降。

CREATE STATISTICS の INCREMENTAL オプションが ON の場合、作成される統計情報はパーティションごとの統計になります。 OFF の場合、統計ツリーが削除され、SQL Server によって統計が再計算されます。 既定値は OFF です。 この設定は、データベース レベルの INCREMENTAL プロパティをオーバーライドします。 増分統計の作成の詳細については、「CREATE STATISTICS (Transact-SQL)」を参照してください。 パーティションごとの統計の自動作成に関する詳細については、「データベースのプロパティ (オプション ページ)」および「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。

大きなテーブルに新しいパーティションを追加した場合、新しいパーティションが含まれるように統計を更新する必要があります。 ただし、テーブル全体のスキャン (FULLSCAN または SAMPLE オプション) に要する時間は非常に長くなることがあります。 また、新しいパーティションに対する統計のみが必要となるため、テーブル全体をスキャンする必要はありません。 増分オプションでは、パーティションごとの統計が作成され格納されるため、更新時には、新しい統計を必要とするそれらのパーティションの統計のみを更新します。

パーティションごとの統計がサポートされていない場合、このオプションは無視され、警告が生成されます。 次の種類の統計では、増分統計がサポートされていません。

• ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。
• Always On の読み取り可能なセカンダリ データベースに対して作成された統計。
• 読み取り専用のデータベースに対して作成された統計。
• フィルター選択されたインデックスに対して作成された統計。
• ビューに対して作成された統計。
• 内部テーブルに対して作成された統計。
• 空間インデックスまたは XML インデックスを使用して作成された統計。

統計を作成する場合

クエリ オプティマイザーによって、既に次のようにして統計が作成されています。

1. インデックスの作成時に、クエリ オプティマイザーによってテーブルまたはビューのインデックスに対する統計が作成されます。 これらの統計は、インデックスのキー列について作成されます。 インデックスがフィルター選択されたインデックスの場合は、フィルター選択されたインデックスに指定された行のサブセットと同じ行のサブセットについて、フィルター選択された統計が作成されます。 フィルター選択されたインデックスの詳細については、「フィルター選択されたインデックスの作成」および「CREATE INDEX (Transact-SQL)」を参照してください。

   注意

   SQL Server 2014 (12.x) 以降では、パーティション インデックスが作成または再構築された場合、テーブル内のすべての行をスキャンして統計を作成することはできません。 代わりに、クエリ オプティマイザーが既定のサンプリング アルゴリズムを使用して統計を生成します。 パーティション インデックスでデータベースをアップグレードした後で、これらのインデックスのヒストグラム データに違いが見つかる場合があります。 この動作の変更はクエリ パフォーマンスに影響しない可能性があります。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成するには、FULLSCAN 句で CREATE STATISTICS または UPDATE STATISTICS を使用します。

2. AUTO_CREATE_STATISTICS がオンの場合、クエリ オプティマイザーによってクエリ述語内の列に対して 1 列ずつ統計が作成されます。

ほとんどのクエリでは、これらの 2 つの方法で作成された統計を使用すれば、高品質のクエリ プランになります。ただし、 CREATE STATISTICS ステートメントを使用して追加の統計を作成することで、クエリ プランが向上する場合もあります。 これらの追加の統計では、クエリ オプティマイザーでインデックスまたは 1 列ずつの統計を作成する場合には考慮されない統計的相関関係を取り込むことができます。 アプリケーションのテーブル データには、計算して統計オブジェクトに含めればクエリ オプティマイザーでクエリ プランを向上させることができる、他の統計的相関関係が含まれている場合があります。 たとえば、データ行のサブセットに関するフィルター選択された統計情報や、クエリ述語列の複数列統計を使用することで、クエリ プランが向上することがあります。

CREATE STATISTICS ステートメントを使用して統計を作成する場合、AUTO_CREATE_STATISTICS オプションを ON のままにし、クエリ述語列に対する 1 列ずつの統計がクエリ オプティマイザーによって通常どおり作成されるようにしておくことをお勧めします。 クエリ述語の詳細については、「検索条件 (Transact-SQL)」を参照してください。

次のいずれかに該当する場合は、CREATE STATISTICS ステートメントを使用して統計を作成することを検討してください。

• データベース エンジン チューニング アドバイザーによって統計の作成が提案される
• 相関関係にある複数の列がクエリ述語に含まれているが、それらがまだ同じインデックスに存在しない。
• データのサブセットから選択するクエリを使用する。
• クエリに統計がない。

相関関係にある複数の列がクエリ述語に含まれている

列間に相関関係や依存関係がある複数の列がクエリ述語に含まれている場合、複数列の統計を使用するとクエリ プランが向上することがあります。 複数列の統計には、 密度と呼ばれる列間の相関関係の統計が含まれます。これは、1 列ずつの統計では使用できません。 複数の列間のデータの相関関係によってクエリ結果が異なる場合、密度を使用するとカーディナリティの推定が向上します。

列が同じインデックスに既に存在する場合、複数列統計オブジェクトは既に存在するため、手動で作成する必要はありません。 列が同じインデックスにまだ存在しない場合は、列のインデックスを作成するか CREATE STATISTICS ステートメントを使用することによって、複数列統計を作成できます。 メンテナンスに必要なシステム リソースは、インデックスの方が統計オブジェクトよりも多くなります。 複数列のインデックスを必要としないアプリケーションの場合は、インデックスを作成せずに統計オブジェクトを作成すると、システム リソースを節約できます。

複数列統計を作成する場合、統計オブジェクト定義内の列の順序によって、カーディナリティの推定に密度を使用した場合の効果が変わります。 統計オブジェクトには、統計オブジェクト定義内のキー列の各プレフィックスの密度が格納されます。 密度の詳細については、このページの密度に関するセクションを参照してください。

カーディナリティの推定に効果的な密度を作成するには、クエリ述語内の列が、統計オブジェクト定義内の列のいずれかのプレフィックスに一致する必要があります。 たとえば、次の例では、列 LastName、MiddleName、FirstName で複数列統計オブジェクトが作成されます。

USE AdventureWorks2022;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO

この例では、統計オブジェクト LastFirst に、列プレフィックス ((LastName))、((LastName, MiddleName))、および ((LastName, MiddleName, FirstName)) の密度が格納されています。 ((LastName, FirstName)) の密度は使用できません。 LastName を使用せずに FirstName と MiddleName を使用したクエリの場合は、カーディナリティの推定に密度を使用することはできません。

データのサブセットから選択するクエリを使用する

クエリ オプティマイザーでは、1 列ずつおよびインデックスに対して統計を作成する際、すべての行の値に対する統計を作成します。 行のサブセットから選択するクエリの場合、その行のサブセットのデータ分布が一意であれば、フィルター選択された統計情報を使用することでクエリ プランを向上させることができます。 フィルター選択された統計情報は、CREATE STATISTICS ステートメントを WHERE 句と共に使用してフィルター述語の式を定義することで作成できます。

たとえば、AdventureWorks2022を使用する場合、 Production.Productテーブルの各製品は、 Production.ProductCategoryテーブルの 4 つのカテゴリ (Bikes、Components、Clothing、および Accessories) のいずれかに属しています。 各カテゴリでは、重量に関するデータ分布が異なります。自転車の重量は 13.77 ～ 30.0、部品の重量は 2.12 ～ 1050.00 (一部 NULL 値)、衣類の重量はすべて NULL、付属品の重量も NULL です。

たとえば Bikes の場合、自転車のすべての重量についてのフィルター選択された統計情報を使用すると、テーブル全体の統計情報を使用する場合や、Weight 列の統計情報が存在しない場合と比べて、より正確な統計情報がクエリ オプティマイザーに提供され、クエリ プランの品質が向上します。 自転車の重量の列は、フィルター選択された統計情報には適していますが、重量の参照が比較的少ない場合、フィルター選択されたインデックスには必ずしも適しているとは限りません。 フィルター選択されたインデックスを使用することで得られる参照のパフォーマンスの向上よりも、フィルター選択されたインデックスをデータベースに追加するためのメンテナンス コストとストレージ コストの増加の方が大きい場合があります。

次のステートメントでは、Bikes のすべてのサブカテゴリについてのフィルター選択された統計 BikeWeights を作成します。 フィルター選択された述語式で、比較 Production.ProductSubcategoryID IN (1,2,3)を使用して自転車のすべてのサブカテゴリを列挙することで、自転車を定義しています。 Bikes カテゴリは Production.ProductCategory テーブルに格納されているため、述語でそのカテゴリ名を使用することはできません。フィルター式に含まれるすべての列が、同じテーブル内に存在する必要があります。

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

クエリ オプティマイザーでは、 BikeWeights というフィルター選択された統計情報を使用して、重量が 25を超えるすべての自転車を選択する次のクエリのクエリ プランを向上させることができます。

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

統計がないことをクエリで識別する

クエリ オプティマイザーでは、エラーやその他のイベントによって統計を作成できない場合、統計を使用せずにクエリ プランを作成します。 クエリ オプティマイザーでは存在しない統計をマークし、次回のクエリの実行時に再生成しようとします。

統計が存在しない場合は、SQL Server Management Studio を使用してクエリの実行プランをグラフィカルに表示すると、警告 (赤色のテーブル名) が表示されます。 また、SQL Server Profiler を使用して Missing Column Statistics イベント クラスを監視すると、統計がない場合はそのことがわかります。 詳細については、「Errors and Warnings イベント カテゴリ (データベース エンジン)」を参照してください。

統計がない場合は、次の手順を実行します。

• AUTO_CREATE_STATISTICS と AUTO_UPDATE_STATISTICS が ON になっていることを確認します。
• データベースが読み取り専用ではないことを確認します。 データベースが読み取り専用の場合は、新しい統計オブジェクトを保存できません。
• 存在しない統計を CREATE STATISTICS ステートメントを使用して作成します。

読み取り専用データベースまたは読み取り専用スナップショットに関する統計が欠落しているか、古くなっている場合、データベース エンジンは、tempdbに一時的な統計を作成して維持します。 データベース エンジンが一時的な統計を作成する場合、一時的な統計と永続的な統計とを区別するためのサフィックス _readonly_database_statistic が統計名に付加されます。 サフィックス _readonly_database_statistic は、SQL Server によって生成される統計用に予約されています。 読み書き可能なデータベースでは、一時的な統計用のスクリプトを作成して再現できます。 スクリプトを作成する場合 Management Studioでは、統計名のサフィックスを _readonly_database_statistic から _readonly_database_statistic_scripted に変更します。

一時的な統計を作成して更新できるのは、 SQL Server のみです。 ただし、永続的な統計の場合と同じツールを使用すると、一時的な統計を削除して、統計のプロパティを監視できます。

• DROP STATISTICS ステートメントを使用して一時的な統計を削除します。
• sys.stats カタログ ビューと sys.stats_columns カタログ ビューを使用して統計を監視します。 sys.stats システム カタログ ビューには、どの統計が一時的または永続的なものかを示すための is_temporary 列が含まれています。

一時的な統計は tempdbに格納されるので、 SQL Server サービスを再起動すると、一時的な統計はすべてなくなります。

統計を更新する場合

クエリ オプティマイザーでは、古くなっている可能性がある統計を判断し、それらがクエリ プランに必要な場合は更新します。 場合によっては、AUTO_UPDATE_STATISTICS をオンにした場合よりも頻繁に統計を更新すると、クエリ プランが向上し、クエリのパフォーマンスが向上することがあります。 統計は、UPDATE STATISTICS ステートメントまたはストアド プロシージャ sp_updatestats を使用して更新できます。

統計を更新すると、クエリが最新の統計を使用してコンパイルされるようになります。 任意のプロセスを使用して統計を更新すると、クエリ プランが自動的に再コンパイルされる場合があります。 パフォーマンスの向上を目的とする場合、クエリ プランの改善とクエリの再コンパイルに要する時間の間にはトレードオフの関係があるため、あまり頻繁に手動で統計を更新しないようにすることをお勧めします。 実際のトレードオフはアプリケーションによって異なります。

UPDATE STATISTICS または sp_updatestats を使用して統計を更新する場合、AUTO_UPDATE_STATISTICS を ON に設定したままにして、クエリ オプティマイザーによって通常どおり統計が更新されるようにすることをお勧めします。

• 列、インデックス、テーブル、またはインデックス付きビューの統計を更新する方法については、「UPDATE STATISTICS (Transact-SQL)」を参照してください。

• データベース内のすべてのユーザー定義および内部テーブルの統計を更新する方法については、ストアド プロシージャ sp_updatestats (Transact-SQL) の説明を参照してください。

• 統計の自動更新のしきい値に関する詳細については、「AUTO_UPDATE_STATISTICS オプション」を参照してください。

AUTO_UPDATE_STATISTICS が OFF に設定されている場合でも、他のさまざまな理由によってプランの再コンパイルが発生する可能性がありますが、古い統計の更新によって自動的に発生することはありません。 AUTO_UPDATE_STATISTICS が OFF に設定されている場合は、メンテナンス プランなど、手動でスケジュール設定された他のプロセスによってのみ、統計の更新が行われます。 したがって、AUTO_UPDATE_STATISTICS を OFF に設定すると、最適ではないクエリ プランが作成されて、クエリのパフォーマンスが低下することがあります。

古くなった統計情報の検出

統計の最終更新日を確認するには、sys.dm_db_stats_properties または STATS_DATE 関数を使用します。

次のような場合は、統計を更新することを検討してください。

• クエリの実行に時間がかかる。
• 昇順または降順のキー列に対して挿入操作を実行する。
• メンテナンス操作の実行後。

統計を手動で更新する例については、「UPDATE STATISTICS (Transact-SQL)」を参照してください。

クエリの実行に時間がかかる

クエリの応答時間が遅い場合や予測できない場合は、他のトラブルシューティング手順を実行する前に、クエリの統計が最新のものであることを確認してください。

昇順または降順のキー列に対して挿入操作を実行する

昇順または降順のキー列 (IDENTITY 列や実時間のタイムスタンプ列など) の統計では、クエリ オプティマイザーで実行されるよりも頻繁に統計の更新が必要になる場合があります。 挿入操作によって昇順または降順の列に新しい値が追加された場合に、 追加された行数が少なすぎると、統計の更新が実行されないことがあります。 統計が最新ではない場合に、追加された最新の行から選択するクエリを実行すると、現在の統計にそれらの新しい値のカーディナリティの推定が含まれません。 その結果、カーディナリティの推定が不正確になり、クエリのパフォーマンスが低下することがあります。

たとえば、最新の販売注文日から選択するクエリで、統計が最新の販売注文日のカーディナリティの推定を含むように更新されていないと、カーディナリティの推定が不正確になります。

メンテナンス操作の実行後

テーブルの切り捨てや大部分の行の一括挿入を行うなど、データの分布が変わるメンテナンス操作を実行した後は、統計を更新することを検討してください。 これにより、統計の自動更新を待つことによってクエリ処理で発生する以降の遅延を回避することができます。

インデックスの再構築、デフラグ、再構成などの操作では、データの分布は変わりません。 そのため、ALTER INDEX REBUILD、DBCC DBREINDEX、DBCC INDEXDEFRAG、または ALTER INDEX REORGANIZE の各操作を実行した後に統計を更新する必要はありません。 ALTER INDEX REBUILD または DBCC DBREINDEX を使用してテーブルまたはビューのインデックスを再構築した場合、クエリ オプティマイザーによって統計が更新されますが、この統計の更新はインデックスを再作成する過程で実行されるものです。 DBCC INDEXDEFRAG 操作または ALTER INDEX REORGANIZE 操作の後は、クエリ オプティマイザーで統計は更新されません。

インデックスと統計の自動管理

Adaptive Index Defrag のようなソリューションを活用し、1 つまたは複数のデータベースに対するインデックスの最適化と統計更新を自動管理します。 このプロシージャでは、断片化レベルやその他のパラメーターに基づいてインデックスを再構築または再構成するか、線形しきい値で統計を更新するかが自動的に選択されます。

統計を効果的に使用するクエリ

クエリ述語にローカル変数や複雑な式が含まれている場合など、特定のクエリ実装では、最適なクエリ プランにならないことがあります。 クエリのデザイン ガイドラインに従って統計を効果的に使用することで、この問題を回避できる場合があります。 クエリ述語の詳細については、「検索条件 (Transact-SQL)」を参照してください。

クエリのデザイン ガイドラインを適用して統計を効果的に使用することで、クエリ述語で使用される式、変数、および関数に対する カーディナリティの推定 を向上させると、クエリ プランを向上させることができます。 クエリ オプティマイザーでは、式、変数、または関数の値が不明な場合、ヒストグラムで参照する値を特定できないため、ヒストグラムから最適なカーディナリティの推定を得ることができません。 その場合、クエリ オプティマイザーでは、ヒストグラム内のサンプリングされたすべての行の値ごとの平均行数に基づいてカーディナリティの推定を行います。 その結果、カーディナリティが適切に推定されず、クエリのパフォーマンスが低下することがあります。 ヒストグラムの詳細については、このページの「ヒストグラム」のセクション、または「sys.dm_db_stats_histogram」をご覧ください。

以下のガイドラインでは、カーディナリティの推定を向上させることによってクエリ プランを改善するためのクエリの作成方法について説明します。

式に対するカーディナリティの推定を向上させる

式に対するカーディナリティの推定を向上させるには、次のガイドラインに従います。

• 定数を含む式は可能な限り単純にします。 クエリ オプティマイザーでは、カーディナリティの推定を判断する前に、定数を含むすべての関数および式の評価は行われません。 たとえば、式 ABS(-100) を 100 に簡略化します。
• 式で複数の変数を使用している場合は、式の計算列を作成し、その計算列に対する統計またはインデックスを作成することを検討します。 たとえば、クエリ述語 WHERE PRICE + Tax > 100 のカーディナリティの推定は、式 Price + Tax に対する計算列を作成すると向上する可能性があります。

変数および関数に対するカーディナリティの推定を向上させる

変数および関数に対するカーディナリティの推定を向上させるには、次のガイドラインに従います。

• クエリ述語でローカル変数を使用している場合は、ローカル変数の代わりにパラメーターを使用してクエリを書き換えることを検討します。 ローカル変数の値は、クエリ オプティマイザーでのクエリ実行プランの作成時には認識されません。 クエリでパラメーターを使用すると、クエリ オプティマイザーで、ストアド プロシージャに渡される最初の実際のパラメーター値に対するカーディナリティの推定が使用されます。

• 複数ステートメントのテーブル値関数 (mstvf) の結果を格納する場合は、標準のテーブルか一時テーブルを使用することを検討します。 クエリ オプティマイザーでは、複数ステートメントのテーブル値関数の統計は作成されません。 この方法を使用すると、クエリ オプティマイザーでテーブル列の統計を作成できるため、それを使用することでクエリ プランを向上させることができます。

• テーブル変数の代わりに標準のテーブルか一時テーブルを使用することを検討します。 クエリ オプティマイザーでは、テーブル変数の統計は作成されません。 この方法を使用すると、クエリ オプティマイザーでテーブル列の統計を作成できるため、それを使用することでクエリ プランを向上させることができます。 一時テーブルとテーブル変数のどちらを使用するかの判断には、トレードオフの関係があります。ストアド プロシージャでテーブル変数を使用すると、ストアド プロシージャの再コンパイルの回数が、一時テーブルを使用した場合よりも少なくなります。 アプリケーションによっては、テーブル変数の代わりに一時テーブルを使用しても、パフォーマンスが向上しない場合もあります。

• 渡されたパラメーターを使用するクエリがストアド プロシージャに含まれている場合は、パラメーター値がクエリで使用される前にストアド プロシージャ内で変更されないようにします。 クエリに対するカーディナリティの推定は、更新された値ではなく渡されたパラメーターの値に基づいて行われます。 パラメーター値が変更されないようにするには、2 つのストアド プロシージャを使用するようにクエリを書き換えます。

  たとえば、次のストアド プロシージャ Sales.GetRecentSales では、@date が NULL の場合にパラメーター @date の値を変更します。

  USE AdventureWorks2022;
  GO
  IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
  AS BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END
  GO
  

  ストアド プロシージャ Sales.GetRecentSales の最初の呼び出しで @date パラメーターに NULL が渡された場合、クエリ オプティマイザーでは、クエリ述語が @date = NULL で呼び出されていなくても、 @date = NULLに対するカーディナリティの推定を使用してストアド プロシージャをコンパイルします。 このカーディナリティの推定は、実際のクエリ結果の行数と大きく異なる場合があります。 そのため、クエリ オプティマイザーにより、最適なクエリ プランが選択されないことがあります。 この問題を回避するには、ストアド プロシージャを次のように 2 つのプロシージャに書き換えます。

  USE AdventureWorks2022;
  GO
  IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      EXEC Sales.GetNonNullRecentSales @date;
  END
  GO
  IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
  AS BEGIN
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END
  GO
  

クエリ ヒントを使用してカーディナリティの推定を向上させる

ローカル変数に対するカーディナリティの推定を向上させるには、RECOMPILE を指定して OPTIMIZE FOR <value> または OPTIMIZE FOR UNKNOWN クエリ ヒントを使用します。 詳細については、「 クエリ ヒント (Transact-SQL)」を参照してください。

アプリケーションによっては、クエリを実行するたびに再コンパイルすると時間がかかりすぎる場合がありますが、 OPTIMIZE FOR クエリ ヒントは RECOMPILE オプションを使用しなくても役立つことがあります。 たとえば、ストアド プロシージャ Sales.GetRecentSales に OPTIMIZE FOR オプションを追加して、特定の日付を指定することができます。 Sales.GetRecentSales プロシージャに OPTIMIZE FOR を追加した例を次に示します。

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

プラン ガイドを使用してカーディナリティの推定を向上させる

アプリケーションによっては、クエリを変更できない場合や、RECOMPILE クエリ ヒントを使用すると再コンパイルが多くなりすぎる場合など、クエリのデザイン ガイドラインが該当しないことがあります。 プラン ガイドを使用すると、アプリケーション ベンダーによるアプリケーションの違いを確認しながら、その他のヒント (USE PLAN など) を指定してクエリの動作を制御することができます。 プラン ガイドの詳細については、「 Plan Guides」を参照してください。

Azure SQL Database では、プラン ガイドではなく、プランを強制するクエリ ストアのヒントを検討してください。 詳細については、「クエリ ストアのヒント」を参照してください。

関連項目

• メモリ最適化テーブルの統計
• CREATE STATISTICS (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• ALTER DATABASE SET のオプション (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• フィルター選択されたインデックスの作成
• SQL Server で Autostat (AUTO_UPDATE_STATISTICS) 動作を制御する
• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats
• sys.stats_columns (Transact-SQL)

次のステップ

• Adaptive Index Defrag (Microsoft SQL Server Tiger チームのツールボックスより)