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 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….
Overview-resumable-indexes-sql-server-2017
Happy Learning!!
