Index Maintenance on Your SQL Server VLDBs

Introduction

Most database administrators are comfortable with the daily tasks of backup, CHECKDB, index maintenance, and statistics updates. The available guidance for those topics is extensive. Much less has been written about caring for estates that contain hundreds of databases—or a single database large enough to strain conventional maintenance windows. This post will focus on options that help when individual tables or complete databases grow beyond the point where “standard” maintenance jobs finish in a reasonable time.

Partitioning and targeted index work

When a table is partitioned across dedicated filegroups, recent partitions almost always receive the bulk of write activity. Fragmentation therefore accumulates unevenly: last month’s data may be heavily fragmented while data from last year is essentially static. SQL Server lets you rebuild only the partitions that need attention:

ALTER INDEX [IX_TransactionHistory_ProductID]
ON [Production].[TransactionHistory]
REBUILD PARTITION = 1
WITH (ONLINE = ON);
GO

Since SQL Server 2014, partitioned indexes can be rebuilt online. The same release added low-priority locking options so that an online rebuild can yield if it begins to block interactive workloads:

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    ONLINE = ON
      (WAIT_AT_LOW_PRIORITY
         (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS))
);
GO

Resumable online index rebuilds

Enterprise Edition 2017 extended online rebuilds with a resumable option. A resumable rebuild writes its progress to the database so that it can pause—manually or after a failover—and continue later without starting over or consuming large amounts of transaction log space.

-- start a resumable rebuild
ALTER INDEX test_idx ON test_table
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 1);

-- pause the operation
ALTER INDEX test_idx ON test_table PAUSE;

-- resume with different settings
ALTER INDEX test_idx ON test_table
RESUME WITH (MAXDOP = 4, MAX_DURATION = 240 MINUTES);

-- abort if necessary
ALTER INDEX test_idx ON test_table ABORT;

A resumable rebuild is especially helpful when maintenance windows are short or unpredictable. You can pause during business hours, allow log backups to clear space, and resume after hours without wasting work already completed.

When index rebuilds are no longer practical

Some tables eventually become so large that even partition-level or resumable rebuilds run for many hours. At that scale, statistics maintenance often delivers a better return on investment than physical defragmentation. Before you disable index maintenance, review the storage tier. If the database already lives on low-latency solid-state storage over Fibre Channel or NVMe, further reducing logical fragmentation may have little measurable effect. If the system still relies on slower disks and all other tuning options have been explored, a storage upgrade may be the only realistic path to faster maintenance.

Statistics still matter. Updating them keeps the query optimizer informed about data distribution and usually prevents sudden plan regressions. Even on very large databases, a regularly scheduled UPDATE STATISTICS (potentially with a sampled scan) is lightweight compared with an index rebuild and remains a sound practice.

Conclusion

Partitioning, online low-priority rebuilds, and the resumable rebuild feature form a toolkit that lets you focus effort where it produces the greatest benefit and defer work safely when time runs out. Combined with up-to-date statistics and a storage platform appropriate to the workload, these options help maintain performance and predictability even as data volumes pass the terabyte mark.