• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

Resumable Online Index Create in SQL Server 2019

January 13, 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!

 

 

 

 

 

Please reload

RECENT POST
Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon