In our ever changing and fast paced data environments, as Data Professionals we are always looking for ways to help us out and meet the ever changing business requirements of the environments we are looking after and supporting. The biggest factor for us keeping our jobs, is ensuring the database is available and is performant. With this in mind we need to utilize the Online functions for our Index Maintenance as much as possible so we are able to ensure minimal downtime for the business applications to be accessing the data in a timely manor. For us to achieve this we probably have implemented some form of High Availability &/or Disaster Recoverability based on our requirements, along with being dictated to that we only have these maintenance windows available to undertake any form of database maintenance. Now I can hear you saying Arghhhhhhhh. Right. The old saying “Do More in less time”. Well SQL Server 2017 is here to help.
Do you find yourself in a situation where you are dictated to having a maintenance window on a regular basis that you are to undertake your Index Maintenance on and due to the increasing growth nature of your data that your Index Maintenance is unable to complete in a timely manor inside of that window????? What approaches are you using? There has been many advancements over recent times around Index Maintenance, and some of them will require you to possibly make your environment more complex. Without looking at individual environments or requirements, with the impending release of SQL Server 2017, we can look at helping you meet your business requirements with keeping your data available and performing by maintaining your indexes.
With the release of SQL Server 2017, and this is a great talking point for you to have in your internal discussions around upgrading or planning to upgrade to SQL Server 2017, we now have Resumable Online Index Rebuilds to assist us in looking like Rock Stars and keeping the performance of our SQL Server environments up and in the ever reducing timeframes applied to us by the business.
Currently for any version that is being utilized prior to SQL Server 2017, when undertaking your Index Maintenance, this is an all of nothing approach for completion. This may be broken down if you are using partitioning and you can undertake index rebuilds on a partition level but it is still an all or nothing approach. If you have an issue like (running out of disk space) the rebuild has to rollback as it did not complete. If you have an issue that cause a failover of your SQL Server environment (DB Mirroring, Failover Clustered Instances or Availability Groups) the transaction has to be rolled back, and depending on the size of the index rebuild being undertaken this can have a significant effect on the time taken to rollback and allow your environment to come online on the new primary environment.
Now with SQL Server 2017 we can have more control over our index maintenance by using Resumable Online Index Rebuilds. Now I mean we can have more control because we are able to:
- Choose how long we want a particular index rebuild to run
- Choose if we want to use parallelism on a particular index rebuild
- Choose when to Pause an Index Rebuild to allow for vital system resources to be available for other important processes
- Choose when to restart a paused or canceled index rebuild
- Restart an index build after a failure and it continues from where it last got to
- Maintain our transaction log size as the process is now broken down and not an all or nothing approach
Like everything this is not a Silver Bullet that fixes all of your problems, there are some caveats that you need to be aware of with this new functionality available in SQL Server 2017:
- We cannot rebuild an existing index that is disabled
- We cannot rebuild an existing index using the ALL option
- We cannot rebuild an existing index that has either computed column or timestamp column as part of the index key
- We cannot utilise TempDB to sort the index being rebuilt
Now that we know what this new feature is, how do we go about using this feature?
Start/Restart an Index Rebuild
ALTER INDEX [INDEX_NAME] ON [TABLE_NAME] REBUILD WITH ( ONLINE = ON, RESUMABLE = ON, MAX_DURATION = x, MAX_DURATION = 30 )
To restart a paused, canceled, killed or failed index rebuild we have two (2) options available to us to use:
- Re-issue the same statement that was executed to start the index rebuild
ALTER INDEX [INDEX_NAME] ON [TABLE_NAME] RESUME;
We are able to check the status of an index rebuild to determine if this is running, paused or completed:
SELECT TOTAL_EXECUTION_TIME, PERCENT_COMPLETE, PAGE_COUNT, * FROM SYS.INDEX_RESUMABLE_OPERATIONS
- A status of zero (0) indicates that the index is currently being rebuilt.
- A status of one (1) indicates that the index rebuild has been paused.
Pause/Cancel an Index Rebuild
ALTER INDEX [INDEX_NAME] ON [TABLE_NAME] PAUSE; ALTER INDEX [INDEX_NAME] ON [TABLE_NAME] ABORT;
- Or by identifying the SPID running the index rebuild and issuing the Kill [SPID]
With this new feature, this really opens up your options on smart index maintenance to build on the decision making process of:
- Index Rebuild Vs Index Reorg Vs No Index Work
- Index Rebuilds at the Partition level
- Index Rebuilds to run with various MaxDOP settings based on Indexes
- Index Rebuilds to run within required lower impact times
Now we do have to remember that if we do use the MaxDOP setting in the initial index rebuild statement, if the process is paused, cancelled, killed or fails, we cannot change the MaxDOP setting value on subsequent executions. The same MaxDOP setting has to be used until completion and at that point in time if required the MaxDOP setting value can be changed up or down based on your system requirements.
Like all new features, before you blindly implement these into production ensure that you understand the ramifications and test in non-production environments and gain approval for the changes to be applied into production.
References:
- https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
- https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
Leave a Reply