Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報

  • [アーティクル]
このページはアーカイブです。記載されている内容は情報提供のみを目的としており、ページ内のリンクは有効でない可能性がありますが、これらの情報についてマイクロソフトはいかなる責任も負わないものとします。

公開日: 2005年10月25日

著者 : Eric N. Hanson  投稿者 : Lubor Kollar

Microsoft® SQL Server™ 2005 は、データベースに格納されているインデックスと列データに関する統計情報を収集します。SQL Server クエリ オプティマイザはこれらの統計情報を使用して、データを取得または更新するための最も効率的なプランを選択します。このペーパーでは、収集されるデータの種類とそれらのデータが格納される場所、そして、統計情報を作成、更新、および削除するコマンドについて説明します。また、既定では、SQL Server 2005 は統計情報を作成および更新する操作が有益だと見なした場合に、それらの操作を自動的に実行します。また、このペーパーでは、これらの既定の動作を異なるレベル (列、テーブル、およびデータベース) で変更する方法についても説明します。

トピック

SQLServer 2005 の統計データ
統計情報機能の概要
SQL Server 2005 の統計情報に関する新機能
用語の意味
SQL Server 2005 が収集する統計情報
統計情報の作成と表示 : 例
SQL Server 2005 を使用した統計情報の作成
SQL Server 2005 における統計情報の維持
文字列サマリーの統計情報
計算列に関する統計情報
CLR ユーザー定義型列に関する統計情報
統計情報とインデックス付きビュー
統計情報の管理に関するベスト プラクティス
まとめ
関連情報

SQLServer 2005 の統計データ

Microsoft® SQL Server™ 2005 は、個々の列 (単一列の統計情報) または列セット (複数列の統計情報) に関する統計情報を収集します。クエリ オプティマイザはこれらの統計情報を使用して式の選択度を推定し、それに応じて中間および最終のクエリ結果のサイズを推定します。統計情報が良好であれば、オプティマイザはさまざまなクエリ プランのコストを正確に見積もり、高品質のプランを選択できます。単一の統計情報オブジェクトに関する情報はすべて、sysindexes テーブル内の単一の行の複数の列と、内部専用テーブルに保持されている統計情報バイナリ ラージ オブジェクト (statblob) に格納されます。また、新しいメタデータ ビューである sys.stats および sys.indexes にも、統計情報に関する情報が格納されます。

統計情報機能の概要

SQL Server 2005 は、統計情報を維持管理するための機能をいくつか備えています。その中で最も重要なのが、統計情報を自動的に作成および更新する機能です。この機能は SQL Server 2005 および SQL Server 2000 では既定でオンにされています。SQL Server 2000 のインストール事例のうちおよそ 98% では、この機能が有効にされたままになっており、ベスト プラクティスとなっています。大多数のデータベースおよびアプリケーションでは、開発者や管理者は統計情報の自動作成および更新を利用することで、各自のデータに関して総合的で正確な統計情報を提供できます。このため、開発や管理のコストを低く抑えつつ、SQL Server 2005 クエリ オプティマイザによって良好なクエリ プランを継続的に生成することが可能になります。良好なクエリ プランを取得して統計情報の収集のオーバーヘッドを管理するために、統計情報の作成と更新についてさらに詳細に制御する必要がある場合は、手動による統計情報の作成および更新の機能を使用できます。

スループットの高いオンライン トランザクション処理アプリケーション環境を実現するうえで重要な新機能として、統計情報を自動的に非同期に更新する機能があります。この機能を使用すると、そのような環境におけるクエリの応答時間の予測能力を向上できます。

SQL Server 2005 の統計情報機能を使用すると、以下を実行できます。

  • 既定のサンプリング レートによる統計情報の自動作成および更新 (SELECT、INSERT、および DELETE コマンドでは、統計情報の自動作成および更新が有効にされているときに、WHERE 句や JOIN 句などのクエリ条件の中で列を使用した場合に、必要に応じて統計情報が作成または更新されます)

  • 任意の必要なサンプリング レートによる統計情報の手動作成および更新と、統計情報の削除 (CREATE STATISTICS、UPDATE STATISTICS、DROP STATISTICS、CREATE INDEX、DROP INDEX)

  • データベースにあるすべてのテーブルのすべての列に対する統計情報の手動一括作成 (sp_createstats)

  • データベースにあるすべての既存の統計情報の手動更新 (sp_updatestats)

  • テーブルまたはデータベースに対して存在する統計情報オブジェクトの列挙 (sp_helpstats、カタログ ビュー sys.stats、sys.stats_columns)

  • 統計情報オブジェクトに関する説明情報の表示 (DBCC SHOW_STATISTICS)

  • データベース全体、あるいは特定のテーブルまたは統計情報オブジェクトに対する、統計情報の自動作成および更新の有効化および無効化 (ALTER DATABASE のオプション AUTO_CREATE_STATISTICS および AUTO_UPDATE_STATISTICS、sp_autostats、CREATE STATISTICS および UPDATE STATISTICS の NORECOMPUTE オプション)

  • 統計情報の自動非同期更新の有効化および無効化 (ALTER DATABASE のオプション AUTO_UPDATE_STATISTICS_ASYNC)

この他、SQL Server Management Studio を使用して、[オブジェクト エクスプローラ] ビューの中で統計情報をグラフィカルに参照し管理することもできます。オブジェクト エクスプローラでは、統計情報が各テーブル オブジェクト配下のフォルダに一覧表示されます。

SQL Server 2005 の統計情報に関する新機能

SQL Server 2005 はいくつかの新しい統計情報機能を備えています。クエリ オプティマイザはこれらの機能を使用して、より広範囲なクエリに対して、より適切なクエリ プランを選択したり、選択ができない場合でも統計情報の管理を向上したりできます。以下の機能が強化されています。

  • 文字列サマリーの統計情報   文字から成る列について、部分文字列の頻度分布に関する情報が維持されます。オプティマイザはこの統計情報を参考にして、LIKE 演算子を使用している条件の選択度を適切に推定します。

  • 統計情報の自動非同期更新   ALTER DATABASE のオプション AUTO_UPDATE_STATISTICS_ASYNC は SQL Server 2005 の新しいオプションです。このオプションは既定でオフにされています。このオプションを有効にすると、SQL Server 2005 によって統計情報の自動更新がバックグラウンドで実行されます。統計情報の更新の原因となったクエリがブロックされずに、古い統計情報で処理が進められます。これにより、一部の作業負荷についてクエリ応答時間の予測能力が向上します。

  • 計算列の統計情報   計算列について、統計情報を手動または自動で、作成および更新できます (この機能は SQL Server 2000 で一部サポートされていましたが、文書化はされていませんでした)。

  • ラージ オブジェクトのサポート   ntext、text、image の各型の列に加えて、新しい型である nvarchar(max)、varchar(max)、varbinary(max) の各列を、統計情報列として指定できます。

  • 統計情報の読み込みフレームワークの向上   オプティマイザでは、SQL Server 2000 と比較して統計情報の読み込みが内部的に向上しており、必要なすべての統計情報だけを読み込むため、最適化の結果の品質とパフォーマンスが向上します。

  • 計算列に関する統計情報を自動的に作成する機能の強化   計算列の式と等価な式がクエリに含まれている場合、SQL Server 2005 は、計算列に関する統計情報を必要に応じて自動作成できます。

  • 最低サンプル サイズ   最低 8 MB のデータ、またはそれよりも小さい場合はテーブルのサイズが、統計情報の収集時にサンプリングされるようになりました。

  • 統計情報数の制限の増加   テーブルごとに許容される列統計情報オブジェクトの数が 2,000 に増えました。さらに、249 のインデックス統計情報も格納できるため、テーブルごとに最大で 2,249 の統計情報が格納されます。

  • DBCC SHOW_STATISTICS の出力の改善   DBCC SHOW_STATISTICS で、あいまいさを避けるため、表示される統計情報オブジェクトについてその名前が表示されるようになりました。

  • 列変更カウンタに基づく統計情報の自動更新   SQL Server 2000 では、統計情報の更新は行変更の数によって判断されていました。SQL Server 2005 では変更が列レベルで追跡されます。このため、統計情報の更新を保証するのに十分な変更がまだ加えられていない列について、統計情報の自動更新を回避できます。

  • 内部テーブルに関す る統計情報   sys.internal_tables に列挙されているテーブルに関する統計情報が完全にサポートされています。これには、XML、フルテキスト インデックス、Service Broker のキュー、およびクエリ通知テーブルが含まれます。

  • DBCC SHOW_STATISTICS での単一行セットの出力   DBCC SHOW_STATISTICS で、ヘッダー、密度ベクトル、およびヒストグラムを単一行セットとして個別に出力するオプションがサポートされています。このため、DBCC SHOW_STATISTICS の出力を自動的に処理する際のプログラミングが簡単になります。

  • 最大 32 個の列に関する統計情報   統計情報オブジェクト内の列数の制限が 16 個から 32 個に増えました。

  • パーティション テーブルに関する統計情報   パーティション テーブルに関する統計情報が完全にサポートされています。このサポートは SQL Server 2005 で新しく導入されたものです。ヒストグラムは、パーティション単位ではなくテーブル単位で維持されます。

  • fullscan における統計情報の並列収集   fullscan を使用して統計情報を収集する場合、単一の統計情報オブジェクトの作成を、非パーティション テーブルとパーティション テーブルの両方について並列に実行できます。

  • 統計情報が欠落している場合の再コンパイルと統計情報作成の向上   統計情報の自動作成が失敗した場合に、以降、欠落している統計情報を使用して生成されたプランが実行される時点で、自動作成が実行され、プランが再コンパイルされます。統計情報の欠落の状態が長く続くことはありません。詳細については、「SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題」ホワイト ペーパー (Marathe 著) を参照してください。

  • 空のテーブルに対する再コンパイル ロジックと統計情報更新の向上   テーブル内の行数が 0 から 0 よりも大きい値に変化した場合、クエリの再コンパイルと統計情報の更新が実行されます。詳細については、「SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題」ホワイト ペーパー (Marathe 著) を参照してください。

  • ヒストグラム表示の明確さと一貫性の向上   ヒストグラムはカタログに格納される前に常にサイズが調整されることから、DBCC SHOW_STATISTICS が改善されています。

  • 推定日の関連付けの制約   DATE_CORRELATION_OPTIMIZATION データベース設定を有効にすると、外部キーによってリンクされているテーブルのペアどうしで日付時刻フィールド間の関連付けに関する情報を保持するよう、SQL Server を設定できます。この情報を使用することで、一部のクエリで暗黙の述語を決定できるようになります。この情報はオプティマイザによる選択度の推定やコスト計算に直接使用されることはありません。そのため、厳密な意味では統計情報ではありませんが、より良好なクエリ プランの取得に役立つ補助的な情報であることから、統計情報と密接に関連しています。

  • sp_updatestats   Microsoft SQL Server 2005 では、sp_updatestats は、sys.sysindexes 互換性ビューの rowmodctr 情報に基づいた更新を必要とする統計情報だけを更新します。したがって、変更されていない項目の不必要な更新がなくなります。90 以上の互換性レベルで稼動しているデータベースでは、どの特定のインデックスまたは統計情報に対しても、sp_updatestats において UPDATE STATISTICS の自動設定が保持されます。

上記以外にも、統計情報の動作に関していくつかの小さな変更が加えられています。特に、sys.sysindexes 内の statblob 列は常に NULL として与えられるようになりました。statblob 自身は内部専用のカタログ テーブルに保持されます。

用語の意味

ここでは、SQL Server 2005 の統計情報に関連する以下の用語を定義します。

  • statblob   統計情報バイナリ ラージ オブジェクト。このオブジェクトは内部カタログ ビュー sys.sysobjvalues 内のシステム カタログに格納されます。

  • 文字列サマリー   文字列の列内の部分文字列について、その頻度分布を要約した統計情報の形式。LIKE 述語の選択度を推定する際に役立ちます。文字列サマリーは文字列の列用の statblob に格納されます。

  • sysindexes   sys.sysindexes カタログ ビュー。テーブルとインデックスに関する情報が収められます。

  • 述語   True または False に評価される条件。述語はデータベース クエリの WHERE 句または JOIN 句に出現します。

  • 選択度   述語の入力セットから得られ、その述語を満たす行の一部。より高度な方法を使用して選択度を測定し、結合や DISTINCT やその他の演算子により生成される行の数を推定する場合もあります。たとえば、SQL Server 2005 は AdventureWorks データベース内の述語 "Sales.SalesOrderHeader.OrderID = 43659" の選択度を 1/31465 = 0.00003178 と推定します。

  • 基数推定値   結果セットのサイズの推定値。たとえば、テーブル T に 100,000 個の行があり、クエリに T.a=10 という形式の選択述語が含まれていて、ヒストグラムにより T.a=10 の選択度が 10% と示される場合、T の行の一部に対してクエリによって判断される基数推定値は、10% * 100,000 = 10,000 となります。

  • LOB   ラージ オブジェクト。text、image、nvarchar(max)、varchar(max)、varbinary(max) の各型の値。

SQL Server 2005 が収集する統計情報

SQL Server 2005 は以下の情報をテーブル レベルで維持しています。これらは統計情報オブジェクトの一部ではありませんが、SQL Server 2005 はクエリ コストの推定時にこれらの統計情報を使用する場合があります。

  • テーブルまたはインデックス内の行数 (sys.sysindexes 内の rows 列)

  • テーブルまたはインデックスが占めるページ数 (sys.sysindexes 内の dpages 列)

SQL Server 2005 はテーブル列に関する以下の統計情報を収集して、統計情報オブジェクト statblob に格納します。

  • 統計情報が収集された時刻

  • ヒストグラムと密度情報の生成に使用された行数 (後述の説明を参照)

  • 平均キー長

  • 単一列ヒストグラム (ステップ数を含む)

  • 文字列サマリー (列に文字データが含まれている場合)。DBCC SHOW_STATISTICS の出力には列 "String Index (文字列インデックス)" があります。統計情報オブジェクトに文字列サマリーが含まれている場合に、この値が YES になります。

ヒストグラムとは、対象の列から得られる最大 200 個の値のセットのことです。対象の列にある値のすべて、またはそのサンプルは、並べ替えられます。統計的に最も重要な情報が取得されるように、並べ替え後の並びは最大 199 個の間隔に分割されます。一般に、これらの間隔は 0 以外のサイズです。以下の値、またはこれらの値を派生するのに十分な情報が、ヒストグラムの各ステップによって格納されます。

RANGE_HI_KEY

ヒストグラム ステップの上側の境界を示すキー値。

RANGE_ROWS

範囲の内側に収まる行の数を指定します。RANGE_HI_KEY よりは小さくなりますが、以前に指定された、より小さな RANGE_HI_KEY よりは大きくなります。

EQ_ROWS

RANGE_HI_KEY とまったく等しくなる行の数を指定します。

AVG_RANGE_ROWS

範囲の内側に収まる、別個の値ごとの行の平均数。

DISTINCT_RANGE_ROWS

この範囲の内側に収まる別個のキー値の数を指定します。RANGE_HI_KEY よりも前の以前のキーと RANGE_HI_KEY 自身は含みません。

SQL Server 2005 のヒストグラムは単一列、つまり、統計情報オブジェクトのキー列セットにおける最初の列に対してのみ、作成されます。

SQL Server 2005 は、並べ替えられた列値のセットから、次の 3 つのステップでヒストグラムを作成します。

  • ヒストグラムの初期化   1 番目のステップでは、並べ替えられたセットの先頭から始まる値の並びが処理され、RANGE_HI_KEY、EQ_ROWS、RANGE_ROWS、および DISTINCT_RANGE_ROWS の範囲から最大 200 個の値が収集されます (RANGE_ROWS および DISTINCT_RANGE_ROWS はこのステップ中は常に 0 です)。すべての入力を使い果たすか、または 200 個の値が見つかると、1 番目のステップが終了します。

  • バケットのマージを伴うスキャン   2 番目のステップでは、統計情報キーの先頭列から続く個々の追加の値が処理されます。処理は並べ替えられた順序で行われ、以降の値はそれぞれ、最後の範囲に追加されるか、または新しい範囲が最後に作成されます (入力値が並べ替えられるためにこの可能性があります)。新しい範囲が作成された場合は、隣接する既存の範囲ペアの 1 つが単一の範囲に縮小されます。この範囲ペアは情報の喪失を最小限に抑えるために選択されます。範囲が縮小された後のステップ数は、このステップ全体を通じて 200 に保たれます。この方法は "maxdiff" ヒストグラムのバリエーションに基づくものです。

  • ヒストグラムの統合   3 番目のステップでは、相当量の情報が失われない場合に、より多くの範囲が縮小される可能性があります。したがって、200 を超える一意の値が列に存在する場合でも、ヒストグラムのステップ数が 200 未満になる可能性があります。

ヒストグラムがサンプルを使用して既に作成されている場合は、RANGE_ROWS、EQ_ROWS、DISTINCT_RANGE_ROWS、および AVG_RANGE_ROWS の値が推定され、したがって、それらが整数である必要はありません。

密度とは、対象の列または列の組み合わせにおける、重複の数に関する情報であり、別個の値の個数の逆数として計算されます。列を等価述語の中で使用している場合は、ヒストグラムから派生される密度を使用して、制限される行の数が推定されます。ヒストグラムはまた、非等価選択述語や結合、その他演算子の選択度を推定する場合にも使用されます。

統計情報が収集された時刻を示すタイムスタンプ、テーブル内の行数、ヒストグラムの生成のためにサンプリングされた行数、Density (密度) 情報と平均キー長、そしてヒストグラム自身以外に、単一列の統計情報には、統計情報列セットのプレフィックスを構成する列セットごとに、"All density (すべての密度)" の値が含まれます。この値は DBCC SHOW_STATISTICS で 2 番目の行セット出力に示されます。All density は、プレフィックス列セット内にある別個の値の個数の逆数を推定した値です。次の節ではこの例を示します。

メモ   dbcc show_statistics によって返される最初の行に含まれている Density の値は、RANGE_HI_KEY 以外の値をサンプリングしたすべての値の密度です。通常、RANGE_HI_KEY の値は、分布の中でより頻繁に出現する値です。そのため、表示される Density は、頻繁に出現しない値の密度に関して、潜在的に有益な情報を提供します。

1 つの列セットに対する複数列の統計情報は、統計情報定義の中の第 1 列に対する 1 つのヒストグラム、第 1 列に対する 1 つの密度値、および、各列 (第 1 列のみも含む) のプレフィックスの各組み合わせに対する All Density で構成されます。複数列の統計情報のセット (1 つのヒストグラムと 2 つ以上の密度値) はそれぞれ、統計情報の直前の更新のタイムスタンプ、統計情報の生成に使用されたサンプル内の行数、ヒストグラム内のステップ数、およびキーの平均長と一緒に、1 つの statblob に格納されます。文字列サマリーは、文字データが含まれている場合にのみ、第 1 列に対してだけ含まれます。

対象のテーブルで使用可能なすべての統計情報を一覧表示するには、sp_helpindex および sp_helpstats を使用します。sp_helpindex は、テーブルのすべてのインデックスを一覧表示します。sp_helpstats は、テーブルに関するすべての統計情報を一覧表示します。インデックスはそれぞれ、その列の統計情報も受け継いでいます。CREATE STATISTICS コマンドを使用して作成された統計情報は、同じ列に対して CREATE INDEX を使用して作成された統計情報と等価です。ただ 1 つの違いは、CREATE STATISTICS コマンドではサンプリングが既定で使用されるのに対して、CREATE INDEX コマンドではどのような場合でもインデックスに対するすべての行を処理する必要があるために統計情報が fullscan によって収集されます。

統計情報の作成と表示 : 例

次の例は、統計情報を自動と手動の両方で作成する方法と、統計情報に関する情報を一覧表示する方法を示しています。ここでは、すべてのコマンドではなくその一部について出力結果を紹介しています。SQL Server 2005 の動作を示す方が役立つ事例では、SQL Server 2005 によって生成される出力を示しています。この例を自分で実行することで、完全な出力を表示できます。

USE tempdb 
GO
-- Clean up objects from any previous runs.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO
-- Create a sample schema and table.
CREATE SCHEMA Person
GO
CREATE TABLE Person.Contact(
FirstName nvarchar(60), 
LastName nvarchar(60), 
Phone nvarchar(15), 
Title nvarchar(15)
)
GO
-- Populate the table with a few rows.
INSERT INTO Person.Contact 
   VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact 
   VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO
-- Show that there are no statistics yet on the Person.Contact table.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- Implicitly create statistics on LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- Show that statistics were automatically created on LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO

結果 :

statistics_name

statistics_keys

_WA_Sys_00000002_1B29035F

LastName

 
-- Create an index, which also creates statistics.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- Show that creating the index created an associated statistics object.
sp_helpstats N'Person.Contact', 'ALL'
GO

結果 :

statistics_name

statistics_keys

_WA_Sys_00000002_1B29035F

LastName

Phone

Phone

 
-- Create a multi-column statistics object on first and last name.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- Show that there are now three statistics objects on the table.
sp_helpstats N'Person.Contact', 'ALL'
GO

結果 :

statistics_name

statistics_keys

_WA_Sys_00000002_1B29035F

LastName

FirstLast

FirstName、LastName

Phone

Phone

 
-- Display the statistics for LastName.
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO

結果 :

統計情報のヘッダー情報 :

Name (名前)

Updated (更新時刻)

Rows (行数)

Rows Sampled (サンプリング行数)

Steps (ステップ数)

Density (密度)

Average key length (平均キー長)

String Index (文字列インデックス)

_WA_Sys_00000002_1B29035F

Mar 25 2005 11:21AM

5

5

4

0

13.6

YES

列セット プレフィックスと関連する密度および長さ :

All Density (すべての密度)

Average Length (平均長)

Columns (列)

0.25

13.6

LastName

ヒストグラム ステップ :

RANGE_HI_KEY

RANGE_ROWS

EQ_ROWS

DISTINCT_RANGE_ROWS

AVG_RANGE_ROWS

Andersen

0

2

0

0

Smith

0

1

0

1

Williams

0

1

0

1

Zhang

0

1

0

1

 
-- If you take the name of the statistics object displayed by 
-- the command above and subsitute it in as the second argument of 
-- DBCC SHOW_STATISTICS you can form a command like the following one 
--(the exact name of the automatically created statistics object 
-- will typically be different for you).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- Executing the above command illustrates that you can show statistics by 
-- column name or statistics object name.
GO
-- The following displays multi-column statistics. Notice the two
-- different density groups for the second rowset in the output.
DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

結果 (2 番目の行セットのみ ) :

列セット プレフィックスと関連する密度および長さ :

All density (すべての密度)

Average Length (平均長)

Columns (列)

0.3333333

11.6

FirstName

0.25

25.2

FirstName、LastName

より大きなテーブルについて、完全に作成されたヒストグラムを表示するには、次のコマンドを実行します。

USE AdventureWorks
-- Clean up objects from previous runs.
IF EXISTS (SELECT * FROM sys.stats 
           WHERE object_id = object_id('Sales.SalesOrderHeader')
           AND name = 'TotalDue')
DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO
CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO
DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

SQL Server 2005 を使用した統計情報の作成

SQL Server 2005 では、以下に示すいくつかの異なる方法で統計情報を作成できます。

  • AUTO_CREATE_STATISTICS が有効なときに (既定の設定)、SELECT、INSERT、UPDATE、および DELETE ステートメントの最適化の副作用として、単一列の統計情報がオプティマイザにより必要に応じて自動的に作成されます。

  • 上記の統計情報を明示的に生成するステートメントとして、SQL Server 2005 には 2 つの基本的なステートメントがあります。CREATE INDEX は、宣言されたインデックスを最初に生成し、インデックス キーを構成する列組み合わせに対する統計情報の 1 セットも作成します (ただし、他に含まれている列については作成しません)。CREATE STATISTICS は、対象の列または列の組み合わせに対する統計情報のみを生成します。

  • 上記以外に、統計情報やインデックスを作成するいくつかの方法があります。ただし、最終的にはどの方法でも上記の 2 つのコマンドのいずれかが発行されます。現在のデータベースにあるすべてのユーザー テーブルについて、必要なすべての列 (XML 列はすべて除く) の統計情報を作成するには、sp_createstats を使用します。既に統計情報オブジェクトが存在する列に対しては、新しい統計情報オブジェクトは作成されません。

  • 指定したデータベースにあるテーブルのインデックスを 1 つまたは複数再構築するには、dbcc dbreindex を使用します。

  • Management Studio では、テーブル オブジェクトの下にあるフォルダを展開し、[統計] フォルダを右クリックして、[新しい統計] をクリックします。

  • データベース チューニング アドバイザ (DTA) を使用してインデックスを作成します。

AdventureWorks.Person.Contact テーブルに対する CREATE STATISTICS コマンドの例を次に示します。

CREATE STATISTICS FirstLast2 ON Person.Contact(FirstName,LastName)
WITH SAMPLE 50 PERCENT

通常は、既定のサンプリングによる統計情報があれば良好なクエリ プランを十分生成できます。しかし、対象の列サンプル内の値がランダムではないなどの場合には、より大きなサンプル サイズによる統計情報の方がクエリの最適化に役立つことがあります。データが並べ替えられている場合や、クラスタ化されている場合は、サンプルがランダムでなくなることがあります。インデックスの作成や、データの読み込み先のヒープ構造が既に並べ替えまたはクラスタ化されていることが原因となって、並べ替えやクラスタ化が発生することがあります。より大きなサンプル サイズで最も頻繁に使用されるのは fullscan です。これは、fullscan によって最も正確な統計情報が提供されるためです。より大きなサンプル サイズによる統計情報を使用するかどうかは、統計情報の作成にどのくらいの時間がかかるかによって判断されます。

上記のコマンドでは、単一の 2 列統計情報オブジェクトが作成されます。この例ではテーブルが小さすぎるため、SAMPLE 50 PERCENT は無視され、fullscan が実行されます。サンプリングは主に、データの過度のスキャンを避け、1,024 以上のページ数 (8 MB) を持つテーブルとインデックスだけに作用するようにする場合に使用されます。

SQL Server 2005 では、統計情報はインデックスの作成時にすべてのインデックスに対して作成されます。SQL Server は、クエリのコンパイル時に単一列の統計情報を自動的に作成します。これらの統計情報は、オプティマイザが本来おおよその密度や分布を推定しなければならない列に対して、作成されます。ただし、これには次の例外があります。(1) データベースが読み取り専用のとき、(2) 未処理のコンパイルが想定以上に多数進行中であるとき、および (3) 列のデータ型が統計情報の自動作成に対応していないときは、統計情報が自動的に作成されない場合があります。

次を実行することで、統計情報の自動作成機能をデータベース レベルで無効にできます。

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

同様に、次を実行することで、統計情報の自動作成をデータベース レベルで有効にできます。

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

この設定はオンにしておくことをお勧めします。一部のテーブルで既定以外のサンプリング レートを指定する必要がある場合など、パフォーマンスに関する既知の問題を回避する場合にのみ、無効にしてください。

既定では、CREATE STATISTICS コマンドを実行するときや、統計情報が自動的に作成されるときに、データ セットをサンプリングすることによって統計情報が作成されます。CREATE INDEX はどのような場合でもデータ セット全体をスキャンします。したがって、最初にサンプリングなしでインデックスの統計情報が作成されます (fullscan に相当)。CREATE STATISTICS コマンドを使用すると、WITH 句で fullscan を指定するか、またはデータの百分率またはスキャンする行数を指定することによって、サンプル サイズを設定できます。後者の場合は近似として解釈されます。また、UPDATE STATISTICS コマンドで WITH RESAMPLE を指定するときに、以前のサンプル サイズを継承することも可能です。特に、ある列または列セットに対してはインデックスがあり (最初に fullscan 統計情報で作成されたもの)、他の列または列セットに対しては統計情報しかない (最初に SAMPLE 統計情報で作成されたもの) という場合に、この方法が便利です。UPDATE STATISTICS で resample オプションを使用すると、インデックスに対して fullscan による統計情報が保持され、残りの列に対してサンプルによる統計情報が保持されます。

小さいテーブルでは、最低 8 MB のデータがサンプリングされます。テーブルが最初は小さく、そのため既定のサンプリング レートでサンプリングし、その後統計情報を更新するときに resample オプションを使用した場合は、テーブルが 8 MB を超えた場合でも fullscan と同等の結果が得られます。テーブル サイズが変化しても既定のサンプリング レートを使用する必要がある場合は、resample の使用を避けてください。

resample のサンプリング レートは、サンプリングされる行数と、以前の統計情報計算時におけるテーブル内の行の総数との関数として計算されます。実際のサンプリング レートはサンプリングのランダムな性質上変化する場合があるため、resample のレートは fullscan でないサンプルに対する以前のサンプリング レートの近似にすぎません。一貫して反復可能なサンプリングを維持する場合は、resample を使用せず、以前に UPDATE STATISTICS で使用したものと同じサンプリング レートを明示的に指定してください。

dbcc show_statistics コマンドでは、Rows Sampled 見出しの下にサンプル サイズが表示されます。自動的に作成される統計情報や、自動的に更新される統計情報 (次節で説明します) は、常に既定のサンプリングを使用して生成されます。既定のサンプリング レートはテーブル サイズに対して緩やかに増加する関数であり、非常に大きなテーブルであっても比較的速く統計情報を収集できます。

統計情報が作成および更新される際、オプティマイザは統計情報を収集するためのアクセス パスを選択する必要があります。アクセス パスには、ヒープ、クラスタ化インデックス、または非クラスタ化インデックスが含まれる場合があります。サンプリングによる統計情報の場合は、オプティマイザは、先頭の統計情報キー列に関して物理的に並べ替えられたアクセス パスを避けることを試みます。このため、よりランダムなサンプルが得られるようになり、より正確な統計情報が導かれます。統計情報キーに関して並べ替えられていないアクセス パスの場合は、コストが最低のアクセス パスが選択されます。これは、範囲の最も狭いインデックスまたはヒープになります。fullscan による統計情報の場合は、アクセス パスの並べ替え順序は統計の正確さにとって重要ではないため、コストが最低のアクセス パスが選択されます。

SQL Server プロファイラは、統計情報の自動作成を監視できます。Auto Stats イベントは Performance トレース イベントのグループの中にあります。トレースを定義するときは、Auto Stats イベントに対して IntegerData、Success、ObjectID の各列も選択してください。AutoStats イベントが取得されると、Integer Data 列には、対象のテーブルに対して作成または更新された統計情報の数が示され、Object ID にはテーブルの ID が示されます。そして、TextData 列 (トレース定義の中に既定で含まれています) には、統計情報の作成または更新が実行された列の名前が、Updated または Created のどちらかのプレフィックスと共に示されます。Success 列には、Auto Stats 操作の成否が示されます。具体的には、Success は次の 3 つの値を示す可能性があります。

名前

値

定義

FAILED

0

Auto Stats による作成または更新が、THROTTLED (下を参照) 以外の理由で失敗しました。たとえば、データベースが読み取り専用でした。

SUCCESS

1

Auto Stats による作成または更新が成功しました。

THROTTLED

2

進行中の最適化の数が多すぎるために、Auto Stats による作成または更新が失敗しました。

場合によっては、統計情報がまったく作成または更新されない状況でも AutoStats イベントが発生することがあります。たとえば、auto update statistics がオフにされたときにそのようなイベントが生成されます。また、クエリ内で参照されているテーブルに対してかなりの数の変更が発生し、クエリの構造と外部キー制約の存在のために、オプティマイザがクエリ プランからそのテーブルへの参照をすべて削除できるようになったときにも、そのようなイベントが生成されます。

DROP STATISTICS コマンドは統計情報の削除に使用されますが、インデックスの副産物である統計情報を削除することはできません。このような統計情報はインデックスが削除されたときにのみ削除されます。

SQL Server 2005 における統計情報の維持

一連の INSERT、DELETE、UPDATE の任意の組み合わせがテーブルに対して実行された後に、対象の列またはインデックスにおける本当のデータ分布が統計情報に反映されないことがあります。SQL Server クエリ オプティマイザで、テーブル内の特定の列の統計情報が必要になったときに、その統計情報が作成または更新されて以降実質的な更新処理が進行している場合、SQL Server は (auto update statistics を使用して) 列の値をサンプリングすることによって、統計情報を自動的に更新します。統計情報の自動更新は、クエリの最適化またはコンパイル済みプランの実行によってトリガされ、クエリ内で参照されている列のサブセットのみが更新の対象になります。AUTO_UPDATE_STATISTCS_ASYNC がオフの場合はクエリのコンパイル前に統計情報が更新され、AUTO_UPDATE_STATISTCS_ASYNC がオンの場合は非同期に更新されます。

クエリが初めてコンパイルされるとき、オプティマイザで特定の統計情報オブジェクトが必要になり、その統計情報オブジェクトが存在する場合、その日付が古ければ統計情報オブジェクトが更新されます。クエリが実行されてそのプランがキャッシュに置かれると、プランで必要な統計情報がチェックされ、日付が古いかどうかが確かめられます。日付が古い場合はそのプランがキャッシュから削除され、クエリの再コンパイル時に統計情報が更新されます。また、プランで必要ないずれかの統計情報が変更された場合にも、プランがキャッシュから削除されます。

SQL Server 2005 は統計情報を更新する必要があるかどうかを、"列変更カウンタ" (colmodctrs) への変更に基づいて判断します。

以下の場合に、統計情報オブジェクトの日付が古いものと見なされます。

  • 標準的なテーブルで統計情報が定義されている場合は、以下の場合に日付が古いと見なされます。

    1. テーブル内の行数が 0 から 0 よりも大きい値に変化した場合

    2. 統計情報が収集された時点でのテーブル内の行数が 500 以下で、それ以降、統計情報オブジェクトの先頭列の colmodctr が変化して 500 を超えた場合

    3. 統計情報が収集された時点でテーブルに 500 の行があり、統計情報オブジェクトの先頭列の colmodctr が変化して 500 + (統計情報が収集された時点でのテーブル内の行数の 20%) を越えた場合

  • 統計情報オブジェクトが一時テーブルに対して定義されている場合も、上記で説明したように日付が古いと見なされます。ただし、上のリストの 2 と同じ条件で、6 行目に再計算用の追加のしきい値が存在します。

テーブル変数には統計情報はまったく存在しません。

上記で説明した auto update statistics 機能は、さまざまなレベルでオフにすることもできます。

  • データベース レベルでは、次を使用して auto update statistics を無効にします。

    ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

  • テーブル レベルでは、UPDATE STATISTICS コマンドまたは CREATE STATISTICS コマンドの NORECOMPUTE オプションを使用して auto update statistics を無効にします。

  • テーブル、インデックス、または統計情報オブジェクトに対する auto update statistics の設定を表示および変更するには、sp_autostats を使用します。

同様に、ALTER DATABASE、UPDATE STATISTICS、または sp_autostats を使用して、統計情報の自動更新を再び有効にできます。

SQL Server 2005 では、データベース単位、テーブル単位、そしてインデックス単位または統計情報オブジェクトのレベルで、統計情報の自動更新設定が維持されます。単一の sp_autostats コマンドを使用して 1 つのテーブルのすべての統計情報に対してこの設定を変更することもできますが、実際には、対象のテーブルの統計情報オブジェクトおよびインデックスごとに設定が個別に変更されます。テーブル全体に対して統計情報の自動更新がオンまたはオフであることを明示的に記録するメタデータはありません。

次の表は、データベース、テーブル、およびインデックスの各設定の組み合わせによる効果を示したものです。

データベース設定

インデックス設定または統計情報オブジェクト設定

統計情報の自動更新がオブジェクトに対して有効

オン

オン

オン

オン

オフ

オフ

オフ

オン

オフ

オフ

オフ

オフ

統計情報の自動更新に対してオフになっているデータベース設定を、統計情報オブジェクトのレベルでオンにすることによってその設定を無効にすることはできません。

統計情報の自動更新は、既定のサンプリング レートを使用してインデックスまたはテーブルをサンプリングすることによって常に実行されます。サンプリング レートを明示的に設定するには、CREATE STATISTICS または UPDATE STATISTICS を実行します。

統計情報の更新は統計情報の作成と同じ SQL プロファイラ イベントによって制御されます。

文字列サマリーの統計情報

SQL Server 2005 には、LIKE 条件の選択度を推定するためのテクノロジが組み込まれています (このテクノロジは特許を受けています)。これは、文字から成る列に対して部分文字列の頻度分布の統計的な要約 (文字列サマリー) を構築するものです。この列には、text、ntext、char、varchar、nvarchar の各型の列が含まれます。文字列サマリーを使用することで、任意数のワイルドカードが任意の組み合わせでパターンに含まれている場合に、SQL Server が LIKE 条件の選択度を正確に推定することが可能になります。たとえば、SQL Server は次の形式を持つ述語の選択度を推定できます。

Column LIKE 'string%'
Column LIKE '%string'
Column LIKE '%string%'
Column LIKE 'string'
Column LIKE 'str_ing'
Column LIKE 'str[abc]ing'
Column LIKE '%abc%xy'

たとえば、パターンが LIKE pattern ESCAPE escape_character という形式の場合など、ユーザー指定のエスケープ文字が LIKE パターンに存在する場合に、SQL Server 2005 は選択度を推測します。

このテクノロジは SQL Server 2000 から改善されたもので、末尾のワイルドカード % を除く任意のワイルドカードが LIKE パターンで使用されているときに、選択度を推測します。ただし、末尾のワイルドカード % が使用されている場合は推定の正確さが制限されます。

DBCC SHOW_STATISTICS が返す最初の行セットの String Index フィールドには、統計情報オブジェクトに文字列サマリーも含まれている場合に、値 YES が含まれます。文字列サマリーの内容は表示されません。文字列サマリーには、ヒストグラムに示されるもの以外の追加の情報が含まれています。

80 文字を超える文字列の場合は、最初の 40 文字と最後の 40 文字が文字列から抽出されて連結された後、その文字列が文字列サマリーの作成で考慮されます。したがって、文字列の中で無視された部分にしか出現しない部分文字列については、正確な頻度を推定できません。

計算列に関する統計情報

SQL Server 2005 では、クエリに計算列が名前で含まれておらず、計算列の式が含まれている場合であっても、計算列に関する統計情報の作成、更新、および使用が可能です。SQL Server 2000 では、クエリの中で計算列の名前が明示的に示されている場合にしか、それらの計算列に関する統計情報を自動的に作成、更新、および使用することができません。

次の Transact-SQL スクリプトを SQL Server 2005 で実行すると、AdventureWorks のデータベース テーブル列 Sales.SalesOrderHeader.TotalDue に対する計算列について、その統計情報の自動作成を確認できます。

USE AdventureWorks
GO
-- Remove all statistics for Sales.SalesOrderHeader
DECLARE c CURSOR FOR
SELECT name FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderHeader')
AND auto_created <> 0 AND user_created <> 0

DECLARE @name NVARCHAR(255)

OPEN c

FETCH next FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop statistics Sales.SalesOrderHeader.' + @name)
FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- Query Sales.SalesOrderHeader with an expression equivalent
-- to the TotalDue computed column,
-- ((isnull(([SubTotal]+[TaxAmt])+[Freight],(0))).
SELECT *
FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00
ORDER BY TotalDue DESC

-- List the created statistics. Observe that statistics 
-- are created for TotalDue even though it is not explicitly
-- referenced in the query.
sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005 では、共通言語ランタイム (CLR) ユーザー定義関数の呼び出しなどの CLR 式が含まれている、一貫性のない計算列に関する統計情報は、サポートされていません。CLR 計算列に関する統計情報を作成するには、列が PERSISTED とマークされている必要があります。

CLR ユーザー定義型列に関する統計情報

SQL Server 2005 では、CLR ユーザー定義型列でバイナリ順がサポートされている場合に、その列に関する統計情報の作成、更新、および使用がサポートされます。バイナリ順がサポートされていないユーザー定義型では、統計情報はサポートされません。型がバイナリ順であるとは、型定義の一部として指定されている SqlUserDefinedType の中で IsByteOrdered フラグが True に設定されている場合の型を指します。型がバイナリ順をサポートすることは、標準のバイナリ並べ替え順序がその型にとって意味的に正しい順序であることを意味します。

統計情報とインデックス付きビュー

通常、インデックス付きビューには統計情報は必要ありません。これは、インデックス付きビューからクエリ プランへの置き換えが考慮されるのは、基になるテーブルおよびインデックスのすべての統計情報がクエリ プランに添付された後のみだからです。ただ 1 つ例外として、FROM 句の中で NOEXPAND ヒントを使用してビューを直接参照している場合は、統計情報が使用されます。インデックスが含まれていないビューに対して NOEXPAND ヒントも使用した場合はエラーとなり、プランは作成されません。

インデックス付きビューについてはこのように使用が限られるため、インデックス付きビューに関する統計情報が sp_createstats を使用して作成されたり、sp_updatestats を使用して更新されたりすることはありません。auto update および auto createstatistics の機能はインデックス付きビューに対して正常に動作します。ただし、既に説明したように、そのような統計情報がオプティマイザで必要になり、以降作成されるのは、クエリの中でインデックス付きビューを NOEXPAND ヒントと共に使用し、auto update/create statistics オプションをオンにしている場合のみです。インデックス付きビューに対して CREATE STATISTICS を手動で実行したり、UPDATE STATISTICS を使用してインデックス付きビューに関する列またはインデックスの統計情報を更新したりすることも可能です。

統計情報の管理に関するベスト プラクティス

SQL Server で統計情報を使用する目的は、オプティマイザが良好なクエリ プランを見つけることができるように良好な基数推定値を得られるようにし、それと同時に、統計情報の収集に伴うあらゆるオーバーヘッドや遅れを妥当な範囲に抑えることです。以下では、SQL Server で統計情報を管理する際のベスト プラクティスを、重要なものから順に示します。

統計情報の自動作成および自動更新を使用する

SQL Server の大多数のインストール事例では、統計情報の自動作成および自動更新をデータベース全体にわたって使用することが、最も重要なベスト プラクティスです。統計情報の自動作成および自動更新は既定でオンにされています。プランが良好でない場合や、統計情報が欠落している場合、または統計情報の日付が古い場合は、統計情報の自動作成および自動更新がオンにされているかどうかを確認してください。

fullscan による統計情報を必要に応じて選択的に使用する

統計情報の自動作成および自動更新を使用していて、統計情報が不正確、または最新ではないために良好なクエリ プランが得られない場合は、以下を実行してください。

  • 統計情報の自動作成および自動更新をオンのままにします。同時に

  • 不正確、または十分に最新ではない統計情報に対してのみ、
    CREATE STATISTICS … WITH FULLSCAN, NORECOMPUTE
    を使用し、
    UPDATE STATISTICS … WITH FULLSCAN, NORECOMPUTE
    を定期的に実行するバッチ ジョブを使用します。

必要となる統計情報の更新頻度はアプリケーションによって異なり、実験的に決めなければならない場合があります。fullscan による更新の頻度を決める際に、対象のテーブルの更新レートが高い場合は、fullscan による統計情報の更新を毎夜実行することから始めることをお勧めします。テーブルの更新レートが低い場合は、fullscan による統計情報の更新を週単位で実行してください。

クエリでのローカル変数の使用は避ける

クエリ述語の中でパラメータやリテラルではなくローカル変数を使用している場合、オプティマイザは述語の選択度を得るための最後の手段として、品質を落とした推定、つまり推測を実行します。クエリではローカル変数ではなくパラメータやリテラルを使用してください。通常はそのようにすることで、オプティマイザはより良好なクエリ プランを選択できるようになります。たとえば、ローカル変数を使用している次のクエリを考えます。

declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate

オプティマイザが推定する SalesOrderHeader からの行数は限定されます。一方、条件 h.OrderDate >= @StartOrderDate は 9439.5 であり、これはテーブルのサイズのちょうど 30% です。クエリに対してはグラフィカルなプラン表示を使用できます。Sales.SalesOrderHeader のプラン ノードを右クリックすると、この基数推定値が表示されます。このペーパーの執筆時点で使用していたプレリリース版の SQL Server 2005 では、選択されたプランでマージ結合が使用されています (以下の説明はこれと同じバージョンの SQL Server 2005 に基づいたものです。使用する SQL Server のバージョンや使用可能メモリなどによっては、結果が異なる場合があります)。ここで、これと等価で、ローカル変数を使用していないクエリを考えます。

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= '20040731'

述語 "h.OrderDate >= '20040731'" に対する結果セットの基数は、クエリのグラフィカルなプラン表示の中では 40 と推定され (filter 演算子を右クリックします)、選択度は 0.13% となります。このクエリに対して選択されたプランでは、このような推定の改善のために、マージ結合ではなくネストされたループ結合が使用されています。

クエリの中でローカル変数を使用していても、等価述語の場合は、推測よりも良好な推定が使用されます。"@local_variable = column_name" という形式の条件に対する選択度は、column_name のヒストグラムから得られる平均値頻度を使用して推定されます。そのため、たとえば列 column_name に含まれている値がすべて一意である場合は、選択度の推定値として、列内にある一意の値の個数の逆数が使用され、それが正確な値になります。

クエリが実行されるたびに SQL Server によってクエリが強制的に最適化され、ローカル変数の値を使用してクエリの最適化時に基数とコストが推定されるようにするには、RECOMPILE クエリ ヒントをクエリに追加します。たとえば、前述の 2 つのクエリ例では、最初の例を次のように変更します。

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
OPTION(RECOMPILE)

これで、ローカル変数が原因となって推定が良好でなくなることは解消されます。ただし、クエリを実行するたびにクエリが再コンパイルされます。

ローカル変数の使用をなくすには、(1) 変数ではなくリテラルを使用してクエリを記述し直す、(2) ローカル変数の使用に代わるパラメータを指定した sp_executesql を使用する、または (3) ローカル変数の使用に代わるパラメータを指定したストアド プロシージャを使用するかの、いずれかを考慮してください。EXEC を介した動的 SQL もローカル変数の解消に役立つ場合がありますが、通常はコンパイルのオーバーヘッドが高くなります。

複数ステートメントの TVF やテーブル変数の使用を制限することを検討する

複数ステートメントのテーブル値関数 (TVF) には統計情報はありません。オプティマイザはそれらの結果のサイズを推測する必要があります。同様に、テーブル変数にも統計情報はなく、オプティマイザは最後の手段として基数を推測する必要があります。このような推測の結果として良好でないプランが得られる場合は、TVF の結果を一時的に保持しておく場所として標準テーブルまたは一時テーブルを使用するか、またはテーブル変数を置き換えることを検討してください。そのようにすることで、より良好な基数推定値をオプティマイザが使用できるようになります。

折りたたみのできない式や組み込みのスカラー関数の結果は推測になる場合がある

SQL Server は、定数しか含んでいない一部の式をコンパイル時に評価できます。これを "定数の折りたたみ" と呼びます。折りたたみのできる式は、選択度の推定時に、リテラルとまったく同様に扱われます。折りたたみのできない式は、選択度の推定時に、結果として推測になります。たとえば、次の Transact-SQL スクリプトを考えます。このスクリプトでは UserLog テーブルに 200 行を設定しています。行の半分に一意の UserName の値を持たせ、もう半分に同じ UserName の値を持たせることで、傾斜分布を表現しています。

IF object_id('UserLog') IS NOT NULL
DROP TABLE UserLog
GO
CREATE TABLE UserLog (UserName NVARCHAR(255), Action NVARCHAR(1000))
DECLARE @i INT
SET @i = 1
SET nocount ON
WHILE @i <= 100
BEGIN
INSERT UserLog VALUES(suser_sname(), 'login')
INSERT UserLog VALUES(newid(), 'login')
SET @i = @i + 1
END

組み込み関数 suser_sname() は現在の Windows ユーザーの domain_name\user_name を返し、newid() は一意の値を出力します。ここで、互いに等価な 2 つの異なるクエリを実行します。下に示す 1 番目のクエリには、述語 UserName = suser_sname() が含まれています。オプティマイザは結果の基数を推測する必要があり、1.98 と推測します。この値は、SET STATISTICS XML ON ディレクティブによって生成される XML プラン表示内の EstimateRows 属性で確認できます。実際の基数は 100 であるため、50 倍以上離れています。

GO
SET STATISTICS XML ON
GO
SELECT * FROM UserLog WHERE UserName = suser_sname()
GO
SET STATISTICS XML OFF
GO

2 番目のクエリは、sp_executesql を通じてパラメータ クエリとして発行されるものです。
suser_sname() の値を式としてクエリ内に記述しているのではなく、入力パラメータとして渡しています。

SET STATISTICS XML ON
GO
DECLARE @UserName NVARCHAR(255)
SET @UserName = suser_sname()
EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n', 
N'@n nvarchar(255)', @UserName
GO
SET STATISTICS XML OFF
GO

今度は、オプティマイザは選択度 UserName = @n に対して 50% という正確な推定を使用します。生成される XML プラン表示を見ると、EstimateRows が 100 と正確に表示されることがわかります。より大きなデータ セットに対して、より大きく複雑なクエリを実行する場合には、この種のエラーのために良好なプランが選択されない可能性があります。これがアプリケーションにとって問題となる場合は、上記に示したような手法を使用することを検討してください。問題のクエリを含む sp_executesql またはストアド プロシージャを使用し、折りたたみのできない式の事前計算済みの結果を入力パラメータとして渡してください。こうすることで問題を回避し、良好な基数推定値を得ることができます。

クエリに複数列の条件がある場合は複数列の統計情報を使用する

クエリに複数列の条件があるとき、オプティマイザがクエリにとって最良のプランを生成していないと判断される場合は、複数列の統計情報の使用を検討してください。複数列の統計情報は複数列インデックスの作成の副産物として得られます。そのため、複数列の条件をサポートしている複数列インデックスが既にある場合は、統計情報を明示的に作成する必要はありません。統計情報の自動作成では、単一列の統計情報しか作成されず、複数列の統計情報が作成されることは決してありません。したがって、複数列の統計情報が必要な場合は、それらを手動で作成するか、または複数列インデックスを作成してください。

AdventureWorks.Person.Contact にアクセスし、次の条件を含むクエリを考えます。

FirstName = 'Catherine' AND LastName = 'Abel'

このクエリについて選択度の推定の正確さを高めるため、次の統計情報オブジェクトを作成します。

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

この統計情報オブジェクトは、LastName と FirstName に関する述語を含むクエリの場合だけでなく、LastName に関する述語だけを含むクエリの場合にも役立ちます。一般に、複数列の統計情報オブジェクトにある列セットについて、その任意のプレフィックスに関する述語の選択度は、その統計情報オブジェクトを使用して推定できます。

統計情報オブジェクトで複数列の条件を完全にサポートするためには、複数列の統計情報オブジェクトにある列のプレフィックスに、条件内の列が含まれている必要があります。たとえば、列 (a,b,c) に関する複数列の統計情報オブジェクトは、条件 a=1 AND c=1 を部分的にしかサポートしません。a=1 に対する選択度はヒストグラムを使用して推定されますが、b が条件から欠落しているため、c の密度情報は使用されません。(a,c) または (a,c,b) に関する複数列の統計情報であれば、条件 a=1 AND c=1 をサポートし、密度情報を使用して選択度の推定を改善できます。

SQL Server が選択度を推測してしまう状況について注意する

SQL Server は、いくつかの状況において選択度を推測します。多くの場合、推測は妥当であり、データ サイズが問題にならないほど十分小さいか、または、推測から良好でないクエリ プランが導かれることはありません。しかし、SQL Server がクエリ述語の選択度を推測しなければならないときに、最適とは言えないクエリ プラン結果が得られる場合あります。クエリが期待どおりに実行されず、次善のクエリ プランが選択されていると思われる場合は、クエリとその結果のプランを調べ、選択度が統計情報に基づいて推定されているのではなく推測されている兆候がないか確かめてください。多くの場合、クエリ、またはアプリケーションのわずかな部分を修正することによって、推測を避けることができます。推測を招く可能性がある状況と、その回避策を以下に示します。

  • 統計情報が欠落している場合。統計情報の自動作成を有効にしているかどうかをチェックします。あるいは、CREATE STATISTICS または sp_createstats を使用して統計情報を手動で作成します。データベースが読み取り専用であるために統計情報の自動作成が実行されないようになっていないか調べます。

  • クエリ条件の中でローカル変数を使用している場合 (回避策については前述の説明を参照)。

  • クエリ条件の中に定数の折りたたみができない式がある場合 (T.x+1 = 0、suser_sname() = T.UserName など)。該当する式がなくなるようにクエリを記述し直します。または、クエリを実行する前に式を評価し、その結果を (ローカル変数ではなく) パラメータとしてクエリに渡します。たとえば、T.x+1 = 0 の場合は、式を T.x = -1 と書き直します。これは等価であり、推測ではなく正確な推定が実行されるようになります。

  • 式が複雑な場合 ("h.OrderDate >= '20040731'"、"Price * (1+TaxRate) > 100" など)。このような式のためにクエリのパフォーマンスが期待した速度よりも遅い場合は、等価な式を持つ計算列を作成し、その計算列に関する統計情報またはインデックスを作成することを検討してください。計算列が既に存在する場合は、統計情報の自動作成によっても統計情報が作成されます。そのため、統計情報の自動作成を有効にしている場合は、計算列の統計情報を手動で作成する必要はありません。

ストアド プロシージャのパラメータをクエリの中で使用する前にストアド プロシージャの中で変更することは避ける

ストアド プロシージャ本体の中で新しい値をプロシージャのパラメータに割り当てた後、クエリの中でそのパラメータ値を使用している場合、クエリのパフォーマンスを最適にするため、状況によってはそのような操作を避けなければならないことがあります。ストアド プロシージャと、その中にあるすべてのクエリが最初にコンパイルされるときは、入力パラメータとして先にクエリに渡されるパラメータ値が使用されます。これは "パラメータのスニッフィング" と呼ばれることがあります。次のストアド プロシージャを考えます。このストアド プロシージャは、特定の日付以降の売上を取得します。引数として NULL を渡した場合は、過去 3 か月の売上を取得します。

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS
BEGIN
IF @date IS NULL
SET @date = dateadd("mm",-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

このストアド プロシージャを NULL を指定して呼び出した場合は、最後の SELECT ステートメントが @date = NULL と最適化されます。NULL の OrderDate を持つ行は存在しないため、このフィルタを SalesOrderHeader に適用した結果に対する選択度の推定値は、非常に低くなります (1 行)。しかし、実行時には日付は NULL ではなく、最後の OrderDate よりも 3 か月前になります。制限される SalesOrderHeader 行の実際の行数は 5,736 になります。オプティマイザは、GetRecentSales に NULL が渡されたときに、クエリに対してネストされたループ結合を選択します。一方、最適なプランにはマージ結合が含まれます。次のスクリプトを使用して、選択されるプラン、期待される基数、および実際の基数を表示できます。

SET STATISTICS PROFILE ON
GO
EXEC GetRecentSales NULL
GO
SET STATISTICS PROFILE OFF
GO

上の GetRecentSales ストアド プロシージャでは WITH RECOMPILE オプションを指定していますが、基数推定のエラーはなくならないことがわかります。この例のクエリが適切なパラメータ値を使用して最適化されるようにし、良好な推定値が得られるようにするためには、1 つの方法として、ストアド プロシージャを次のように変更して分割します。

CREATE PROCEDURE GetRecentSales (@date datetime)  AS
BEGIN
IF @date IS NULL
SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) 
FROM Sales.SalesOrderHeader))
EXEC GetRecentSalesHelper @date
END

CREATE PROCEDURE GetRecentSalesHelper (@date datetime) WITH RECOMPILE AS
BEGIN
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
AND h.OrderDate > @date -- @date is unchanged from compile time,
-- so a good plan is obtained.
END

昇順キーに対して統計情報の収集頻度を上げることを検討する

IDENTITY 列や実際のタイムスタンプを表す日付時刻列などの昇順キー列は、INSERTS が頻出するテーブルにおいて、新しい値がすべてヒストグラムの範囲外になるため、統計情報が不正確になる原因となることがあります。アプリケーションで、昇順キー列に関する条件を持つクエリに対して不適切なクエリ プランが得られると思われる場合は、それらの列に関する統計情報をバッチ ジョブを使用して頻繁に更新することを検討してください。バッチ ジョブをどの程度の頻度で実行する必要があるかは、アプリケーションによって異なります。毎日または毎週、またはアプリケーションで必要な場合はそれ以上の頻度で実行してください。

同期更新によって望ましくない遅れが発生する場合は統計情報の非同期更新を使用する

大きなデータベースを使用していて、OLTP 作業負荷があり、AUTO_UPDATE_STATISTICS を有効にしている場合、通常は瞬間的に実行されるいくつかのトランザクションが、統計情報の更新の原因となるために、まれに実行に数秒以上かかることがあります。この無視できない遅れの可能性を避ける必要がある場合は、AUTO_UPDATE_STATISTICS_ASYNC を有効にしてください。長時間実行されるクエリの作業負荷の場合は、コンパイル時のまれな遅れよりも、最良のプランを得ることの方が重要です。そのような場合は、統計情報の非同期更新ではなく同期更新を使用してください。

まとめ

SQL Server 2005 では、統計情報の管理機能について数多くの強化が図られています。最も重要なことは、ほとんどの場合において、良好なクエリ プランが確実に得られる統計情報の自動作成および更新を利用できることです。既定のサンプリング レートによる統計情報の自動作成および更新では不十分な場合は、統計情報のサンプリング レートと、作成および更新の時間を、明示的に制御できます。統計情報やコスト推定から見て明らかに次善のクエリ プランだと思われる場合は、このペーパーで説明したベスト プラクティスを検討してください。

関連情報

[Mar04] Arun Marathe 著 「SQL Server 2005 のバッチのコンパイル、再コンパイル、およびプランのキャッシュに関する問題」
https://www.microsoft.com/japan/technet/prodtechnol/sql/2005/recomp.mspx (2004 年 7 月)