A nightly rebuild can consume more resources than the queries it is meant to help, so index maintenance on modern storage needs evidence. SSDs and storage arrays change the cost of scattered reads, while rebuilds still consume CPU, I/O, log space, and time. Start with symptoms and useful evidence.
Separate Fragmentation From Slow Queries
Logical fragmentation describes how leaf pages are ordered relative to key order. It can matter for large ordered scans, particularly when physical reads dominate. It is less informative for selective seeks or workloads whose pages are already in memory. A single percentage cannot explain every query plan or storage system.
I begin with the slow query, its actual plan, reads, waits, and result size. If the query is waiting on locks or spilling to tempdb, rebuilding an unrelated index is a detour. On modern storage, scattered I/O can be less punishing than an old rule assumes, but it is still workload dependent. Which slow query improved because of layout, and which improved because statistics changed?
Consider Page Density
Low page density means more pages hold the same rows. More pages can increase memory pressure, I/O, and upper-level index size. Page splits from random inserts or unsuitable fill factor can contribute. Density can matter even when logical fragmentation looks acceptable. Conversely, an intentionally lower fill factor can reduce split pressure for a specific write pattern.
Inspect page_count and average_page_space_used_in_percent alongside fragmentation. Ignore tiny indexes where percentages jump dramatically with only a few pages. The right action depends on why pages are sparse and whether the workload is harmed. A rebuild that packs pages tightly can recreate split pressure tomorrow.
Sample Physical Statistics
sys.dm_db_index_physical_stats provides page counts, fragmentation, and density estimates. LIMITED mode is lighter and suitable for broad triage, though detailed columns can require SAMPLED or DETAILED mode. Scope the query to one database and a target table or index before broadening it. Running detailed scans across every large object during business hours is its own performance problem.
This example inspects one table. Replace the object name and review the page count before interpreting percentages. A 90 percent figure on eight pages is not an emergency.
SELECT OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name, ips.index_level,
ips.page_count,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.Orders'), NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.index_level = 0
ORDER BY ips.page_count DESC;Do Not Confuse Statistics With Rebuilds
A rebuild updates index statistics as a side effect, so a faster query afterward does not prove fragmentation was the cause. Better cardinality estimates can explain the improvement. Reorganizing an index does not have the same statistics effect. Test statistics updates separately when estimates are suspect and compare plans before paying for a full rebuild.
I have seen maintenance schedules credited for fixing performance when the decisive change was refreshed statistics. The clean experiment is to identify a query with poor estimates, update the relevant statistics, and test again. If the query improves, the storage layout was an innocent bystander.
Choose the Lightest Index Maintenance on Modern Storage
Possible actions include no change, updating statistics, changing fill factor, reorganizing, or rebuilding a specific index. Rebuilds can be online for supported editions and operations, but online does not mean zero resource use or zero locking. Reorganizations can be incremental, yet they still write pages and do not solve every density problem.
Pick an action for a specific observed issue. If a large range scan suffers and physical reads are high, targeted layout work can help. If the problem is stale estimates, update statistics. If inserts cause frequent splits, review key pattern and fill factor. A maintenance job should explain its choice more clearly than a magic threshold.
Inspect Statistics Freshness
The statistics metadata can show when a statistic was last updated and how many rows changed since then. These are clues, not a universal trigger. Skew, ascending keys, and filtered subsets can make a small change important. A broad uniform table can tolerate more change. Compare estimated and actual row counts for the queries that matter.
This query lists statistics for one table and their update state. It does not automatically update anything. Use the result to choose a focused experiment, then confirm with query plans and runtime metrics.
SELECT s.name, s.auto_created, s.user_created,
p.last_updated, p.rows, p.rows_sampled,
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY p.modification_counter DESC;Account for Index Maintenance Cost on Modern Storage
Rebuilds produce log records, consume I/O, and can need extra disk space. They can also affect availability group redo and backups. A whole-database nightly rebuild can compete with application work and leave a long log chain to process. Measure maintenance duration, log growth, and waits just as you measure query benefit.
If the window is short, prioritize the few indexes tied to a visible problem. Use resumable or online features only where supported and operationally understood. I keep rollback and monitoring plans because a maintenance command is still a workload. The server does not know it was scheduled with good intentions.
Build a Lighter Index Maintenance Routine for Modern Storage
Begin with regular statistics review, targeted index health sampling, and a record of queries affected. Exclude small objects from fragmentation-driven actions. Set thresholds based on your storage and workload, then revisit them with evidence. Avoid assuming every index needs a weekly rebuild. Some stable indexes need no physical maintenance for long periods.
Document why each action runs and what metric should improve. If the job cannot name the benefit, narrow the job. A simple routine with measured exceptions is easier to trust than a script that touches every table to demonstrate diligence. Maintenance should preserve performance, not become the largest query of the night.
Validate the Change
Capture the actual query plan, CPU, duration, reads, and relevant waits before maintenance. Repeat the same representative workload afterward. Compare data distribution and cache state as fairly as possible. A warm cache can make any second run look better. Track whether the improvement lasts after normal writes resume.
Modern storage changes the index maintenance tradeoff, but it does not erase physical design. The useful rule is to follow an observed query or space problem to the smallest effective maintenance action. A lower fragmentation percentage is a measurement. A faster, steadier workload is the outcome that matters.
Related reading on this blog: Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses and Sample Script to Check Index Fragmentation with RowCount.
Index maintenance on modern storage is not a percentage ritual, it is targeted work tied to query behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
