DBCC INDEXDEFRAG (Transact-SQL)

[アーティクル]
04/26/2023

適用対象: SQL Server Azure SQL Managed Instance

指定されたテーブルまたはビューのインデックスをデフラグします。

重要

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

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

Transact-SQL 構文表記規則

構文

DBCC INDEXDEFRAG
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
    [ WITH NO_INFOMSGS ]

引数

database_name | database_id | 0

デフラグするインデックスが含まれているデータベース。 0 を指定すると、現在のデータベースが選択されます。データベース名は、識別子のルールに従っている必要があります。

table_name | table_id | view_name | view_id

デフラグするインデックスが含まれているテーブルまたはビュー。テーブル名とビュー名は、識別子の規則に従っている必要があります。

index_name | index_id

デフラグするインデックスの ID または名前。インデックス ID を指定しないと、ステートメントは指定されたテーブルまたはビューのすべてのインデックスをデフラグします。インデックス名は、識別子の規則に従っている必要があります。

partition_number | 0

デフラグするインデックスのパーティション番号。パーティション番号を指定しない場合、またはパーティション番号に 0 を指定した場合、ステートメントは指定されたインデックス内のすべてのパーティションをデフラグします。

WITH NO_INFOMSGS

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

解説

DBCC INDEXDEFRAG では、ページの物理順序がリーフノードでの左から右への論理順序と一致するように、インデックスのリーフレベルをデフラグするので、インデックスのスキャンのパフォーマンスが向上します。

Note

DBCC INDEXDEFRAG が実行されると、インデックスのデフラグが順次行われます。つまり、単一のスレッドを使用して、単一のインデックスについて操作が実行されます。並列処理は実行されません。また、同じ DBCC INDEXDEFRAG ステートメントからの複数のインデックスについての操作は、1 つのインデックスについて同時に実行されます。

また、DBCC INDEXDEFRAG では、インデックス作成時に指定された FILL FACTOR を考慮しながら、インデックスのページの圧縮も行います。この圧縮によって作成された空のページは削除されます。詳細については、「インデックスの FILL FACTOR の指定」を参照してください。

インデックスが複数のファイルにわたる場合、DBCC INDEXDEFRAG では一度に 1 つのファイルをデフラグします。ページがファイル間で移行されることはありません。

DBCC INDEXDEFRAG では、5 分ごとに完了した割合の予測値をレポートします。 DBCC INDEXDEFRAG は、プロセスのどの時点でも停止することができ、完了していた作業は保持されます。

DBCC DBREINDEX (または一般のインデックス構築操作) とは異なり、DBCC INDEXDEFRAG はオンライン操作です。長期にわたってロックが保持されることはありません。したがって、DBCC INDEXDEFRAG ではクエリや更新の実行はブロックされません。デフラグの所要時間は断片化のレベルに関係するため、比較的断片化されていないインデックスのデフラグは、新しいインデックスの構築にかかる時間よりも短時間で済む場合があります。著しくフラグメント化されたインデックスでは、デフラグすると再構築するよりも所用時間がかなり長くなる場合があります。

デフラグは、データベース復旧モデルの設定にかかわらず、常にすべてログに記録されます。詳しくは、「 ALTER DATABASE (Transact-SQL)」をご覧ください。著しくフラグメント化されたインデックスをデフラグした場合、インデックスの作成をすべてログに記録したときよりも多くのログが生成される可能性があります。ただし、ログのバックアップが頻繁に作成されているか、または復旧モデルに SIMPLE が設定されている場合、断片化の解消は一連の短いトランザクションとして実行されるため大量のログは必要なくなります。

制限

DBCC INDEXDEFRAG では、インデックスリーフページの再編成を行います。したがって、特定のインデックスがディスク上の他のインデックスと交互に配置されている場合は、そのインデックスに対して DBCC INDEXDEFRAG を実行すると、隣接するインデックス内では一部のリーフページが作成されません。ページが連続して配置されるようにするには、インデックスを再構築します。

DBCC INDEXDEFRAG を使用して、次のインデックスをデフラグすることはできません。

無効化されたインデックス。
ページロックが OFF に設定されたインデックス。
空間インデックスです。

DBCC INDEXDEFRAG は、システムテーブルでの使用ではサポートされていません。

結果セット

DBCC INDEXDEFRAG では、(WITH NO_INFOMSGS が指定されている場合を除き) インデックスがステートメント内で指定されている場合に、次の結果セットが返されます (値は異なることがあります)。

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359           346         8
  
(1 row(s) affected)
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

アクセス許可

呼び出し元はテーブルまたはインデックス付きビューを所有しているか、固定サーバーロール sysadmin、固定データベースロール db_owner、または固定データベースロール db_ddladmin のメンバーである必要があります。

例

A. DBCC INDEXDEFRAG を使用してインデックスをデフラグする

次の例では、PK_Product_ProductID データベースの Production.Product テーブルにある AdventureWorks2022 インデックスのすべてのパーティションをデフラグします。

DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO

B. 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

次の方法で共有