[SQL Server]Resume Online Index Rebuild-2

2018-02-10

Resume online index rebuild

這篇我來進行一些基本測試和驗證可靠性

我這裡預先建立一個big table為MyMovementCopy，並分別建立clustered 和nonclustered indexes。

CREATE CLUSTERED INDEX cidx ON dbo.MyMovementCopy 
  ( mk, Quantity, [Supplier Key], [Customer Key], [Date Key] DESC )
  WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX idx1 ON dbo.MyMovementCopy 
  ( [Date Key], Quantity ) INCLUDE ([Lineage Key])
  WITH (ONLINE = ON);

To see how much pages

set transaction isolation LEVEL READ UNCOMMITTED;
declare @tblname varchar(30)='MyMovementCopy'
declare @schname varchar(10)='dbo'
select 
    s.Name + N'.' + t.name as [Table]
	,i.index_id
    ,i.name as [Index] 
    ,i.is_unique as [IsUnique]
	,i.is_primary_key as [Isprimarykey]
	,ips.index_type_desc
	,ips.index_level
   ,ips.page_count
   ,ips.ghost_record_count
from 
    sys.tables t with (nolock) join sys.indexes i with (nolock) on
        t.object_id = i.object_id
    join sys.schemas s with (nolock) on 
        t.schema_id = s.schema_id
		left outer join sys.dm_db_index_physical_stats
 (DB_ID(), OBJECT_ID(@tblname), NULL, NULL, NULL)  ips on 
 ips.database_id=db_id()  and ips.object_id= i.object_id and ips.index_id = i.index_id
 where t.name=@tblname and s.Name=@schname
order by
    s.name, t.name, i.index_id
option (recompile)

Max_Duration

我預期執行1分鐘後並自動停止

alter index cidx on [dbo].[MyMovementCopy]
REBUILD 
with (online=on, resumable=on,max_duration=1)

check the current execution status for resume index rebuilds

SELECT total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count
FROM sys.index_resumable_operations;

1分鐘後，可以看到狀態從running變為paused，完成約33%，已經重新allocated index page=104855。

原本的session出現以下錯誤訊息

Msg 3643, Level 16, State 1, Line 1
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped. 
Msg 3643, Level 16, State 1, Line 1
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped. 
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded. 
Msg 3621, Level 0, State 0, Line 1
The statement has been terminated

Where is data store during resume rebuilds

SELECT index_id,allocation_unit_type_desc,page_type_desc,is_page_compressed, pages = COUNT(*) 
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.MyMovementCopy'), NULL, NULL, 'LIMITED')
GROUP BY index_id,allocation_unit_type_desc,page_type_desc,is_page_compressed;

可以看到有兩個索引副本，page數量大約都是原先的33%，這兩個隱藏索引你無法透過sys.indexes查看，

也無法透過sp_spaceused，所以disk space還是要留意，因為你無法對暫停的索引作業進行drop，如下

drop index cidx on [dbo].[MyMovementCopy]
	Msg 10637, Level 16, State 1, Line 1
Cannot perform this operation on 'object' with ID 1410104064 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Check disk space of transaction log for reusing

select
    used_log_space_in_bytes / 1024 / 1024  as used_log_space_MB,
    log_space_in_bytes_since_last_backup / 1024 / 1024 as log_space_MB_since_last_backup,
    used_log_space_in_percent
from sys.dm_db_log_space_usage;
go

select name,log_reuse_wait_desc
from sys.databases
where name='WideWorldImportersDW'
go

可以看到目前因為有活動交易，所以無法重用空間。

現在我執行交易紀錄檔案備份，看看是否可以重用空間。

backup log [WideWorldImportersDW] to disk='/mnt/backup/WideWorldImportersDW_log.bak'
with compression,init

備份完成後，等待就變成nothing，這表示我們可以shrink the transaction log。

Resume

alter index cidx  on [dbo].[MyMovementCopy] resume

resume可以讓我們接續未完成的索引作業，我們也可以正常備份log(除非暫停，交易才能commit，並重用空間)，

但這次將不會自動停止，因為我沒有再次指定max_duration，或我們手動執行pasue，如下

Note: alter index cidx on [dbo].[MyMovementCopy] resume WITH (MAX_DURATION =1 MINUTES)

alter index cidx  on [dbo].[MyMovementCopy] pause
Msg 1219, Level 16, State 1, Line 1
Your session has been disconnected because of a high priority DDL operation. 
Msg 1219, Level 16, State 1, Line 1
Your session has been disconnected because of a high priority DDL operation. 
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

至於abort則不會顯示任何錯誤訊息，而且相當快速。

alter index cidx  on [dbo].[MyMovementCopy] abort

另外，dm_exec_requests也多了一個is_resumable，讓我們可以掌握有那些正在執行resume作業

select is_resumable,*
from sys.dm_exec_requests
where is_resumable=1

Force to Stop sql server service

執行resume online index rebuild，我強制關閉sql server service(systemctl stop mssql-server.service)，

模擬發生意外crash，原本作業session會出現以下錯誤訊息

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - 遠端主機已強制關閉一個現存的連線。)