INDEX REBUILD跟REORGANIZE的差異

  • 11273
  • 0
  • 2014-08-04

INDEX REBUILD跟REORGANIZE的差異

 

 

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

 

MSDN說明兩者差異如下:

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

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

 

 

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

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

clip_image002[4]

 

 

 

 

 

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

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

(所有頁面為2510) -  (9頁的索引) – (1IAM) = 2500頁的資料,而我們1Page8筆資料,

2500 * 8 = 20000筆資料。

PageType  1:資料頁面;2:索引頁面;3Lob_mixed_page4Lob_tree_page10IAM頁面

clip_image004[4]

 

 

 

 

 

 

 

 

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

clip_image006[4]

 

 

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

clip_image008[4]

 

步驟五:對該資料表叢及索引CIXINDEX REORGANIZE

clip_image010[4]

 

 

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

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

clip_image012[4]

 

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

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

clip_image014[4]

 

 

 

 

 

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

clip_image016[4]

 

 

 

 

 

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

clip_image018[4]

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

 

 

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

clip_image002

 

 

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

clip_image004

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

clip_image006

 

 

 

 

 

 

 

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

clip_image008

 

 

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

clip_image010

 

 

 

 

 

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

 

花費的時間

產生的交易紀錄量

REBUILD

241ms

12MB

REORGANIZE

1036ms

25MB

 

之前聽過兩位大師針對REORGANIZEREBUILD耗費資料庫資源的說法有所出入。一位表示REORGANIZE耗費資源較多,而另一位表示REBUILD耗費資源較多,當時很令我困擾。如今看來應該考量該索引破碎程度為何?破碎程度高則REBUILD較好,破碎程度低則REORGANIZE

 

 

Rock Chang 2014/8/4

我是ROCK

rockchang@mails.fju.edu.tw