INDEX REBUILD跟REORGANIZE的差異

2014-08-04

11264
0
2014-08-04

看過很多書籍或文章都有寫到關於INDEX REBUILD或是REORGANIZE的建議，一般都是建議索引破碎率(avg_fragmentation_in_percent 值)達30%以上就REBUILD。> 5% 和 < = 30%則可以REORGANIZE。

MSDN說明兩者差異如下:

您可以重新組織或重建索引以修復索引片段。對於在資料分割配置上建立的資料分割索引，您可以在完整的索引或在索引的單一資料分割上使用這些方法。重建索引會先卸除再重新建立索引。這會移除片段；根據指定的或現有的填滿因數設定壓縮頁面來收回磁碟空間，以及重新排序連續頁面中的索引資料列。當指定 ALL 時，會在單一交易中卸除和重建資料表的所有索引。重新組織索引只需使用極少的系統資源。它會實際重新排序分葉層級的頁面，使它們由左至右符合分葉節點的邏輯順序，以重新組織資料表和檢視表之叢集和非叢集索引的分葉層級。重新組織也會壓縮索引頁。壓縮是根據現有的填滿因數值。

資料來源: http://msdn.microsoft.com/zh-tw/library/ms189858.aspx

步驟一:下圖Script中我們建立一張資料表，每一筆資料為1003個Bytes，而一個Page為8060 Bytes，所以一

頁可以存放8筆資料，本LAB我預設INSERT 20000筆資料。

步驟二:我們利用DBCC IND(abc2,ClusteredTable,-1)來看看該資料表索引配置狀況，該資料表

索引所佔的頁面為9頁(PageType=2)，IAM頁面為1頁(PageType=10)，而其他PageType為1的就是資料本身。

(所有頁面為2510頁) - (9頁的索引) – (1頁IAM) = 2500頁的資料，而我們1個Page約8筆資料，

故2500 * 8 = 20000筆資料。

PageType 1：資料頁面；2：索引頁面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM頁面

步驟三:我們利用DBCC PAGE(abc2,1,467,3)檢視一下編號467索引頁的內容為何，我們可以從下圖中發現該索引共存放了617筆資料。其中id(Key)欄位及ChildPageId欄位就是索引的核心。記錄著哪個RANGE的資料該到哪一個PAGE去找。

步驟四:我們直接刪除一半的資料。

步驟五:對該資料表叢及索引CIX做INDEX REORGANIZE。

步驟六: 我們利用DBCC IND(abc2,ClusteredTable,-1)再來檢視一下該資料表配置狀況有甚麼不一樣的改變，

我們可以發現索引頁面一樣是9頁，還有一頁IAM。但是分葉層的頁數減少到剩下1254頁。

步驟七:我們再利用DBCC PAGE(abc2,1,467,3)檢視一下編號467索引頁的內容看看有什變化呢?如下圖紅色圈選處，我們發現該Page的資料筆數由步驟三的617筆降為309筆，因此該page大概有一半的空間是沒有資料。也就是表示

INDEX REORGANIZE並不會對中間層索引做壓縮，因此中間層索引的填滿率會下降，但它會實際重新排序分葉層級的頁面並根據現有的填滿因數值壓縮分葉層級索引頁。

步驟八:我們重作步驟一到步驟四，但這一次我們是直接做INDEX REBUILD。

步驟九: 我們利用DBCC IND(abc2,ClusteredTable,-1)再來檢視一下INDEX REBUILD後該資料表配置狀況跟INDEX REORGANIZE有甚麼不一樣，我們可以透過下圖發現中間層索引頁面減少為5頁，還有1頁IAM。分葉層的頁數減少到剩下1254頁。

經過這幾個步驟下來我們大致可以了解REBUILD跟REORGANIZE的差異在哪裡。REBUILD會把舊的INDEX給DROP掉後再重新建立新的索引，所以不管中間層或分葉層空間填滿率都是根據填滿因素重建。而REORGANIZE會重新排序分葉層的資料且會根據填滿因素來壓縮分葉層。但對於中間層的索引就不會這樣處理，因此中間層索引會有空間浪費的情形。

補充 : 經Jmaes Fu大哥的建議可以針對本LAB加入LDF的成長情形及REBUILD和REORGANIZE時所耗費的時間等數據資料。因此我針對這兩項指標補充一下測試結果。首先我一樣是新增完20000筆資料後再刪除奇數列的資料共10000筆。然後我將LDF壓縮到5M大小，如下圖所示。

由下圖中我們可以發現整個REORGANIZE共花了1036ms。

由下圖中我們可以發現整個REORGANIZE導致LDF成長了25MB的資料量。

由下圖中我們可以發現整個REBUILD只花了241ms。

由下圖中我們可以發現整個REBUILD過程讓LDF增加了12MB的交易紀錄。

下表是我們針對花費時間及產生的交易紀錄量所取得的結果，很意外的發現REORGANIZE所耗費的資源都比REBUILD多。似乎跟MSDN中描述 重新組織索引只需使用極少的系統資源 不一樣，個人認為應該是和索引破碎率有關聯。當破碎率過高時使用REORGANIZE時反而需要消耗更多的資源，此時REBUILD索引會較好。