Resumable Online Index Create in SQL Server 2019
The Microsoft introduced with SQL Server 2019 CTP 2.0 new option in regards to Index Maintenance - Resumable Online Index Create. It allows during creating indexes pause this operation and continue later with RESUME parameter. When we are creating index we have to specify this option RESUMABLE = ON. In addition, we need to add one more parameter ONLINE = ON, because without this option our CREATE INDEX statement will failed.
Also you can declare MAX_DURATION greater than 0 or equal to 10080 minutes(one week). It's supported only with option RESUMBALE = ON.
Now, I'll show you how it works with some sample script.
First of all, we have to fulfill some prerequisites:
Installed SQL Server 2019 CTP 2.0
Management Studio 18.0
Compatibility Level 150
1) I will use my test database - PigeonSQL
Data file size is 5 GB
Log size is 5 GB
2) Then I'll create table Pigeons with some generated data
3) If will previous step is done, we will create a table
[dbo].[Index_Resumable_Actions] where we will during create online index operation write data from system catalog view
[sys].[index_resumable_operations].
Now, we are ready for demo.
We need to Create index statement in Query window 1. and during this, we will execute INSERT INTO statement in Query window 2..
Query window 1.:
Query window 2:
While Create index statement is running is Query window 1 we will execute INSERT INTO statement where WAITFOR DELAY, so execute only once. As you can see at the end of the script is command PAUSE:
This PAUSE command cause this state in Query window 1.
For continue create index operation we have to execute RESUME command below in new query window 3.:
Then immediately run this command in Query windows 4.
Once everything is done, use this select statement, where is visible where was Create Index statement paused, percentage completing or how many pages were processed. As you can see below:
We cannot use SORT_IN_TEMPDB = ON with option RESUMBALE = ON., also RESUMBALE = ON doesn't supports in explicit transaction.
Seee you next time!