DBCC SHOWCONTIG (Transact-SQL)

適用対象:SQL ServerAzure SQL Managed Instance

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

適用対象: 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 のすべての情報メッセージを表示しないようにします。

結果セット

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

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

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

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

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

解説

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

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

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

  [論理スキャンの断片化] と、それに比べて程度は低くなりますが、 [エクステント スキャンの断片化] の値は、テーブルの断片化レベルの最善の指標です。 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)