Did you know that SQL Server supports pausing and resuming the index creation and rebuild processes? The resumable online index rebuild option is currently supported in Azure SQL Database and SQL Server 2017 With the introduction of the public preview of Azure SQL Database and the community technical preview (CTP) of SQL Server 2019, the SQL Server team has enhanced the pause and resume functionality by added the resumable online index create option.
If you have ever had to cancel an online rebuild or create index process on a very large table because the index rebuild or creation process didn’t complete during your maintenance window, or consumed valuable resources causing other queries to run slow, then you need to check out the resumable online index options.
The worse part about canceling the rebuild or index creation process in an older release of SQL Server was that the whole process rolled back, and all that work done prior to the canceling was wasted. It would be nice if you could restart your cancelled online rebuild or create index statement where it left off, instead of starting the index rebuild or creation process all over again.
If you are running SQL Server 2017, you can pause and restart the index rebuild operation, but not the create index options. Although, if your database is running in the preview of an Azure SQL database or SQL Server 2019, you can pause and restart both the online rebuild and index create processes. In this article, I will show you to pause and restart your online index operations.
Overview of Resumable Online Index Operations
When you cancel an index rebuild or a create index operation in SQL Server prior to SQL Server 2017, the database engine must roll back all the work it had done on the index. Because of this, when you restart the index rebuild or create index process, SQL Server has to start all over again at rebuilding or creating the index. This causes lots of processing and requires resources just to redo what was done prior to cancelling the indexing rebuild or create index process.
But if you migrate your older versions of your databases to SQL Server 2017, you can restart your online index rebuild operations. Plus, with the rollout of the previews of Azure SQL Database or SQL Server 2019, you can pause and restart both your online rebuilds and creation processes.
Being able to pause these online index operations allows SQL Server to pick up the rebuild or create index operations where they left off. The ability to retain the work done when pausing an online index operation allows you to perform the index operation in a piecemeal approach. Restarting a create or rebuild an index on a really big table by pausing and resuming the indexing process allows you to work around your short maintenance windows and other resource intensive queries.
Sample Test Data
In order to test out the new pause and resume functionality of online resumable index operations, I decided to create a table that contains 60 million rows. If you want to follow along and run the code in this article, you can use the code found in the Listing 7 in the last section of this article titled “Create Sample Data.” (Another option is to create the table and populate it using Redgate’s SQL Data Generator.) That code creates a database named SampleData and then creates and populates the Visits2 table. The table definition looks like the image found in Figure 1:
Figure 1: Definition for table Visits2
I created this database on the CTP 2.2 version of SQL Server 2019, so I could test pausing and restarting both the index creation and rebuild operations. You can download that version of SQL Server here.
How to Pause and Resume an Online Index Operations
To test out the new pause and resume functionality of an online index operation, you will first create a non-clustered index online. The index will be on the I100 column of the Visits2 table using the script in Listing 1. Keep in mind the resumable option for the create index operation is only available in the preview versions of Azure SQL Database and SQL Server 2019 at the time this article was written. You cannot create a resumable online index in any version of SQL Server except the CTP versions I just mentioned.
Listing 1: Create an Online Resumable Non-Clustered Index
|
1 2 |
CREATE INDEX NC_Visits2_1 ON dbo.Visits2 (I100) WITH (ONLINE = ON,RESUMABLE = ON); |
The RESUMABLE=ON parameter in the WITH clause is what makes this CREATE INDEX command resumable. Note that you also have to include the ONLINE = ON clause when you use the RESUMABLE option. If you leave off the ONLINE option, you will get an error.
The next step of testing out the resumable index creation is to pause the create index process. After allowing the resumable index creation operations to process for a while, you can PAUSE this index create operation, by running the code in Listing 2 in a new query window:
Listing 2: Pausing the resumable online index creation process
|
1 |
ALTER INDEX NC_Visits2_1 ON dbo.Visits2 PAUSE; |
When you run the code in Listing 2, you will find that the session that was running the CREATE INDEX statement gets disconnected and the messages in Figure 2 are displayed.
Figure 2: Message from pausing the resumable index creation process
Once you have paused your online resumable index operation you can determine how far along the process progressed prior to being paused by using a new system view that came out in the current version of Azure SQL Database, or SQL Server 2017 named sys.index_resumable_operations. By running the code in Listing 3, you can see how far along the online resumable index creation operation progressed prior to being paused.
Listing 3: Show how far the resumable index create operation got prior to being paused
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE SampleData; GO SELECT name as index_name, percent_complete, state_desc, start_time, last_pause_time, total_execution_time AS ExecutionMin, -- execution time times -- ratio of percent to complete and precent completed total_execution_time * (100.0-percent_complete)/percent_complete AS ApproxExecutionMinLeft FROM sys.index_resumable_operations; |
When you run the code in Listing 2, you will see something like the output in Figure 3.
Figure 3: How far the index creation process got prior to being paused
By looking at the pecent_complete column in Figure 3, you can see that the progress shows 64.33% through the creation process. Plus if you look at the state_desc column, in Figure 3, you can see the index creation process is in a PAUSED state. Note you might also want to use the sys.index_resumable_operations system view to review the progress status of your index prior to pausing it, just to see if it is almost finished. No sense in pausing something that is just about ready to complete.
To restart a paused indexing process, all you have to do is run the code in Listing 4 in a query window.
Listing 4: Code to resume the indexing creation process
|
1 2 3 4 |
USE SampleData; GO ALTER INDEX NC_Visits2_1 ON dbo.Visits2 RESUME; GO |
When you run the code in Listing 4, the index creation process will pick up where it left off and continue creating the index. You can verify that the index creation process is running again and starts where it left off by running the code in Listing 3 again. The output you get from the second run of this code will show that your index creation process is now running, and the percent completed should be slightly further along than what was shown the first time you ran this code.
When you restart the index process using the code in Listing 4, the ALTER INDEX statement continues to run until the index process completes. When the index is done being created you will get a message indicated that the ALTER INDEX statement completed successfully in the message window.
The next test is to determine if you can run two online resumable index operations on the same table, at the same time. To test that out you can run the code in Listing 5, while the original online resumable index process is running using the code in Listing 1.
Listing 5: Starting a second resumable index operation on same table
|
1 2 3 4 |
USE SampleData GO CREATE INDEX NC_Visits2_2 ON dbo.Visits2 (I100) WITH (ONLINE = ON,RESUMABLE = ON); |
You might be thinking you could start a second non-clustered online resumable online index process on the same table while an existing online reusable index is running. However, if you try to start a second non-clustered online resumable index operation, or any index operation, on the same table as one already running you will get the error showing in Figure 4.
Figure 4: Error when trying to run second resumable online index operation
As you can see you can’t run a second resumable index operation while the first one is running.
You also might think you could just pause the first index operation so you can start the second one. But if you try this you will get the messages shown in Figure 5.
Figure 5: Error message when starting second index operation when another one is paused
As you can see SQL Server does not support running a second online resumable index operations at the same time, even if one of them is paused.
So far, you have just tested out pausing and resuming an online resumable index create operations. But you can also pause and resume an index rebuild operation by adding the RESUMABLE = ON option to your ALTER INDEX statements. The code in Listing 6 shows how you would run an online resumable REBUILD operation on the NC_Visits2_1 non-clustered index.
Listing 6: Code to start an online resumable index rebuild
|
1 2 |
ALTER INDEX NC_Visits2_1 ON dbo.Visits2 REBUILD WITH (ONLINE = ON, RESUMABLE = ON); |
As already stated, if you are only running the current GA version of SQL Server, whether it is Azure SQL Database, or SQL Server 2017 you can only perform online resumable index rebuild operations and not index create operations. You need to be running the new CTP version of SQL Server in order to run the online resumable index creation operations.
I think you will agree that being able to pause and restart the online index creation or rebuild processes is a great enhancement, especially when you what to create an index or rebuild and index on a very large table. With this new resumable option, SQL Server makes it simple to pause and restart the online index creation and rebuild processes. Being able to do this provides the ability to better manage resources and maintenance windows while you run a long running online index create or rebuild operation.
Observations
When I was reviewing this new pause and resume functionality for the online index creation and rebuild operations, I made the following observations:
- You can only pause the index creation process if you have specified the
RESUMABLEoption on the create or rebuild index statement. If you already have scripts to create or rebuild your indexes online don’t forget to put this new option in your scripts if you want to pause and resume your indexes. Also note you can only use theRESUMABLE=ONoption if you also include theONLINE=ONoption as well. - When you pause your resumable create index or rebuild operation, the session running the index create or rebuild statement will be canceled and disconnected. The messages you get when this happens seem is a little different than I would have expected (see Figure 2). It would be nice if the messages would have said something like Your reusable index operation has been PAUSED. To restart use the ALTER INDEX with the RESUME option. Since the message about being disconnected is the message that was used to support the online resumable rebuild operation in the current Azure SQL database, or SQL Server 2017, I don’t think the SQL Server team are going to change this message to have any text that references the resumable operation.
- You cannot cancel your resumable index create or rebuild operation by just terminating these index operations. When you kill or cancel your resumable index operations the index goes into a
PAUSEDstate. To truly cancel your resumable index operation completely you will need run anALTER INDEXstatement with theABORToption. - You can only create one resumable online index operation on a table at a time. It really is too bad you can’t start multiple index rebuild operations if you have the system resources to do it, but this is just the way it works.
- When using the
RESUMABLEoption you can also add aMAX_DURATIONoption. By using theMAX_DURATION, you can set the number of minutes a resumable index create or rebuild operation will run. When that time limit is reached, the resumable index process will be killed, in affect pausing the index creation or rebuild operation. This would be a useful option to use when you want to perform your index operation in a piecemeal approach. - When creating a new index using the RESUMABLE option, you will not see it listed in sys.indexes or in SSMS until the operation has completed. To find the index, use the
sys.index_resumable_operationssystem view.
Pausing/Restarting Online Index Operations
The introduction of the resumable online index rebuild feature of SQL Server 2017 and Azure SQL Database, and the addition of the resumable online index create feature that is available in the preview versions of Azure SQL Database and SQL Server 2019 are great improvement to the index create and rebuild function. By having this new pause/restart functionality, you no longer need to run your create index, and rebuild operations as a single uninterrupted process. Being able to pause and resume your online index create and rebuild operations allows you a way to do these operations in a piecemeal approach. If this roll back feature of canceling a rebuild or create online index process has been a pain point, then you should be excited to realize that SQL Server now supports resumable index operations. By moving your database to SQL Server 2017 today, you can get the online resumable index rebuild functionality now. But you are going to have to wait until Microsoft rolls out the newest versions of SQL Server code base into the GA version of Azure SQL Database and SQL Server 2019 in order to use the resumable online index create functionality.
Create Sample Data
In Listing 5, you will find the code I used to create my contrived sample database (SampleData) and the table (Visits2) I used to support my testing for this article.
Listing 7: Create Sample Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
-- Create Sample DB with SIMPLE recovery CREATE DATABASE SampleData CONTAINMENT = NONE ON PRIMARY ( NAME = N'SampleData', FILENAME = N'D:\SQL Server 2019\SampleData.mdf' , SIZE = 4GB, FILEGROWTH = 1GB) LOG ON ( NAME = N'SampleData_log', FILENAME = N'D:\SQL Server 2019\SampleData_log.ldf' , SIZE = 1GB, FILEGROWTH = 1GB); GO ALTER DATABASE SampleData SET RECOVERY SIMPLE; GO -- Set database context USE SampleData; GO -- Create table to hold sample data CREATE TABLE Visits2 ( ID INT, I100 INT, I1000 INT, I10000 INT, I100000 INT, I1000000 INT, I10000000 INT, IP_Address VARCHAR(15), VisitDate DATE ); GO -- Create Tally Table GO CREATE VIEW vw_Tally AS --Itzik style tally table WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0) ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4 ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16 ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256 ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536 ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296 ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5) SELECT TOP (1000000) n FROM Tally ORDER BY n; GO -- Populate Visits2 with sample data SET NOCOUNT ON; DECLARE @Max bigint = (select ISNULL(max(ID),0) From Visits2); WHILE @Max < 60000000 BEGIN WITH TallyTable AS ( SELECT n + @Max as N, CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A4, CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A3, CAST(RAND(CHECKSUM(NEWID())) * 255 as INT) + 1 AS A2, 1.0 + floor(1 * RAND(convert(varbinary, newid()))) AS A1, DATEADD(DD, 1.0 + floor(62 * RAND(convert(varbinary, newid()))),'2018-07-01') AS VisitDate FROM vw_Tally) INSERT INTO Visits2 (ID, I100, I1000, I10000, I100000, I1000000, I10000000, IP_Address, VisitDate) SELECT n,n%100, n%1000, n%10000,n%100000, n%1000000, n%10000000, CAST(A1 AS VARCHAR) + '.' + CAST(A2 AS VARCHAR) + '.' + CAST(A3 AS VARCHAR) + '.' + CAST(A4 AS VARCHAR), VisitDate FROM TallyTable set @Max = (select ISNULL(max(ID),0) From Visits2); END |
Load comments