By: Rajendra Gupta | Comments (1) | Related: > SQL Server 2017
Problem
Database performance optimization is always a key task for the DBA. Index maintenance plays a vital role in optimizing database performance. Sometimes in busy OLTP environments we have very limited maintenance windows and if an index is large we may not have enough time to rebuild the index. SQL Server 2017 provides a solution for this and in this tip we will see how we can use resumable index rebuilds.
Solution
SQL Server 2017 offers a new feature for maintaining indexes which we will cover in this tip.
Resumable Online Index Rebuild - Overview
SQL Server 2017 provides a key feature Resumable Online Index Rebuilds. In previous versions of SQL Server, we might need to cancel the index maintenance operation either due to running out of time in the maintenance period, blocking issues or maybe low disk space due to the transaction log filling up. When we cancel the index rebuild, we need to start the entire operation again to rebuild the index.
Resumable Online Index Rebuilds provide the option to pause an index rebuild and provides an option of executing, suspending, resuming or aborting an online index operation.
Note: Currently SQL Server 2017 is supporting only row store indexes.
Use of Resumable Online Index Rebuild
We can use the new feature in the following scenarios:
- We can resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space.
- We can pause an ongoing index rebuild operation and resume it later. For example we may need to temporarily free up system resources to execute a high priority task or due to a short maintenance window. Instead of aborting the index rebuild process, we can pause the index rebuild operation and resume it later without losing prior progress.
- We can rebuild large indexes without using a lot of log space and have a long-running transaction that blocks other maintenance activities. This helps log truncation and avoids out-of-log errors that are possible for long-running index rebuild operations.
The syntax for Resumable Online Index Rebuild is as below:
ALTER INDEX { index_name | ALL } ON <object> { REBUILD [ WITH ( <rebuild_index_option> [ ,...n ] ) ] } [ ; ] rebuild_index_option > ::= { …. | ONLINE = { ON [ ( <low_priority_lock_wait> )] | OFF } | RESUMABLE = { ON | OFF } | MAX_DURATION = <time> [MINUTES] | PAUSE | ABORT | … }
We can see the options below that can be used:
- ONLINE: Resumable index rebuild supports only online index rebuild, so we have to specify ONLINE = ON.
- RESUMABLE: If we want to use the resumable index rebuild functionality, we need to execute the online rebuild with RESUMABLE option. If we want to do a normal online index rebuild then either specify RESUMABLE=OFF or don't specify this option. RESUMABLE=OFF is the default value.
- MAX_DURATION= [MINUTES]: This is also an interesting and useful option to specify the max duration. It specifies the number of minutes that the reusable online index operation will be executed, before being suspended. The time in minutes for this MAX_DURATION option, must be greater than 0 and less or equal to one week (7 x 24 x 60= 10080 minutes).
- PAUSE: It pauses the resumable online index rebuild operation.
- ABORT: It aborts a running or paused resumable online index rebuild operation.
Now lets see how to use this functionality.
To perform a test, I will be using the sample database WideWorldImporters and table [Sales].[InvoiceLines].
First let's check the clustered index on this table.
select o.name as table_name, i.name as index_name, p.index_id, au.type_desc, au.used_pages, p.rows from sys.allocation_units as au join sys.partitions as p on au.container_id = p.hobt_id join sys.objects as o on o.object_id = p.object_id left join sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id where p.object_id = object_id('[Sales].[InvoiceLines]') and i.index_id=1
Now we will initiate an online index rebuild using the resumable index option. To do this, open a query window and run the below query.
ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines] REBUILD WITH (ONLINE = ON, RESUMABLE = ON); GO
Note that we have to specify ONLINE = ON with RESUMABLE = ON, if we don't specify ONLINE=ON then it will throw the below error:
Once the index rebuild is running, open a new query window and run the below query:
ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines] PAUSE
Once the above query completes, notice the first query window's output.
This is not an error message, this is the expected output. We can see in the output that it kills the session under which the index rebuild is running. So if we are running the index maintenance using a SQL Agent job, it will kill the job under which the index rebuild is running.
SQL Server 2017 includes a new system view sys.index_resumable_operations to monitor and check the current execution status for resumable index rebuilds.
SELECT total_execution_time, percent_complete, name,state_desc,last_pause_time,page_count FROM sys.index_resumable_operations;
We can see that we have paused the online resumable index rebuild operation after 50.94% completion, the current status is PAUSED and last_paused_time is 2017-07-02 12:08:14.110.
We cannot drop the index while the index is in a PAUSED state, if we try we will get the below error.
drop index PK_Sales_InvoiceLines on [Sales].[InvoiceLines]
Suppose we have paused an online index rebuild operation due to a space issue as our transaction log has grown. The best part is that we can shrink the transaction log with the index rebuild status being PAUSED.
First let's check the transaction log usage while the index rebuild status is paused.
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
If we check the log_reuse_wait_desc status, we can see that a LOG_BACKUP is required.
After a log backup is completed, we will be able to shrink the transaction log.
We can see that now the status of the database for log_reuse_wait_desc is NOTHING which means we can shrink the transaction log.
Resume the Online Index Rebuild Operation
To resume the Online Index operation that we paused earlier, we need to use the below query.
ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines] RESUME
If we try to run the index rebuild again, it will not do the rebuild process from the start, however it will resume the index rebuild operation.
Once the rebuild is complete, we won't see any entries in the sys.index_resumable_operations.
Abort the Online Index Rebuild Operation
If we want to abort the resumable index rebuild operation, we can do so by specifying the Abort option.
ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines] Abort
With this we can see that the index operation will abort in the session where the index rebuild is running..
Resumable Index with MAX_DURATION Option
We don't want our index maintenance operation to run during business hours. Currently we don't have an option to stop the index rebuild after a specific duration. In SQL Server 2017, we now have the option to specify the Max_Duration in terms of minutes to run for the resumable index rebuild.
Suppose I want my resumable index rebuild to run for maximum of 1 minute and then stop automatically, so my query will be.
ALTER INDEX [ix_ColdRoomTemperatures_Archive] ON [Warehouse].[ColdRoomTemperatures_Archive] REBUILD WITH (ONLINE = ON, RESUMABLE = ON,MAX_DURATION =1 MINUTES); GO
Now let's run the query and we can see it stops after 1 minute.
We can see that query executed for a maximum of 1 minute and then automatically went to a PAUSED state.
Similarly if want to resume the operation and want it to run for specific duration, we can specify Max_Duration as well.
ALTER INDEX PK_Sales_InvoiceLines ON [Sales].[InvoiceLines] Resume WITH (MAX_DURATION =1 MINUTES)
Updated sys.dm_exec_requests DMV
DMV sys.dm_exec_requests was updated in SQL Server 2017 with an additional column is_resumable to identify Resumable Online Index Rebuild operations.
Restriction for Resumable Index Rebuild
Some things to note:
- It works only on row store indexes.
- It doesn't work with the SORT_IN_TEMPDB option.
- It doesn't work with TimeStamp columns.
- It doesn't work with Computed Columns.
- We cannot use it on Disabled Indexes.
- The RESUMABLE INDEX statement cannot be used inside a user transaction.
Next Steps
- We will explore more about SQL Server 2017 in future tips.
- Explore SQL Server 2017 preview
- Read more SQL Server 2017 Tips.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips