DBCC SHOWCONTIG (Transact-SQL)

適用対象: SQL Server Azure SQL Managed Instance

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

重要

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

適用対象: SQL Server 2008 (10.0.x) 以降のバージョン

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 未満の場合は、断片化が発生していることを示します。 最善値は、すべて連続的にリンクされている場合の理想的なエクステント変更回数です。 実際値は、実際のエクステント変更回数を示します。
論理スキャンの断片化	インデックスのリーフ ページをスキャンするときに返される、順序が無効なページのパーセンテージです。 この数値はヒープとは関係ありません。 順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの next-page ポインターが示すページが異なるページのことです。
エクステント スキャンの断片化	インデックスのリーフ ページをスキャンするときの順序が無効なエクステントのパーセンテージです。 この数値はヒープとは関係ありません。 順序が無効なエクステントとは、インデックス上の現在のページを含むエクステントの物理的な位置が、インデックス上の前のページを含むエクステントの直後でない状態を指します。 注: インデックスが複数のファイルにわたっている場合、この数値は無意味です。
平均空きバイト数 (ページあたり)	スキャンしたページの平均の空きバイト数です。 数値が大きいほど、ページの密度が低くなります。 ランダムな挿入がそれほど行われないインデックスの場合は、小さい値の方が適しています。 この数値は行サイズの影響も受けます。行サイズが大きいと数値が大きくなります。
平均ページ密度 (全体)	パーセンテージで表した平均ページ密度です。 この数値は行サイズを考慮しています。 このため、ページが満たされる程度に関してはより正確な指標になります。 パーセント値は、大きいほど適しています。

table_id と FAST を指定した場合、DBCC SHOWCONTIG からは次の列だけを含む結果セットが返されます。

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

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

統計	説明
[オブジェクト名]	処理されるテーブルまたはビューの名前です。
ObjectId	オブジェクト名の ID。
IndexName	処理されるインデックスの名前です。 ヒープの場合は NULL です。
IndexId	インデックスの ID。 ヒープのサイズは 0 です。
Level	インデックスのレベルです。 レベル 0 は、インデックスのリーフ レベルまたはデータ レベルです。 ヒープの場合、レベルは 0 です。
ページ	インデックスまたはヒープ全体のレベルを構成するページ数です。
行数	指定したインデックスのレベルのデータまたはインデックス レコードの数です。 ヒープの場合は、ヒープ全体のデータ レコードの数です。 ヒープでは、この関数から返されるレコード数が、ヒープに対して 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	インデックスのリーフ ページをスキャンするときに返される、順序が無効なページのパーセンテージです。 この数値はヒープとは関係ありません。 順序が不正なページとは、インデックスに割り当てられている次の物理的なページと、現在のリーフ ページの next-page ポインターが示すページが異なるページのことです。
ExtentFragmentation	インデックスのリーフ ページをスキャンするときの順序が無効なエクステントのパーセンテージです。 この数値はヒープとは関係ありません。 順序が無効なエクステントとは、インデックス上の現在のページを含むエクステントの物理的な位置が、インデックス上の前のページを含むエクステントの直後でない状態を指します。 注: インデックスが複数のファイルにわたっている場合、この数値は無意味です。

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

[行]	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 を考慮しながらインデックスが最適化されるので、これらの統計が改善されます。

Note

ランダム挿入が多く、ページの密度が高いインデックスは、ページ分割の数が増えます。 断片化が大きくなります。

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

• [エクステントの切り替え回数] と [スキャンされたエクステント数] の値を比較します。

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

  Note

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

• [論理スキャンの断片化] と [エクステント スキャンの断片化] の値を確認します。

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

  Note

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

アクセス許可

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

例

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

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

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

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

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

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

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

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

USE AdventureWorks2022;
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 (Transact-SQL)
• OBJECT_ID (Transact-SQL)
• sys.indexes (Transact-SQL)