DBCC SHOWCONTIG (Transact-SQL)

更新 : 2007 年 9 月 15 日

指定されたテーブルまたはビューのデータとインデックスの断片化に関する情報を表示します。

重要 :
この機能は、将来のバージョンの Microsoft SQL Server では削除される予定です。新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。 代わりに sys.dm_db_index_physical_stats を使用してください。

Transact-SQL 構文表記規則

構文

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

引数

• table_name | table_id | view_name | view_id
  断片化情報をチェックするテーブルまたはビューを指定します。指定しない場合、現在のデータベース内のすべてのテーブルおよびインデックス付きビューがチェックされます。テーブル ID またはビュー ID を取得するには、OBJECT_ID 関数を使用します。

• index_name | index_id
  断片化情報をチェックするインデックスを指定します。インデックスを指定しない場合、ステートメントは指定されたテーブルまたはビューのベース インデックスを処理します。インデックス ID を取得するには、sys.indexes カタログ ビューを使用します。

• WITH
  DBCC ステートメントが返す情報の種類に関するオプションを指定します。

• FAST
  インデックスの高速スキャンを実行し、最小限の情報を出力するかどうかを指定します。高速スキャンでは、インデックスのリーフ レベルまたはデータ レベルのページは読み込まれません。

• ALL_INDEXES
  特定のインデックスが指定されていても、指定されたテーブルおよびビューのすべてのインデックスに関する結果を表示します。

• TABLERESULTS
  追加情報を含めた結果を、行セットとして表示します。

• ALL_LEVELS
  旧バージョンとの互換性のためにだけ用意されています。ALL_LEVELS が指定されている場合でも、インデックス リーフ レベルまたはテーブル データ レベルのみ処理されます。

• NO_INFOMSGS
  重大度レベル 0 ～ 10 のすべての情報メッセージを表示しないようにします。

結果セット

次の表では、結果セットに表示される情報について説明します。

統計

説明

スキャンされたページ数

テーブルまたはインデックスのページ数です。

スキャンされたエクステント数

テーブルまたはインデックスのエクステント数です。

エクステントの切り替え回数

DBCC ステートメントがテーブルまたはインデックスのページを横断する間に 1 つのエクステントから別のエクステントに移動する回数です。

エクステントごとの平均ページ数

ページ チェーンに存在するエクステント 1 つあたりのページ数です。

スキャン密度 [最善値 : 実際値]

パーセンテージです。実際値に対する最善値の比率です。すべて連続的にリンクされているときは、この値は 100 になります。100 未満の場合は、断片化が発生していることを示します。

最善値は、すべて連続的にリンクされている場合の理想的なエクステント変更回数です。実際値は、実際のエクステント変更回数を示します。

論理スキャンの断片化

インデックスのリーフ ページをスキャンするときに返される、順序が不正なページのパーセンテージです。この数値はヒープとは関係ありません。順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの次ページ** ポインタで示されるページが異なるページのことです。

エクステント スキャンの断片化

インデックスのリーフ ページをスキャンするときの順序が不正なエクステントのパーセンテージです。この数値はヒープとは関係ありません。順序が不正なエクステントとは、インデックス上の現在のページを含むエクステントの物理的な位置が、インデックス上の前のページを含むエクステントの直後でない状態を指します。

メモ :

インデックスが複数のファイルにわたっている場合、この数値は無意味です。

ページごとの平均空きバイト数

スキャンしたページの平均の空きバイト数です。数値が大きいほど、ページの密度が低くなります。インデックスに多くのランダム挿入が含まれていない場合は、小さい値の方が適しています。この数値は行サイズの影響も受けます。行サイズが大きいと、数値が大きくなります。

平均ページ密度 (全体)

パーセンテージで表した平均ページ密度です。この数値は行サイズを考慮しています。このため、ページが満たされる程度に関してはより正確な指標になります。パーセント値は、大きいほど適しています。

table_id および FAST を指定した場合、DBCC SHOWCONTIG は次の列だけが含まれる結果セットを返します。

• スキャンされたページ数
• エクステントの切り替え回数
• スキャン密度 [最善値 : 実際値]
• エクステント スキャンの断片化
• 論理スキャンの断片化

TABLERESULTS を指定した場合、DBCC SHOWCONTIG は次の列に加え、前の表で説明した 9 つの列も返します。

統計

説明

Object Name

処理されるテーブルまたはビューの名前です。

ObjectId

オブジェクトの ID です。

IndexName

処理されるインデックスの名前です。ヒープの場合は NULL です。

IndexId

インデックスの ID です。ヒープの場合は 0 です。

Level

インデックスのレベルです。レベル 0 は、インデックスのリーフ レベルまたはデータ レベルです。

ヒープの場合、レベルは 0 です。

Pages

インデックスまたはヒープ全体のレベルを構成するページ数です。

Rows

指定したインデックスのレベルのデータまたはインデックス レコードの数です。ヒープの場合は、ヒープ全体のデータ レコードの数です。

ヒープでは、この関数から返されるレコード数が、ヒープに対して SELECT COUNT(*) を実行したときに返される行数と一致しない場合があります。これは、1 つの行に複数のレコードが含まれている可能性があるためです。たとえば、更新を実行している状況では、1 つのヒープ行に前方向レコードと、更新操作の結果として転送されたレコードがそれぞれ 1 つずつ含まれる場合があります。また、大きな LOB 行のほとんどは、LOB_DATA ストレージ内で複数のレコードに分割されます。

MinimumRecordSize

指定したインデックスのレベルまたはヒープ全体のレコードの最小サイズです。

MaximumRecordSize

指定したインデックスのレベルまたはヒープ全体のレコードの最大サイズです。

AverageRecordSize

指定したインデックスのレベルまたはヒープ全体の平均レコード サイズです。

ForwardedRecords

指定したインデックスのレベルまたはヒープ全体の転送されたレコードの数です。

Extents

指定したインデックスのレベルまたはヒープ全体のエクステントの数です。

ExtentSwitches

DBCC ステートメントがテーブルまたはインデックスのページを横断する間に 1 つのエクステントから別のエクステントに移動する回数です。

AverageFreeBytes

スキャンしたページの平均の空きバイト数です。数値が大きいほど、ページの密度が低くなります。インデックスに多くのランダム挿入が含まれていない場合は、小さい値の方が適しています。この数値は行サイズの影響も受けます。行サイズが大きいと、数値が大きくなります。

AveragePageDensity

パーセンテージで表した平均ページ密度です。この数値は行サイズを考慮しています。このため、ページが満たされる程度に関してはより正確な指標になります。パーセント値は、大きいほど適しています。

ScanDensity

パーセンテージです。ActualCount に対する BestCount の比率です。すべて連続的にリンクされているときは、この値は 100 になります。100 未満の場合は、断片化が発生していることを示します。

BestCount

すべて連続的にリンクされている場合の理想的なエクステント変更回数です。

ActualCount

エクステント変更の実際の数です。

LogicalFragmentation

インデックスのリーフ ページをスキャンするときに返される、順序が不正なページのパーセンテージです。この数値はヒープとは関係ありません。順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの次ページ** ポインタで示されるページが異なるページのことです。

ExtentFragmentation

インデックスのリーフ ページをスキャンするときの順序が不正なエクステントのパーセンテージです。この数値はヒープとは関係ありません。順序が不正なエクステントとは、インデックス上の現在のページを含むエクステントの物理的な位置が、インデックス上の前のページを含むエクステントの直後でない状態を指します。

メモ :

インデックスが複数のファイルにわたっている場合、この数値は無意味です。

WITH TABLERESULTS および FAST を指定した場合の結果セットは WITH TABLERESULTS を指定した場合と同じです。ただし、次の列が NULL 値になります。

解説

index_id を指定すると、DBCC SHOWCONTIG ステートメントは、指定されたインデックスのリーフ レベルでページ チェーンを移動します。table_id のみを指定するか、index_id を 0 に指定すると、指定されたテーブルのデータ ページがスキャンされます。この操作では、インテント共有 (IS) テーブル ロックのみが必要となります。この方法では、排他 (X) テーブル ロックが必要な場合を除き、すべての更新と挿入が実行できます。これにより、実行の速度は速くなりますが、返される統計数に対する同時実行の数は削減されません。ただし、このコマンドを断片化の測定にのみ使用している場合は、最適なパフォーマンスを実現するために、WITH FAST オプションを使用することをお勧めします。高速スキャンでは、インデックスのリーフ レベルまたはデータ レベルのページは読み込まれません。WITH FAST オプションはヒープには適用されません。

SQL Server 2005 における変更

SQL Server 2005 では、SQL Server 2000 よりも断片化計算のアルゴリズムが正確になりました。この結果、断片化の値は以前より高く表示されます。たとえば SQL Server 2000 では、テーブルの 11 ページと 13 ページが同じエクステントにあり、12 ページがない場合、そのテーブルは断片化しているとは見なされません。しかし、これら 2 ページにアクセスするには 2 回の物理的な I/O 操作が必要になります。したがって、SQL Server 2005 ではこの状態は断片化と見なされます。

DBCC SHOWCONTIG では、ntext、text、および image データ型のデータは表示されません。これは、テキストとイメージ データが格納されているテキスト インデックス (SQL Server 2000 のインデックス ID 255) が SQL Server 2005 に存在しなくなったためです。インデックス ID 255 の詳細については、「sys.sysindexes (Transact-SQL)」を参照してください。

また、SQL Server 2005 の新機能の中には DBCC SHOWCONTIG でサポートされないものもあります。以下に例を示します。

• 指定されたテーブルまたはインデックスがパーティション分割されている場合、DBCC SHOWCONTIG では指定されたテーブルまたはインデックスの最初のパーティションのみが表示されます。
• DBCC SHOWCONTIG では、行オーバーフロー ストレージ情報と、nvarchar(max)、varchar(max)、varbinary(max)、xml など、その他の新しい行以外のデータ型は表示されません。

SQL Server 2005 の新機能はすべて、sys.dm_db_index_physical_stats 動的管理ビューで完全にサポートされます。

テーブルの断片化

DBCC SHOWCONTIG は、テーブルに著しい断片化が生じているかどうかを調べます。テーブルの断片化は、テーブルに対するデータの変更処理 (INSERT、UPDATE、DELETE ステートメント) によって発生します。このような変更処理は、テーブルのすべての行にわたって均等に分散するわけではないので、時間の経過と共に、各ページの充填率に差が生じることがあります。そのため、テーブルの一部または全体をスキャンするクエリでは、このようなテーブルの断片化によって、余分なページ読み込みが必要になり、データの並列スキャンの妨げになる場合があります。

インデックスが著しく断片化している場合、断片化を減らす方法は次のとおりです。

• クラスタ化インデックスを削除し、再作成します。
  クラスタ化インデックスを再作成すると、データが再構成され、データ ページがデータで満たされます。充填率のレベルは、CREATE INDEX の FILLFACTOR オプションを使用することによって構成することができます。この方法の短所は、削除と再作成のサイクルの間インデックスがオフラインになることと、その操作がアトミックであることです。インデックスの作成が中断されると、そのインデックスは再作成されません。
• インデックスのリーフ レベルのページを論理順序の順に並べ替えます。
  ALTER INDEX...REORGANIZE を使用してインデックスのリーフ レベル ページを論理順序の順に並べ替えます。この操作はオンライン操作であるため、ステートメントを実行しているときにインデックスが使用できます。この操作は、完了済みの作業を失わずに中断することもできます。この方法の短所は、クラスタ化インデックスの削除と再作成の操作よりも、データの再構成において劣ることです。
• インデックスを再構築します。
  REBUILD を設定した ALTER INDEX を使用して、インデックスを再構築します。詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

結果セットに含まれる [ページごとの平均空きバイト数] および [平均ページ密度 (全体)] の統計情報は、インデックス ページの充填率を示します。インデックスに多くのランダム挿入が含まれない場合は、[ページごとの平均空きバイト数] の数値は小さく、[平均ページ密度 (全体)] の数値は大きくなります。インデックスを削除し、FILLFACTOR オプションを指定して再作成を行うと、これらの統計が改善されます。また、REORGANIZE と併用する ALTER INDEX コマンドは、インデックスの FILLFACTOR を考慮しながらインデックスを最適化するので、これらの統計が改善されます。

メモ :
ランダム挿入が多く、ページの密度が高いインデックスは、ページ分割の数が増えます。これにより、断片化の数も多くなります。

インデックスの断片化レベルは、次の方法で確認できます。

• [エクステントの切り替え回数] と [スキャンされたエクステント数] の値を比較します。
  [エクステントの切り替え回数] の値は、[スキャンされたエクステント数] の値とできる限り近い値にします。この比率は、[スキャン密度] 値として計算されます。この値はできるだけ高くし、インデックスの断片化を削減することで改善できます。

  メモ :
  この方法は、インデックスが複数のファイルにわたっている場合は使用できません。

• [論理スキャンの断片化] および [エクステント スキャンの断片化] の値を理解します。
  [論理スキャンの断片化] および、それに比べて程度は低くなりますが、[エクステント スキャンの断片化] の値は、テーブルの断片化レベルの最善の指標です。0 ～ 10% は許容範囲ですが、この 2 つの値はできるだけ 0 に近い値にする必要があります。

  メモ :
  インデックスが複数のファイルにわたっている場合、[エクステント スキャンの断片化] の値は大きくなります。これらの値を小さくするには、インデックスの断片化を削減する必要があります。

権限

ユーザーはテーブルを所有しているか、固定サーバー ロール sysadmin、固定データベース ロール db_owner、または固定データベース ロール db_ddladmin のメンバであることが必要です。

例

A. テーブルの断片化情報を表示する

次の例では、Employee テーブルの断片化情報を表示します。

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. OBJECT_ID を使用してテーブル ID を取得し、sys.indexes を使用してインデックス ID を取得する

次の例では、OBJECT_ID および sys.indexes カタログ ビューを使用して、AdventureWorks データベース内の Production.Product テーブルの AK_Product_Name インデックスに対するテーブル ID とインデックス ID を取得します。

USE AdventureWorks;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id 
FROM sys.indexes
WHERE object_id = @id 
   AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

C. テーブルの簡略な結果セットを表示する

次の例では、AdventureWorks データベース内の Product テーブルの簡略な結果セットを返します。

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

D. データベースに含まれる全テーブルの全インデックスの完全な結果セットを表示する

次の例では、AdventureWorks データベースに含まれる全テーブルの全インデックスの完全な結果セットを返します。

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. DBCC SHOWCONTIG と DBCC INDEXDEFRAG を使用して、データベース内のインデックスの断片化を解消する

次の例では、宣言されたしきい値を超えて断片化しているデータベースのインデックスすべての断片化を解消する、簡単な方法を示しています。

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

参照

関連項目

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
DBCC (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)

その他の技術情報

領域の割り当てと再利用

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴