DBCC SHOWCONTIG (Transact-SQL)

[アーティクル]
06/18/2013

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

重要
この機能は、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 つのヒープ行に含まれることがあります。また、大きな 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 値になります。

Rows	Extents
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

説明

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

制限事項

DBCC SHOWCONTIG では、ntext、text、および image データ型のデータは表示されません。これは、テキストとイメージのデータが格納されているテキストインデックスが存在しなくなったためです。

また、DBCC SHOWCONTIG でサポートされない新機能もあります。次に例を示します。

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

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

テーブルの断片化

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 AdventureWorks2012;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

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

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

USE AdventureWorks2012;
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. テーブルの簡略な結果セットを表示する

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

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

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

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

USE AdventureWorks2012;
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

Share via