Prashanth Jayaram

Overview of Resumable Indexes in SQL Server 2017

June 26, 2017 by

Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

This article outlines

  • Overview of the indicators of disk space issues during heavy index rebuilding maintenance
  • Index maintenance options available in SQL 2017
  • How to use index_resumable_operations
  • Application of these settings on SQL partitions
  • A demonstration
  • And more…

Background

Being a database administrator, understanding the implication of fragmentation takes precedence over fixing it. Often we don’t think about fragmentation details, and the proceed to rebuild (or defrag) every index in the database. This operation eats up a significant amount of transaction log space, and has a substantial impact on the system resources, backup size, restoration time, etc. Finding the Fill Factor parameter and setting it to a right value works great to deal with fragmentation, but in many cases, finding this value is a tedious job.

Rebuilding indexes requires additional disk space, and an inadequate disk space can negatively impact the I/O, and degrade system performance; sometimes the entire operation may fail.

The index rebuild operation typically has an option to sort the results in a system database called “TempDB”. The option, SORT_IN_TEMPDB, is set to OFF by default for each index. When using this option, one must ensure that there is enough room to expand TempDB. If not, we must at least make sure that there is enough room on the disk on which the user database resides.

If the index rebuild maintenance operation fails due insufficient disk space, the operation has to be started from the beginning. In many instances, administrators struggle to manage the load on the system resources, and wish to pause and resume the operation at a later point.

Introduction

On SQL Server 2007 CTP 2.0 there is an Alter Index feature to pause and resume indexes during maintenance, which offers several ways to maintain the indexes at a very granular level. Previous versions of SQL Server gave a different dimension to index maintenance operations, but this feature has something noteworthy.

This feature is very useful for database administrators who perform periodic index maintenances at regular intervals of time. This option provides us with the flexibility to pause, start, and abort the index rebuild operations. Of course, there is overhead on storage, but, in general, this is a pretty cool feature.

Here is an outline of the SQL 2017 Resumable Index Rebuild features and other considerations.

  • Better planning and a greater flexibility in the management of indexes during maintenance windows. Provides an option to pause and restart an index rebuild operation multiple times.
  • Flexibility to recover from index rebuild failures, since the process does not necessarily have to start from the beginning.
  • Space can be a game-changer (storage constraint), when an index operation is paused—both, the original index and the newly created one require disk space.
  • Better log space management since it enables truncation of transaction logs during an index rebuild operation.
  • SORT_IN_TEMPDB=ON is not supported.

Syntax

Limitations

  • The SORT_IN_TEMPDB=ON option is not supported
  • Resumable index operations are not supported on disabled index
  • Columnstore index is not supported

Demonstration

I’ve chosen a compressed data warehouse fact table named ftb_Material_Issue for the entire demo. The stats and metrics have been gathered at regular intervals, throughout the index rebuilding operation with index resumable option.

Execute an online index rebuild as a resumable operation with MAXDOP=1.


The second run with the same options would reveal that there is already a process with the same options running; we can resume it from where it stopped.


After running it for a few seconds, stop or cancel the execution, and check for the statistics using sys.index_resumable_operations.

The sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable index rebuild. Let’s now look into the view to understand the state of the operation. This view gives us details of the progress of the entire operation, such as the total number of index pages allocated by the operation. The following query shows our operation that was paused.


The sys.index_resumable_operations column details

Column name Data type Description
object_id int Every object has an ID, called the object identifier.
index_id int This is the unique index identifier; this field is not nullable either; index_id is unique only within the object.
name sysname This is the unique name of the index.
sql_text nvarchar(max) This is the DDL T-SQL statement text
last_max_dop smallint Self-explanatory, this is the last MAX_DOP used (default = 0)
partition_number int This is the partition number. For non-partitioned objects, or in case all partitions are being rebuilt, the value of this column is NULL.
state tinyint Boolean-like operational state of the resumable index:
0=Running
1=Paused
state_desc nvarchar(60) Description of the state of the index (Running or Paused)
start_time datetime Self-explanatory, the start time of the index operation.
last_pause_time datatime The last pause time of the index operation. If the operation is running and was never paused, the value would be NULL.
total_execution_time int Total run time from start time (in minutes)
percent_complete real The progress of the index operation (in %).
page_count bigint The total number of index pages allocated by the index build operation.

Let’s resume the rebuild operations after 10 minutes and gather the stats.

I’ve canceled the query execution and status of the index rebuilding operation of clustered index is at 52% complete, and the database size has increased to 2200 MB.


The database growth is shown below. It is apparent that the there is a small chunk of database expansion. This is due to the temporary storage of the current state of the index and other metadata.

Let’s now cancel the query execution and resume the process with MAXDOP 4 with MAX_DURATION of 10 minutes.

Let’s pause/resume and abort a running resumable online index rebuild




Although online index operations permit concurrent user DML activity, the index operations will take longer if the update activity is heavy. During any online index rebuilding process, the system maintains both, the source and target structures, so the space is going to be a major constraint.

Manage blocking

The periodic locking and blocking can be handled effectively by setting WAIT_AT_LOW_PRIORITY.Let’s resume the online index rebuild by setting MAXDOP to 2, set the execution time for the index being running as resmable to 120 minutes, and in case of an index being blocked on the lock, wait 10 minutes, and after that, kill all blockers.

Resumable Index on Data-compression and SQL Paritition

Having tested the resumable option with data compression and SQL partition; the performance is no different than the normal operation.

The following SQL gives an idea of how to integrate the resumable option with SQL partitions and data-compression. The performance is acceptable and the process is really seamless. Let’s compare the time that each of these queries took.

Query Partition Compression MAXDOP Resumable Time taken
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = ALL WITH (
DATA_COMPRESSION = NONE, ONLINE=ON, MAXDOP=1, RESUMABLE=ON )
All None 1 On 9 sec
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = ALL WITH (
DATA_COMPRESSION = NONE, ONLINE=ON, MAXDOP=1, RESUMABLE=OFF )
All None 1 Off 9 sec
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = ALL WITH (
DATA_COMPRESSION = PAGE, ONLINE=ON, MAXDOP=1, RESUMABLE=ON )
All Page 1 On 17 sec
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = ALL WITH (
DATA_COMPRESSION = PAGE, ONLINE=ON, MAXDOP=1, RESUMABLE=OFF )
All Page 1 Off 18 sec
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = 3 WITH (
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2,3), ONLINE=ON, MAXDOP=1, RESUMABLE=ON)
1, 2, 3 None, Page, Page 1 On 9 sec
ALTER INDEX Index_WOCD_INCLUDE
ON [dbo].ftb_WOSummary_Daily
REBUILD PARTITION = 3 WITH (
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2,3), ONLINE=ON, MAXDOP=1, RESUMABLE=OFF)
1, 2, 3 None, Page, Page 1 Off 9 sec

Stop the query execution and measure the progress using sys.index_resumable_operations.

To resume the index rebuilding process, use


Summary

This article discusses all the features of indexing using the resumable option with SQL Server 2017 including all the combinations of partitioning, data compression, MAXDOP, priority management, blocking as well as the resumable option, and its different states.

Effective t-log management and flexibility to handle indexes at a very granular level eases the huge burden on the database administrator, for the index maintenance operation. Although the online operation is recommended, it’s important that the underlying infrastructure is evaluated, and the implications are understood before proceeding.

The impact of using the resumable option on performance is no different than using the normal command. This is tested with a compressed table and partitioned table. As a recommended practice, you should run the index operations offline, or at least during off-business hours, in order to ensure minimal impact to daily operations.

References


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
673 Views