Index Maintenance

Automated index defragmentation engine. Scans fragmentation levels across all databases using sys.dm_db_index_physical_stats, determines the optimal action (REORGANIZE vs REBUILD), auto-detects Enterprise edition for ONLINE rebuilds with OFFLINE fallback, enforces a configurable time limit, and logs every operation to a history table. All objects in the indexing schema in DBA_Utilities.

Replaces Ola Hallengren's IndexOptimize with smarter edition-aware defaults and full execution history.

Key proc: indexing.sp_IndexMaintenance

How It Works

Detect edition — Enterprise gets ONLINE = ON; Standard gets OFFLINE only
For each database: scan sys.dm_db_index_physical_stats (LIMITED mode). Filter to B-tree indexes only. Skip heaps, disabled, hypothetical, CDC schema, and indexes below @MinPageCount
For each fragmented index (sorted by fragmentation DESC):
- Check time limit
- Fragmentation ≥ @RebuildThreshold → REBUILD (Enterprise: ONLINE = ON, with automatic OFFLINE fallback on failure; Standard: OFFLINE)
- Fragmentation ≥ @ReorgThreshold (but below rebuild) → REORGANIZE (with optional LOB_COMPACTION)
Log every operation to indexing.IndexMaintenanceHistory
Output summary and result set

Edition-Aware Defaults

Setting	Enterprise	Standard
Default rebuild threshold	10% (same as reorg — everything rebuilds ONLINE)	30% (wide REORGANIZE band)
ONLINE = ON	Yes	No
ONLINE fallback	Automatic OFFLINE retry on failure	N/A

Parameters

Parameter	Default	Description
`@DatabaseName`	NULL	NULL = all user databases. Supports CSV list and LIKE wildcards
`@TableName`	NULL	NULL = all tables. CSV with optional schema prefix (`dbo.MyTable`)
`@ReorgThreshold`	10.0	Reorganize indexes at or above this fragmentation %
`@RebuildThreshold`	NULL	NULL = auto (Enterprise: 10%, Standard: 30%). Rebuild at or above this %
`@MinPageCount`	1000	Skip indexes smaller than this (~8 MB). Small indexes defrag on their own
`@MaxIndexSizeMB`	NULL	NULL = no limit. Skip indexes larger than this (useful for splitting maintenance windows)
`@MaxDOP`	NULL	NULL = server default. Override MAXDOP for rebuild operations
`@TimeLimit`	18000	Seconds (5 hours). 0 = no limit. Stops starting new operations when reached
`@Execute`	0	0 = dry run (show what would happen). 1 = execute maintenance
`@LOBCompaction`	1	LOB_COMPACTION for REORGANIZE. 0 = skip (faster)
`@SortBy`	FRAGMENTATION	Process order: `FRAGMENTATION` (most fragmented first) or `SIZE` (largest first)

History Table

Every operation is logged to indexing.IndexMaintenanceHistory:

Column	Description
`RunId`	Groups all operations from a single execution
`FragmentationPct`	Fragmentation level before maintenance
`Action`	`REORGANIZE`, `REBUILD_ONLINE`, `REBUILD_OFFLINE`, or `REBUILD` (planned but not executed)
`Status`	`Success`, `Failed`, `DryRun`, or `TimeLimitReached`
`DurationSeconds`	Computed from StartTime/EndTime
`CommandExecuted`	The exact ALTER INDEX statement
`ErrorMessage`	Populated on failure

History is automatically purged after 90 days.

Time Limit Behavior

The time limit prevents starting new operations — it does not interrupt running ones. If a single REBUILD takes 30 minutes and the limit is 5 hours, the actual run may extend slightly past 5 hours while the last operation completes.

When the limit is reached:

Remaining indexes in the current database are logged as TimeLimitReached
Remaining databases are counted in the summary but not scanned
The next run picks up where this one left off (fragmentation will still be high)

Usage

Basic

-- Dry run: see what would be maintained (all databases)
EXEC indexing.sp_IndexMaintenance;

-- Execute maintenance on all databases
EXEC indexing.sp_IndexMaintenance @Execute = 1;

-- Single database
EXEC indexing.sp_IndexMaintenance @DatabaseName = 'MyDatabase', @Execute = 1;

-- Wildcard
EXEC indexing.sp_IndexMaintenance @DatabaseName = 'MyApp_%', @Execute = 1;

-- Specific table
EXEC indexing.sp_IndexMaintenance
    @DatabaseName = 'MyDatabase',
    @TableName = 'dbo.audit_trail',
    @Execute = 1;

Maintenance Windows

-- Weekend window: large indexes only, 8-hour limit
EXEC indexing.sp_IndexMaintenance
    @MinPageCount = 100000,
    @TimeLimit = 28800,
    @Execute = 1;

-- Weeknight window: small indexes only, 3-hour limit
EXEC indexing.sp_IndexMaintenance
    @MaxIndexSizeMB = 500,
    @TimeLimit = 10800,
    @Execute = 1;

Custom Thresholds

EXEC indexing.sp_IndexMaintenance
    @ReorgThreshold = 5.0,
    @RebuildThreshold = 15.0,
    @Execute = 1;

Check History

-- Most recent run
SELECT * FROM indexing.IndexMaintenanceHistory
WHERE RunId = (SELECT MAX(RunId) FROM indexing.IndexMaintenanceHistory)
ORDER BY Id;

-- History summary by run
SELECT RunId, MIN(CreatedDate) AS RunDate,
       COUNT(*) AS TotalIndexes,
       SUM(CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) AS Succeeded,
       SUM(CASE WHEN Status = 'Failed' THEN 1 ELSE 0 END) AS Failed,
       SUM(SizeMB) AS TotalMB,
       MAX(DurationSeconds) AS LongestOpSec
FROM indexing.IndexMaintenanceHistory
GROUP BY RunId
ORDER BY RunId DESC;