Overview of Resumable Indexes in SQL Server 2017

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…


Being a database administrator, understanding the implication of fragmentation takes precedence over fixing it. Often we don’t think about fragmentation details, and the proceeds 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 require 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.

Further reading….


Happy Learning!!


About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL Indexes, SQL Server 2017 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s