- Docs
- /
31 Mar 2021 19477 views 0 minutes to read Contributors
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.
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.
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 |
int |
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.
After cancelling 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.
To resume the process with MAXDOP 4 with MAX_DURATION of 10 minutes.
To pause/resume and abort a running resumable online index rebuild
The periodic locking and blocking can be handled effectively by setting WAIT_AT_LOW_PRIORITY. resume the online index rebuild by setting MAXDOP to 2, set the execution time for the index being running as resumable to 120 minutes, and in case of an index being blocked on the lock, wait 10 minutes, and after that, kill all blockers.
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.
In this article
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly