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
|
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 |
-- Syntax for SQL Server and SQL Database ALTER INDEX { index_name | ALL } ON <object> { REBUILD { [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ] | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ] } | RESUME [WITH (<resumable_index_options>,[…n])] | PAUSE | ABORT } [ ; ] <rebuild_index_option > ::= { | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF } | RESUMABLE = { ON | OFF } | MAX_DURATION = <time> [MINUTES} } <resumable_index_option> ::= { MAXDOP = max_degree_of_parallelism | MAX_DURATION =<time> [MINUTES] | <low_priority_lock_wait> } |
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.
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ; |
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.
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue REBUILD WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) ; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT name ObjectName, index_ID, sql_text, last_max_dop_used, partition_number, state, state_desc, start_time, last_pause_time, total_execution_time, percent_complete, page_count FROM sys.index_resumable_operations WHERE STATE = 1; |
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.
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME WITH (MAXDOP=2) |
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.
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME WITH (MAXDOP=4, MAX_DURATION=10) |
Let’s pause/resume and abort a running resumable online index rebuild
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue PAUSE |
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME |
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue ABORT |
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.
|
1 2 3 |
ALTER INDEX PK_ftb_Material_Issue on ftb_Material_Issue RESUME WITH (MAXDOP=2, MAX_DURATION= 120 MINUTES, WAIT_AT_LOW_PRIORITY (MAX_DURATION=10, ABORT_AFTER_WAIT=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
|
1 2 3 |
ALTER INDEX Index_WOCD_INCLUDE on ftb_WOSummary_Daily RESUME |
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.
Table of contents
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021