SQL Server Rebuild Index

標籤：style   blog   http   io   ar   color   os   使用   sp   

重建索引是為了減少資料片段。資料片段會導致SQL Server進行不必要的資料讀，降低SQL Server的效能。重建索引也會同時更新列統計，而如果查詢所使用的列缺少或遺漏統計資訊，這可能導致SQL Server內部的最佳化器選擇比預期效率低的查詢計劃。

如果您重建了某張表上的叢集索引，該表上的非叢集索引也同時會被更新。

要更新索引，您可以使用Maintenance Wizard（相關內容您可以參考http://msdn.microsoft.com/en-us/library/ms180074.aspx），或在SQL Server代理（Agent）中運行如下的自訂代碼來更新某個資料庫中所有表上的索引：

 

USE DatabaseName --Enter the name of the database you want to reindex  DECLARE @TableName varchar(255)  DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table‘  OPEN TableCursor  FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,‘ ‘,90) FETCH NEXT FROM TableCursor INTO @TableName END  CLOSE TableCursor  DEALLOCATE TableCursor

 

您可以根據您的需求修改DBREINDEX的參數。

需要注意的是，重建非叢集索引時該表會暫時加上共用鎖定，對使用者不可進行SELECT以外的操作；重建叢集索引時該表會暫時加上排外鎖，不允許任何使用者訪問。因此需要制定好計劃來預防可能的訪問問題。

 

REBUILD有一個fill factor參數，如果fill factor設定為100%，這意味著每一個索引頁都是完全滿的，如果fill factor設定為50%意味著每個索引頁都是半滿的。對於fill factor 100%，每次新插入或更新一個記錄，由於當前頁沒有空間可用，可能有分頁情況產生。過多的分頁會降低SQL Server的效能。下面具體舉個例子：

假設您在一張表上建立了一個使用預設fill factor的新索引。當SQL Server建立索引時，它會把索引放置在連續的物理頁上，以使資料順序地被讀，I/O訪問最佳化。但當表因INSERT，UPDATE，DELETE等操作增長改變時，分頁發生，SQL Server在磁碟的其他地方分配新的頁，導致新的頁與原物理頁不連續，增加了隨機I/O，訪問索引頁變慢。

 

那麼fill factor的合適值應該為多少？這取決於表的讀/寫比：

低更新表（讀/寫比：100比1）：100% fill factor

高更新表（寫超過讀）：50%-70% fill factor

置中：80%-90% fill factor

過低的fill factor會增加頁的數量，也會導致更多的頁需要被移至緩衝，緩衝中有用的資料減少。預設的fill factor為0（即100% fill factor），通常這不是個好的選擇，特別是對於叢集索引。

如果您無法判斷設定什麼fill factor,您首先需要確定磁碟的讀/寫比.方法就是使用如下兩個計數器:

Physical Disk Object: % Disk Read Time 和 Physical Disk Object: % Write Time。另外一個可能有用的計數器就是：SQL Server Access Methods: Pages Splits/Sec。這個計數器測量SQL Server內每秒分頁的次數。如果該數值過高，您需要降低fill factor防止新的分頁。

 

如果您想確認您的索引因分頁產生的片段程度，您可以運行DBCC SHOWCONTIG命令。如果看特定表和特定索引，您可以運行如下代碼：

結果集中最重要的參數是Scan Density，越接近100%越好。如果Scan Density小於75%，那麼您可能需要重建表中的索引。

 

--Script to identify table fragmentation  --Declare variables DECLARE @ID int, @IndexID int, @IndexName varchar(128)  --Set the table and index to be examined SELECT @IndexName = ‘index_name‘ --enter name of index SET @ID = OBJECT_ID(‘table_name‘) --enter name of table  --Get the Index Values SELECT @IndexID = IndID FROM sysindexes WHERE id = @ID AND name = @IndexName  --Display the fragmentation DBCC SHOWCONTIG (@id, @IndexID)

 

對於小於100資料頁，重建索引並不會有明顯的效能改善。這是因為物理硬體緩衝，SQL Server緩衝和SQL Server預讀機制隱藏了片段的負面作用。但對於非常大的表，重建索引會使它受益匪淺，因為涉及大量磁碟I/O操作。

SQL Server Rebuild Index