DBCC INDEXDEFRAG (Transact-SQL)
重組指定資料表或檢視的索引。
.gif "重要注意事項") 重要事項 |
|---|
下一版的 Microsoft SQL Server 將不再提供此功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 請改用 ALTER INDEX。 |
.gif "主題連結圖示")
語法
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
要重組之索引的名稱或識別碼。若未指定，陳述式會重組指定之資料表或檢視表的所有索引。索引名稱必須符合識別碼的規則。partition_number | 0
這是要重組之索引的資料分割編號。若未指定，或指定 0，陳述式會重組指定索引中的所有資料分割。WITH NO_INFOMSGS
抑制所有嚴重性層級在 0 到 10 的參考用訊息。
備註
DBCC INDEXDEFRAG 會重新組織索引的分葉層級，使頁面的實體順序符合分葉節點由左至右的邏輯順序，以改進索引掃描的效能。
[!附註]
當執行 DBCC INDEXDEFRAG 時，會循序重新組織索引。這表示單一索引的作業是利用單一執行緒來執行。不會有任何平行處理原則。另外，相同 DBCC INDEXDEFRAG 陳述式多重索引的作業，每次只會處理一個索引。
DBCC INDEXDEFRAG 也會壓縮索引的頁面，它會將建立索引時所指定的填滿因數考慮在內。因這項壓縮而建立的任何空白頁面都會被移除。如需詳細資訊，請參閱＜填滿因數＞。
如果索引跨越多個檔案，DBCC INDEXDEFRAG 每次會重組一個檔案。在檔案之間，不會進行頁面的移轉。
DBCC INDEXDEFRAG 每五分鐘會報告一次估計的完成百分比。在這個處理序中，隨時可以停止 DBCC INDEXDEFRAG，任何已完成的工作都會保留下來。
DBCC INDEXDEFRAG 不像 DBCC DBREINDEX (一般而言，是建立索引的作業)，它是一項線上作業。它不會長期保留鎖定。因此，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 重組索引
下列範例會重組 AdventureWorks 資料庫之 Production.Product 資料表中 PK_Product_ProductID 索引的所有資料分割。
DBCC INDEXDEFRAG (AdventureWorks, "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