DBCC INDEXDEFRAG (Transact-SQL)

文章
05/04/2023

重組指定資料表或檢視的索引。

重要

未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。請改用 ALTER INDEX。

適用於：SQL Server 2008 (10.0.x) 和更新版本

Syntax

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 ]

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法，請參閱舊版文件。

引數

database_name | database_id | 0

包含要重組之索引的資料庫。如果指定 0，就會使用目前的資料庫。資料庫名稱必須符合識別碼的規則。

table_name | table_id | view_name | view_id

包含要重組之索引的資料表或檢視。資料表和檢視表名稱必須符合識別碼的規則。

index_name | index_id

要重組之索引的名稱或識別碼。若未指定，陳述式會重組指定之資料表或檢視表的所有索引。索引名稱必須符合識別碼的規則。

partition_number | 0

這是要重組之索引的資料分割編號。若未指定，或指定 0，陳述式會重組指定索引中的所有資料分割。

WITH NO_INFOMSGS

抑制所有嚴重性層級在 0 到 10 的參考用訊息。

備註

DBCC INDEXDEFRAG 會重組索引的分葉層級，使頁面的實體順序符合分葉節點由左至右的邏輯順序，因此改進索引掃描效能。

注意

當執行 DBCC INDEXDEFRAG 時，會循序重組索引。這表示單一索引的作業是利用單一執行緒來執行。不會有任何平行處理原則。另外，相同 DBCC INDEXDEFRAG 陳述式多重索引的作業，每次只會處理一個索引。

DBCC INDEXDEFRAG 也會壓縮索引的頁面，因此會將建立索引時所指定的填滿因數考慮在內。因這項壓縮而建立的任何空白頁面都會被移除。如需詳細資訊，請參閱指定索引的填滿因素。

如果索引跨越多個檔案，DBCC INDEXDEFRAG 每次會重組一個檔案。在檔案之間，不會進行頁面的移轉。

DBCC INDEXDEFRAG 每五分鐘會報告一次估計的完成百分比。在過程中，隨時可以停止 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

分享方式：