[SQL Server]Resume Online Index Rebuild-1

2018-02-10

2329
0
SQL Server 2017
2018-02-11

Index rebuild將使用量大交易紀錄檔並耗費硬碟空間(無論online or offline)，

有時候重建大型索引，可能會遇到空間不足或blocking issue(long-running transaction)，

來看看SQL Server 2017如何幫DBA解決這問題。

SQL Server索引是否需要維護，目前存在兩種立場，是否真要使用index rebuild，也有兩種立場，

以我個人並沒有預設立場，我大多看實際情況決定，針對交易頻繁的索引，我盡量不使用index rebuild。

實務上，當我執行重建大索引，可能會遇到空間不足(交易紀錄檔案暴增，且online資源(row version)耗用遠高於offline)、

database failover造成rebuild failure、長時間的transaction造成blocking其他作業，

或是blocking issue by modify schema(you have to avoid full table scan)，

這時我們可能會取消重建大索引作業，但整批交易rollback一來對系統持續有影響，二又覺得之前時間都白白浪費了，

下次，當我又要執行同一個索引重建時，以前SQL Server只有一條路就是重新來過，

但SQL Server 2017將提供DBA一個新重建索引選項，就是Resume Online Index Rebuild新功能，

下面我簡單介紹。

P.S.:企業版才有支援 Online。作業進行時，不能截斷、修改或刪除base table。

效能因素

我很少使用Online選項，因為我曾遇過online rebuild unique index所造成的deadlock，

而且SQL Server永遠選擇犧牲應用程式(使用者)，另外，我覺得offline快速完成並使用較少資源會比較好，

當然，你還是得評估需求和資源可用度來決定是否要用online，

尤其注意clustered index 造成CPU過度，不要使用明確交易來進行，因為lock將直到交易結束才釋放，

這可能會影響concurrency users，還有要重建clustered index務必三思，

而我測試resume online index和一般online index的重建效能並無明顯差異。

Note:可設定MAXDOP來避免過度使用CPU

我個人認為這項新功能，對於大索引重建會有很大的幫助(尤其對AG)，

有時候我會遇到要進行Failover，但卡在有大索引維護作業而須等待完成，

這時，我們就可以先暫停大索引維護作業，等待failover完成後，在繼續進行大索引維護作業，

同時，DBA也有很大彈性分多次執行，啟用該功能，SQL2017看來會先commit完成的index row，

這樣我們執行backup log才能重用disk空間，來避免硬碟空間不足或長時間交易問題。

目前限制:

只支援row store index

無法使用sort_in_tempdb

無法支援computed 和timestamp 欄位

無法包交易

相關選項:

Resume: 必須設定on並搭配online=on才能啟用，預設off。

Max_Duration(minutes): 這選項我們應該會很常使用，這表示該作業最大執行時間，

當達到該時間後，就會自動停止，該值必須>0 and <= 10080 (one week)

PAUSE: 暫停作業

ABORT: 中斷作業

下面我將用sample database進行測試

使用wget下載sample database

wget https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

Check data file location

restore filelistonly from disk='/mnt/WideWorldImporters-Full.bak'

Restore database on CentOS

USE [master]
go
RESTORE DATABASE [WideWorldImporters] FROM DISK = N'/mnt/WideWorldImporters-Full.bak'
WITH FILE = 1, 
MOVE N'WWI_Primary' TO N'/mnt/db/datafile/WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'/mnt/db/datafile/WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'/mnt/db/logfile/WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'/mnt/db/xtpfile/WideWorldImporters_InMemory_Data_1',
NOUNLOAD, STATS = 5,recovery,replace
GO

Note:請注意相關資料夾要有存取權限