Index Maintenance
Index maintenance keeps your SQL Server indexes healthy by detecting and resolving fragmentation before it impacts query performance. DB24 automates this process — evaluating every eligible index on every eligible database, choosing the right action based on fragmentation level and index type, and respecting your configured thresholds and maintenance windows.
Eligibility
Not every database or index is processed on every run. DB24 applies a two-level eligibility check before any maintenance is performed.
Database eligibility
A database must meet all of the following to be included:
| Condition | Rule |
|---|---|
| AG role | Must be the primary replica — AG secondaries are never touched |
| Read-only | Database must not be read-only |
| Access | The service account must have access |
| Exclusions | Not listed in the exclusions table (CategoryId = 100 or 101) |
| Has eligible indexes | At least one index must be present in the maintenance index list |
Index eligibility
Each index within an eligible database is then evaluated individually:
| Condition | Rule |
|---|---|
| Index type | Rowstore (type 1–4) or Columnstore (type 5–7) only |
| State | Not disabled, not hypothetical |
| Ownership | Not a system-owned (MS-shipped) index |
| Allocation unit | IN_ROW_DATA only for Rowstore |
| Fragmentation | At or above the configured minimum threshold |
| Minimum size | Must meet both the minimum page count and minimum row count |
| Exclusions | Not listed in the exclusions table for this specific database |
Thresholds and Configuration
All thresholds are configurable per scope — instance-wide, primary, or secondary — via cfg.Maintenance.
Rowstore indexes
| Parameter | Default | Description |
|---|---|---|
MediumFragmentation_RS |
5% | Minimum fragmentation to qualify for any maintenance |
HighFragmentation_RS |
30% | Fragmentation level that triggers Rebuild instead of Reorganize |
MaxPDensityForReorg_RS |
80% | Page density ceiling for Reorganize — indexes above this are Rebuilt to avoid page splits |
MinIXPages_RS |
2,000 pages | Minimum page count for inclusion |
MinIXRows_RS |
2,000 rows | Minimum row count for inclusion |
Columnstore indexes
| Parameter | Default | Description |
|---|---|---|
MediumFragmentation_CS |
20% | Minimum fragmentation to qualify |
MinIXPages_CS |
0 pages | No page floor |
MinIXRows_CS |
5,000 rows | Minimum row count for inclusion |
General settings
| Parameter | Default | Description |
|---|---|---|
RunMaintenanceIndex |
1 (instance/primary), 0 (secondary) |
Master on/off switch per scope |
MaxDOP |
2 (instance), 0 (primary/secondary) |
Parallelism for Rebuild operations |
SortInTempDB |
1 (instance), 0 (others) |
Whether Rebuild sort operations use TempDB |
OnlineOption |
0 |
Rebuilds run offline by default |
AllowRowLocks |
1 |
Row-level locking enabled |
AllowPageLocks |
1 |
Page-level locking enabled |
Maintenance Actions
Rowstore
The action taken depends on both fragmentation level and page density:
| Fragmentation | Page Density | Action |
|---|---|---|
| ≥ Medium and < High | ≤ MaxPDensityForReorg (80%) | Reorganize with LOB_COMPACTION = ON |
| ≥ Medium and < High | > MaxPDensityForReorg (80%) | No action — see note below |
| ≥ High (30%) | Any | Rebuild with configured MAXDOP, ONLINE, SORT_IN_TEMPDB, and lock options |
The dead zone: An index that is moderately fragmented but highly dense (page density above 80%) receives no maintenance. Reorganizing such an index risks excessive page splits and log growth. The index will naturally accumulate more fragmentation over time until it crosses the High threshold and qualifies for a Rebuild.
Columnstore
Columnstore indexes are always Reorganized using LOB_COMPACTION = ON, COMPRESS_ALL_ROW_GROUPS = ON, regardless of fragmentation level. Rebuild is never used.
This is intentional: COMPRESS_ALL_ROW_GROUPS flushes all open and closed delta rowgroups into the columnstore, making Rebuild effectively redundant. This behavior requires SQL Server 2016 or later.
Partitioned Indexes
Partitioned indexes are evaluated and maintained at the partition level. Each partition is assessed independently, and PARTITION = N is appended to the ALTER INDEX command where applicable.
Runtime Behavior
A few important behaviors govern how maintenance runs in practice:
Maintenance window is checked per index.
The window is evaluated at the start of every index iteration. If the window closes mid-run, the remaining indexes in that database — and all subsequent databases — are skipped immediately.
Disk space is checked before every index.
Free space is verified across all volumes hosting the database's files before each index is processed. If any volume breaches the configured threshold, the current database is aborted and processing continues with the next. Each volume breach is logged once per run.