使用DROP_EXISTING參數重建叢集索引以降低交易紀錄

2017-06-20

MSDN官網在Create Index中對DROP_EXISTING功能的解說如下。

DROP_EXISTING = { ON | OFF }
Is an option to drop and rebuild the existing clustered or nonclustered index with modified column specifications, 
and keep the same name for the index. The default is OFF.

ON
  Specifies to drop and rebuild the existing index, which must have the same name as the parameter index_name.

OFF
  Specifies not to drop and rebuild the existing index. SQL Server displays an error if the specified index name already exists.

With DROP_EXISTING, you can change:
  A nonclustered rowstore index to a clustered rowstore index.

With DROP_EXISTING, you cannot change:
  A clustered rowstore index to a nonclustered rowstore index.
  A clustered columnstore index to any type of rowstore index.
  In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

由上述說明我們僅可以知道如果我們要利用不同欄位重建已存在之索引就可以搭配這一個參數使用，這樣的好處就是不用先Drop Index再Create Index。某日去上許致學老師的課，老師剛好有介紹到這一個參數。他對這個參數的介紹讓我恍然大悟，原來DROP_EXISTING的好處是可以降低交易紀錄的產生，我就用以下這篇文章就來簡易的說明。

如下圖所示我先建立兩個資料庫DB1及DB2，建立後先做一次資料庫備份動作，這樣SQL才會將這兩個資料庫用完整復原模式來運作。

接下來我用[sales].[SalesOrderDetail]當資料來源在DB1及DB2各建立一張名為tb1的資料表。

在DB1上的tb1資料表上建立一個叢集索引(CIX_tb1)及一個非叢集索引(IX_tb1_SalesOrderID)。

在DB2上的tb1資料表上建立一個叢集索引(CIX_tb1)及一個非叢集索引(IX_tb1_SalesOrderID)。

我們透過Sp_helpindex 'tb1' 檢視一下兩個DB的tb1的確建出相同的索引。

這時我們用DBCC SQLPERF(logspace)檢視一下DB1及DB2的交易紀錄檔使用量，如下圖所示可以看見兩個DB交易紀錄檔是72MB左右，而使用量約43%。

剛剛我們在tb1是用SalesOrderDetailID欄位來建立叢集索引，接下來我們改用如下圖紅色圈選的rowguid欄位來當叢集索引。

首先在DB1我們採用先Drop叢集索引再Create的模式來操作，如下圖所示。

而在DB2我們則是利用DROP_EXISTING = ON的方法重新Create叢集索引，如下圖所示。

完成DB1及DB2的tb1資料表重建後，我們再次利用DBCC SQLPERF(logspace)檢視雙方目前的交易紀錄檔空間使用量。如下圖所示我們可以發現兩個DB的交易紀錄檔還是72MB不變，但是DB1的交易紀錄使用空間成長到80%，而DB2的交易紀錄成長到71%。也就是DB1比DB2多了9%的交易紀錄量。

經過這樣簡單的LAB，我們可以知道使用DROP_EXISTING參數來重建叢集索引，的確可以減少交易紀錄。其原因是因為當我們Drop叢集索引時，該Table所有非叢集索引也都得一併更新(因為叢集變了)。而Drop完再Create時所有非叢集索引又得再更新一次(因為叢集又變了)。由此可知當您的資料表有非常多的非叢集索引的話，這樣的Drop再Create叢集可是要耗費非常多的資源的。而在改變叢集時如果是使用DROP_EXISTING參數，則SQL會利用本身機制來以最少的交易紀錄資源達到所有非叢集索引的更新。