[SQL Server] Rebuild Index vs Reorganize Index

2018-01-17

摘要：

通常我們會需要用到Rebuild Index或Reorganize Index幾乎都是感覺Query速度變慢了，發現Index破碎率變高影響效能，才會開始決定是否重建或重組織Index

下面為這兩種行為的差別：

空間需求：

Rebuild Index:

額外需要一個可以容納新Index的空間 + Log增長的空間
不管現在的Index破碎率多高，它都會先建立一個新的Index，建完後再把舊的Index drop掉，所以在Rebuild Index前必須確保現在的disk有足夠的空間容納新的Index大小

Reorganize Index:

速度：

Rebuild Index:

Reorganize Index:

因為Reorganize Index是處理碎片，故如果破碎率越高，碎片越多，重組時間就越長。所以微軟建議如果Index的破碎率小於30%則使用Reorganize Index

Lock：

Rebuild Index 可以online或offline執行，而Reorganize Index則一律是online執行。

以Rebuild Index來說通常我們使用Online模式建，這是為了避免影響線上的DB，而這兩種模式的差別為(詳細說明)：

Offline：

會讓這個Table在執行Index的開始到結束都會有Sch-M的鎖來Lock Table，導致整個Table無法更新與讀取

在剛建立時會有短暫的share lock與intent-shared table lock，中間過程Table都是可以正常使用，直到最後結束時跟offline一樣會啟動Sch-M Lock來短暫封鎖所有外來的行為

是否可暫停：

Rebuild Index:

SQL Server2017以前的Rebuild Index不能暫停，只存在1跟0，沒有做一半，一但開始執行後想中斷或停止，只能等剛剛的行為全部都Rollback才能繼續作業．

SQL Server 2017以後多了Resumable-online index rebuild 的功能，可以有語法暫停再繼續，但是必須在執行語法時加入Online = On和RESUMABLE = ON的參數
詳見: https://www.mssqltips.com/sqlservertip/4987/sql-server-2017-resumable-online-index-rebuilds/

Reorganize Index:

可暫停與中斷，中斷後只會rollback剛剛執行到一半的那一個Page

總結：

如果空間不夠，我們只能選擇Reorganize Index來做；如果空間夠，可以參考破碎率來選擇要用哪一種方式（如下圖），但是有些時候反而是需要依據當時情況來選擇（其他案例詳細說明）

avg_fragmentation_in_percent 值	修正的陳述式
> 5% 且 < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON) 1

案例分享：

看完上面我們可以發現Rebuild 和Reorganize有些不同，但我們還是應該依照當時的狀況來決定要使用哪一個，下面分享一下最近一次我們團隊使用 Rebuild Index的案例：

當時我們是發現有個DB的備份檔越來越大，因為一些維護的困難而開始找尋到底是哪個Table導致DB變大，之後發現原來是某個Table的cluster Index Size過大，原因是因為此Table的Index破碎率很高，每個Page的使用率才5%

導致此cluster Index需要一直新增Page來容納新進的資料，才會有越長越大的跡象

我們當時就決定online的Rebuild這個Index （在Rebuild前我們都會先確認這個Table是否能允許短暫的lock，才敢Rebuild Index）

而這個Table因為資料量龐大，故我們大概等了一個多小時...才得到下面的結果：

可以看到Index的Size明顯降低了，我們也觀察到整個DB跟backup file都已經變小。

分享給大家，如果有哪邊說明錯誤再麻煩各位大大指教。

當Eva搭上DBA航班之後