インデックス統計
更新 : 2006 年 12 月 12 日
SQL Server 2005 では、列内の値の分布に関する統計情報を作成できます。クエリ オプティマイザでは、この統計情報を使用して、インデックスの使用コストを算出してクエリを評価することにより、最適なクエリ プランを判断します。
データベース エンジンでは、統計が作成されるときに、統計を構築中の列の値が並べ替えられ、複数の間隔で区切られた最大 200 個の値に基づいて、ヒストグラムが作成されます。ヒストグラムでは、各間隔値に正確に一致する行数、間隔内に含まれる行数、および間隔内での値の密度や重複する値の発生率の計算が示されます。
SQL Server 2005 では、char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text、および ntext の各列に作成される統計によって収集された追加情報が提供されます。この情報は文字列の要約と呼ばれ、文字列パターンによるクエリ述語の選択度をクエリ オプティマイザが算出する際に役立ちます。文字列の要約を使用することで、LIKE 条件がクエリ内に存在する場合に、結果セットのサイズをより正確に算出でき、多くの場合、より優れたクエリ プランになります。このことは、WHERE ProductName LIKE '%Bike' や WHERE Name LIKE '[CS]heryl' などの条件にも当てはまります。
.gif "ms190397.note(ja-jp,SQL.90).gif") メモ : |
|---|
| 列サンプルの要約がデータベース エンジンで保持できるよりも大きい場合、文字列の要約情報は保持されません。たとえば、85,000 行のテーブルで、一意の varchar(80) 列の各文字列に 80 文字含まれ、各文字列間に類似性がほとんどない場合、WITH FULLSCAN を使用して作成された統計には、文字列の要約は保持されません。特定の統計オブジェクトに文字列の要約が格納されているかどうかを判断するには、DBCC SHOW_STATISTICS (Transact-SQL) を使用します。 |
自動統計の動作
インデックスを作成すると、クエリ オプティマイザによってインデックス列に関する統計情報が自動的に格納されます。また、AUTO_CREATE_STATISTICS データベース オプションが ON (既定値) に設定されている場合は、述語の中で使用されているインデックスがない列についても、データベース エンジンによって自動的に統計情報が作成されます。
列内のデータが変更されると、インデックスと列統計が最新ではなくなり、クエリ オプティマイザがクエリの処理方法について下す判断が最適でなくなります。たとえば、インデックス列と 1,000 行のデータを含むテーブルを作成し、インデックス列内の値はすべて一意であるとします。この場合、クエリ オプティマイザは、クエリのデータを収集するのにインデックス列が適切であると判断します。列内のデータを更新した結果、多くの値が重複することになると、その列はクエリで使用する最適な候補ではなくなります。しかし、クエリ オプティマイザでは更新前のデータに基づくインデックスの古い分布統計を基に、引き続きそのインデックス列が適切な候補であると判断されます。
| メモ : |
|---|
| 統計が最新ではなかったり、統計が存在しない場合は、SQL Server Management Studio を使用してクエリの実行プランをグラフィカルに表示すると、警告 (赤色のテーブル名) が表示されます。詳細については、「グラフィカル実行プランの表示 (SQL Server Management Studio)」を参照してください。また、SQL Server Profiler を使用して Missing Column Statistics イベント クラスを監視すると、統計情報がない場合はそのことがわかります。詳細については、「Errors and Warnings イベント カテゴリ (データベース エンジン)」を参照してください。 |
AUTO_UPDATE_STATISTICS データベース オプションが ON (既定値) に設定されている場合、テーブル内のデータが変更されると、クエリ オプティマイザによって統計情報が自動的かつ定期的に更新されます。クエリの実行プランで使用されている統計が現在の統計のテストに失敗するたびに、統計の更新が開始されます。サンプリングはデータ ページ間でランダムに行われ、テーブルから取得されるか、統計に必要な列の最小の非クラスタ化インデックスから取得されます。ディスクからデータ ページが読み取られると、そのデータ ページ上のすべての行を使用して統計情報が更新されます。約 20 パーセントのデータ行が変更されると、ほぼ常に、統計情報が更新されます。ただし、クエリ オプティマイザでは、常に最少行数が確実にサンプリングされます。8 MB より小さいテーブルでは、統計を収集するために、常に完全にスキャンされます。
すべてのデータを分析する代わりにデータのサンプリングを行うと、自動統計の更新コストを最小限に抑えることができます。状況によっては、統計サンプリングではテーブル内のデータを正確に表さない場合があります。UPDATE STATISTICS ステートメントの SAMPLE 句と FULLSCAN 句を使用することで、テーブル単位の手動統計更新の際にサンプリングされるデータ量を制御できます。FULLSCAN 句は、テーブル内のすべてのデータをスキャンして統計を収集するように指定します。これに対して SAMPLE 句では、サンプリングする行の比率または行数を指定できます。
統計の非同期更新
古い統計の更新を開始するクエリでは、コンパイルして結果セットを返す前に、古い統計が更新されるのを待機する必要があります。これにより、クエリの応答に予想よりも時間がかかることがあり、厳しいタイムアウト時間が設定されたアプリケーションが失敗する場合があります。
SQL Server 2005 では、データベース オプション AUTO_UPDATE_STATISTICS_ASYNC により、統計の非同期更新を実現しています。このオプションを ON に設定すると、クエリはコンパイル前に統計の更新を待機しません。代わりに、更新をバックグラウンド プロセスのワーカー スレッドで行うために、古い統計がキューに登録されます。非同期更新を行うクエリや他の同時実行中のクエリは、既存の古い統計を使用してすぐにコンパイルされます。統計の更新による遅延がないので、クエリ応答時間を予測できます。ただし、古い統計を使用するため、クエリ オプティマイザで効率性の劣るクエリ プランが選択される場合があります。更新された統計が使用できるようになってから開始されるクエリでは、更新された統計が使用されます。これにより、キャッシュされているプランのうち、古いバージョンの統計に依存しているプランは再コンパイルされる場合があります。CREATE、ALTER、DROP ステートメントなどのデータ定義言語 (DDL) ステートメントが同一の明示的ユーザー トランザクション内で発生する場合は、統計の非同期更新を行うことができません。
AUTO_UPDATE_STATISTICS_ASYNC オプションは、データベース レベルで設定され、データベース内のすべての統計の更新方法がこのオプションにより決定されます。このオプションは統計の更新だけに適用され、このオプションを使用して、統計を非同期に作成することはできません。このオプションを ON に設定しても、AUTO_UPDATE_STATISTICS も ON に設定しない限り、効果はありません。既定では、AUTO_UPDATE_STATISTICS_ASYNC オプションは OFF です。このオプションの設定の詳細については、「ALTER DATABASE (Transact-SQL)」を参照してください。
データベースを SINGLE_USER に設定する前に、AUTO_UPDATE_STATISTICS_ASYNC オプションが OFF に設定されていることを確認します。ON に設定されている場合、統計の更新に使用されるバックグラウンド スレッドによってデータベースへの接続が使用されるため、シングル ユーザー モードでデータベースにアクセスできなくなります。このオプションが ON に設定されている場合、次の作業を行います。
- AUTO_UPDATE_STATISTICS_ASYNC を OFF に設定します。
- sys.dm_exec_background_job_queue 動的管理ビューにクエリを実行することにより、アクティブな非同期の統計ジョブがあるかどうかを確認します。
- アクティブなジョブがある場合、それらのジョブが完了するまで待機するか、KILL STATS JOB を使用して手動でジョブを終了します。
推奨設定の注意事項
アプリケーションに次の特性があるときは、AUTO_UPDATE_STATISTICS_ASYNC オプションを ON に設定することを検討してください。
- 統計の更新を待機している 1 つ以上のクエリによって、クライアント要求がタイムアウトする。
- 古い統計を使用したために、効率性の劣るクエリ プランでクエリが実行されたとしても、クエリの応答時間を予測できることが必要である。
統計の非同期更新のプロパティの表示
AUTO_UPDATE_STATISTICS_ASYNC オプションの ON、OFF の状態を表示するには、sys.databases カタログ ビューから is_auto_update_stats_async_on 列を選択します。詳細については、「sys.databases (Transact-SQL)」を参照してください。
統計が更新用にキューに登録されているか、更新用に処理中であるかを表示するには、sys.dm_exec_background_job_queue 動的管理ビューを使用します。統計の場合、列 object_id1 にはテーブル ID またはビュー ID が表示されます。また、列 object_id2 には統計 ID が表示されます。sys.dm_exec_background_job_queue_stats 動的管理ビューを使用して、実行を待機しているジョブ要求の数、失敗した要求の数、および以前に送信された要求の平均実行時間などの、すべてのバックグラウンド ジョブ キューを集計した統計を表示します。
自動統計の無効化
特定の列やインデックスに対する統計の自動生成は、次の方法で無効にできます。
- sp_autostats システム ストアド プロシージャを使用する。
- CREATE INDEX ステートメントの STATISTICS_NORECOMPUTE 句を使用する。
- UPDATE STATISTICS ステートメントの NORECOMPUTE 句を使用する。
- CREATE STATISTICS ステートメントの NORECOMPUTE 句を使用する。
- ALTER DATABASE ステートメントを使用して、AUTO_CREATE_STATISTICS データベース オプションと AUTO_UPDATE_STATISTICS データベース オプションを OFF に設定する。詳細については、「データベース オプションの設定」を参照してください。
データベース エンジンに統計を自動的に管理しないように指示した場合は、統計情報を手動で更新する必要があります。
| メモ : |
|---|
| NORECOMPUTE 句が指定されていない限り、UPDATE STATISTICS ステートメントによって統計の自動更新が再度有効になります。 |
手動での統計の作成と更新
sp_createstats システム ストアド プロシージャを使用することで、1 つのステートメントで、現在のデータベースにあるすべてのユーザー テーブル内で該当するすべての列の統計を作成することもできます。CREATE STATISTICS ステートメントを使用して、特定のテーブル列やビュー列の統計を作成できます。また、UPDATE STATISTICS ステートメントを使用して統計を更新できます。インデックスとは無関係にテーブルやビューに作成できる統計の最大数は 2,000 です。インデックス キーに適している任意の列または列の組み合わせは統計に適していますが、次のような例外があります。
- xml を除く、ラージ オブジェクト型の列を指定できます。varchar(max)、nvarchar(max)、varbinary(max)、image、text、および ntext の各型を指定できます。
- 組み合わせた列の値の許容最大サイズは、インデックス キー値に課せられる 900 バイトの制限を超えることができます。
列に作成された統計は、これをメンテナンスしたり、管理する必要がなくなった場合に削除できます。
手動で統計を作成すると、複数の列密度が含まれる統計が作成されます。これらは、列の組み合わせでの重複の平均数です。たとえば、クエリに WHERE a = 7 and b = 9 句が含まれているとします。
a と b の両方の列をまとめて手動で統計を作成すると、データベース エンジンでは、クエリにとってより適切な算出が行われます。これは、列 a と列 b を組み合わせた重複しない値の平均値も統計に含まれるためです。
列の統計を作成するには
CREATE STATISTICS (Transact-SQL)
すべてのユーザー テーブル内の該当するすべての列の統計を作成するには
統計を手動で更新するには
UPDATE STATISTICS (Transact-SQL)
テーブルの統計を表示するには
DBCC SHOW_STATISTICS (Transact-SQL)
列の統計を削除するには
DROP STATISTICS (Transact-SQL)
データベースを SQL Server 2005 へアップグレード後の統計に関連する作業
以前のバージョンの SQL Server から SQL Server 2005 にデータベースをアップグレードすると、以前のバージョンの統計はすべて古い統計と見なされます。したがって、AUTO_UPDATE_STATISTICS データベース オプションでの更新に適している統計は、最初の使用時に既定のサンプリング レートを使用して更新されます。この機能には大きな利点があり、ユーザーは通常、どのような操作も実行する必要がありません。ただし、以前のバージョンの SQL Server で、FULLSCAN または他の高いサンプリング レートを使用して統計が手動で計算されている場合、またはサンプリングされるテーブルのサイズが 8 MB を超え、データ分布がランダムでない場合、まれに、あまり正確でない統計になることもあります。実際、AUTO_UPDATE_STATISTICS を行うと、8 MB を超えるテーブルの FULLSCAN 統計では、常にサンプリング レートの低下が発生します。新しいバージョンの SQL Server にアップグレードした後、早い時点で、最初の統計の更新が行われます。
統計を SQL Server 2005 形式にアップグレードする利点は、特定のサンプリング レートの SQL Server 2005 統計が、通常、SQL Server 2000 や以前のバージョンよりも優れた品質であることです。また、前述のように、SQL Server 2005 では、文字型の列に対して特殊な文字列の要約統計が作成されます。SQL Server 2005 の統計の詳細については、この Microsoft Web サイトを参照してください。
推奨設定の注意事項
多くの場合、データベースをアップグレードした後に、統計に関して特殊な操作を行う必要はありません。ただし、パフォーマンス要件が厳しい大規模データベースがある場合、アップグレード後に RESAMPLE オプションを指定して sp_updatestats (Transact-SQL) を実行することをお勧めします。これにより、以前のサンプリング レートが保持された状態で、すべての統計が最新の形式に更新されます。インデックス作成時に作成される統計は、FULLSCAN サンプリング レートを使用して作成されます。これらの FULLSCAN 統計および他の FULLSCAN 統計では、AUTO_UPDATE_STATISTICS により、更新時に既定のサンプリング レートが使用されます。sp_updatestats を実行してすべての統計を更新しない場合は、データベース アップグレード後に UPDATE STATISTICS を使用して、インデックスの統計および FULLSCAN サンプリング レートが使用された他の FULLSCAN 統計を選択して更新することを検討してください。
参照
概念
その他の技術情報
CREATE INDEX (Transact-SQL)
クエリのチューニング
sp_autostats (Transact-SQL)
ヘルプおよび情報
変更履歴
| リリース | 履歴 |
|---|---|
2006 年 12 月 12 日 |
|
2005 年 12 月 5 日 |
|