Index/Statistics Maintenance Based on Evidence Instead of Blind Rebuild Schedules

Learning outcomes

Many SQL Server estates still run a script that rebuilds every index above a fixed fragmentation percentage every night. That script often creates more work than it removes: large log generation, extra tempdb usage for sorts/versioning, blocking or lock pressure, AG send/redo backlog, replication volume, plan recompilation and unnecessary SSD I/O. Index maintenance should start with a degraded workload hypothesis, not a threshold copied from an old blog post.

1. Start with the query and the access pattern

Logical fragmentation describes leaf-page order; page density describes how full the pages are. Fragmentation mostly matters for workloads that perform large ordered scans and can benefit from efficient read-ahead. If a query performs point seeks, eliminating fragmentation might have no measurable benefit. Low page density can be more broadly expensive because more pages must be cached and read for the same rows, and the optimizer’s I/O cost model can change.

USE master;GOIF DB_ID(N'ServiceHubOpsLab') IS NULLBEGIN    CREATE DATABASE ServiceHubOpsLab;END;GOALTER DATABASE ServiceHubOpsLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubOpsLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubOpsLab;GOIF SCHEMA_ID(N'lab21') IS NULL EXEC(N'CREATE SCHEMA lab21 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab21.RunAudit', N'U') IS NULLBEGIN  CREATE TABLE lab21.RunAudit  (    run_id bigint IDENTITY PRIMARY KEY,    run_name sysname NOT NULL,    started_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    finished_at datetime2(0) NULL,    outcome varchar(16) NOT NULL DEFAULT 'STARTED',    detail nvarchar(1000) NULL  );END;GOIF OBJECT_ID(N'lab21.WorkQueue', N'U') IS NULLBEGIN  CREATE TABLE lab21.WorkQueue  (    work_id bigint IDENTITY PRIMARY KEY,    status varchar(16) NOT NULL,    created_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),    processed_at datetime2(0) NULL,    payload nvarchar(200) NULL  );  INSERT lab21.WorkQueue(status,payload)  SELECT TOP (5000)    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5 = 0 THEN 'READY' ELSE 'DONE' END,    CONCAT(N'work-',ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;END;GOUSE ServiceHubOpsLab;GOIF OBJECT_ID(N'lab21.OrderHistory',N'U') IS NULLBEGIN  CREATE TABLE lab21.OrderHistory  (    order_id int IDENTITY PRIMARY KEY,    customer_id int NOT NULL,    status varchar(16) NOT NULL,    order_date date NOT NULL,    amount decimal(12,2) NOT NULL,    padding char(100) NULL  );  INSERT lab21.OrderHistory(customer_id,status,order_date,amount,padding)  SELECT TOP (30000)    1 + ABS(CHECKSUM(NEWID())) % 4000,    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 7=0 THEN 'OPEN' ELSE 'CLOSED' END,    DATEADD(day,-(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 730),CONVERT(date,SYSUTCDATETIME())),    10 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 5000),    REPLICATE('x',100)  FROM sys.all_objects a CROSS JOIN sys.all_objects b;  CREATE INDEX IX_OrderHistory_StatusDate    ON lab21.OrderHistory(status,order_date)    INCLUDE(customer_id,amount);END;GO

The random inserts here are for a disposable diagnostic dataset, not a benchmark. Before maintenance, record the query shape and whether the index participates in a scan or seek. Query Store from Chapters 10–11 can provide before/after runtime evidence; index usage DMVs are transient since restart/failover and therefore need collection context.

SELECT  i.name, ps.page_count, ps.avg_fragmentation_in_percent,  ps.avg_page_space_used_in_percent,  us.user_seeks, us.user_scans, us.user_updatesFROM sys.indexes AS iCROSS APPLY sys.dm_db_index_physical_stats  (DB_ID(),OBJECT_ID(N'lab21.OrderHistory'),i.index_id,NULL,'SAMPLED') AS psLEFT JOIN sys.dm_db_index_usage_stats AS us  ON us.database_id=DB_ID() AND us.object_id=i.object_id AND us.index_id=i.index_idWHERE i.object_id=OBJECT_ID(N'lab21.OrderHistory')ORDER BY i.index_id;GOSELECT s.name,sp.last_updated,sp.rows,sp.rows_sampled,sp.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS spWHERE s.object_id=OBJECT_ID(N'lab21.OrderHistory');GO

A tiny 40-page index can report dramatic percentages but be cheaper to leave alone than to maintain. A large, scan-heavy index with poor density and correlated query regression deserves more attention. Never treat one DMV row as a maintenance command generator.

2. REORGANIZE, REBUILD, and statistics updates are different interventions

ALTER INDEX ... REORGANIZE is an online, incremental leaf-level operation that compacts pages toward the stored fill factor and reduces logical fragmentation. It does not update index statistics. ALTER INDEX ... REBUILD creates a new index structure and updates index statistics as part of the build; it can be much more resource-intensive. Online rebuild availability is edition/version sensitive. UPDATE STATISTICS can refresh the cardinality model without rewriting the entire index.

Microsoft’s current guidance explicitly warns that perceived “rebuild benefits” are often statistics-refresh benefits. Therefore if a query regresses because estimates are stale, updating the relevant statistics may achieve the goal with far less log, I/O and blocking than rebuilding the index.

-- Use one intervention at a time and measure representative queries.UPDATE STATISTICS lab21.OrderHistory IX_OrderHistory_StatusDate WITH RESAMPLE;GO-- REORGANIZE is online and does not update statistics.ALTER INDEX IX_OrderHistory_StatusDate ON lab21.OrderHistory REORGANIZE;GO-- REBUILD is more invasive; ONLINE=ON is edition-sensitive.-- ALTER INDEX IX_OrderHistory_StatusDate ON lab21.OrderHistory--   REBUILD WITH (ONLINE=ON);GO

3. Deliberately wrong: rebuild everything nightly

A nightly ALTER INDEX ALL ... REBUILD across every database can create a burst of transaction-log generation, extend log-backup duration, fill a log that was otherwise right-sized, consume tempdb for online/sort operations, block schema locks, and push large redo/send queues to availability-group replicas. It also creates needless writes on storage and can invalidate plans. If replication or CDC observes the same database, maintenance can interact with their log/retention pressure even though index rebuild itself is not “replicated as row changes” in the simple sense.

The repair is a decision procedure: identify queries/indexes where maintenance might matter; compare Query Store/runtime evidence; choose no-op, stats update, reorganize or rebuild; limit scope to the object/partition; record resource preconditions; verify after. “Do nothing” is a valid maintenance action.

DECLARE @idx int = INDEXPROPERTY(OBJECT_ID(N'lab21.OrderHistory'),N'IX_OrderHistory_StatusDate','IndexID');DECLARE @frag decimal(9,2), @density decimal(9,2), @pages bigint;SELECT TOP (1)  @frag=avg_fragmentation_in_percent,  @density=avg_page_space_used_in_percent,  @pages=page_countFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'lab21.OrderHistory'),@idx,NULL,'SAMPLED');INSERT lab21.RunAudit(run_name,finished_at,outcome,detail)VALUES(N'IndexAssessment',SYSUTCDATETIME(),'SUCCEEDED',       CONCAT(N'pages=',@pages,N'; frag=',@frag,N'; density=',@density,              N'; action=NO AUTOMATIC ACTION - correlate with workload evidence'));SELECT TOP (3) * FROM lab21.RunAudit ORDER BY run_id DESC;GO

This is intentionally conservative. It demonstrates that collection and decision are separate. A production framework can encode approved policy, but the policy should still have object exclusions, resource guards, partition awareness, time budgets, cancellation/rollback behavior, and a path to override based on measured regressions.

4. Statistics maintenance needs its own policy

Automatic statistics creation/update handles many workloads well, but not all. Large tables, skew, ascending keys, selective predicates and bursty modifications can need targeted updates. Use sys.dm_db_stats_properties and Query Store estimate/runtime evidence instead of “statistics older than seven days” alone: a statistic can be old and perfect because the data did not change, or recent and unrepresentative because the sample missed important skew.

Rebuilding every index merely to update statistics is especially wasteful. If statistics are the problem, fix statistics. If page density or fragmentation demonstrably hurts scan performance, fix the relevant index. Keep cause and intervention aligned.

5. Production judgment

Maintenance must fit the surrounding system. Before rebuilds or large stats jobs, inspect free log space, log-backup health, tempdb headroom, current blocking, AG send/redo queues, storage latency, replication/CDC health, and the business window. Record engine build, edition, database compatibility, recovery model, index/partition, command options and observed result. Resumable/online features can reduce operational risk where supported, but they do not remove resource cost.

The next lesson moves from object maintenance to estate governance: instead of changing objects on a fixed schedule, how do you define desired configuration, detect drift, and decide what a policy engine may safely enforce?

Authoritative references

• Optimize index maintenance to improve query performance and reduce resource consumption
• sys.dm_db_index_physical_stats
• sys.dm_db_stats_properties
• UPDATE STATISTICS
• ALTER INDEX