The maintenance plan fires at 1 a.m. Sunday. It calls sys.dm_db_index_physical_stats in DETAILED mode against every index in the database, reorganizes anything whose fragmentation passes 5 percent, rebuilds anything past 30 percent, and finishes around 4 a.m. Monday morning the availability group secondary sits 40 minutes behind, the log backup share grew by 180 GB overnight, and the report that took 900 ms on Friday still takes 900 ms. No one can name a query that got faster.
That job does what most published guidance still tells you to do, and the two numbers at its center were never meant to be a rule.
Where 5 and 30 came from
Paul Randal wrote DBCC SHOWCONTIG and DBCC INDEXDEFRAG for SQL Server 2000, and the Books Online thresholds came from him. His post on their origin gets misquoted in both directions, and the running order is why. He opens "I made them up. Yup." He says it again further down, under the guidance itself: "These numbers are made up." The account comes several blocks after that: customers wanted guidance, Books Online needed a number, "so I talked to some customers, inside and outside Microsoft, did a bunch of experimentation, and chose these numbers as most appropriate at the time." Then the line the misquotes cut: "So they're not really made up – they were chosen carefully." He calls them "a big generalization" and tells readers not to treat them as absolute.
Stop reading at the first line and you carry away the opposite of his position. He never says what he ran the experiments on, so the inference that it was the hardware and workloads of 2000 is mine. Microsoft's current index maintenance documentation says it in plain language: "Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They shouldn't be based on fixed fragmentation or page density thresholds alone."
The same documentation set still ships a sample script that reorganizes under 30 percent and rebuilds over it. Take the guidance, not the sample.
Two different problems share one name
avg_fragmentation_in_percent measures logical fragmentation: indexes that "have pages in which the logical ordering within the index, based on the key values of the index, doesn't match the physical ordering of index pages."
avg_page_space_used_in_percent measures page density, also called page fullness. A split leaves two pages at about 50 percent each, so the same rows occupy more pages and consume more I/O and more buffer pool. One root cause, two consequences. Logical fragmentation costs you sequential read throughput on large scans. Low page density costs you memory and I/O on every access pattern, including singleton seeks, because the leaf level is bigger than it needs to be. Microsoft's tip is blunt about which matters more: "In many workloads, increasing page density results in a greater positive performance impact than reducing fragmentation."
Most fragmentation scripts report only the first number.
Read-ahead and flash changed the math
Read-ahead "allows the Database Engine to read up to 64 contiguous pages (512 KB) from one file." A contiguous leaf level gives a range scan 512 KB per I/O; a shredded one gives it more, smaller reads. On a disk array with seek latency, those smaller reads cost real time: "When the storage subsystem provides better sequential I/O performance than random I/O performance, index fragmentation can degrade performance because more random I/O is required to read fragmented indexes." The Azure section of the same documentation set gives the flip side: "For most types of storage used in Azure SQL Database and Azure SQL Managed Instance, there's no difference in performance between sequential I/O and random I/O." Microsoft scopes that sentence to Azure storage; extending it to your own NVMe-backed SAN is my reading, not their claim. Fragmentation still matters. It stopped being the first thing to look at.
Fragmentation also does nothing to a query that never scans: "Fragmentation alone isn't a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it might reduce the effectiveness of page read-ahead during large index scans. If the query workload on a fragmented table or index doesn't involve large scans, removing fragmentation has no effect."
Measure with page count, not fragmentation alone
The scan mode passed to sys.dm_db_index_physical_stats decides both the cost and which columns come back populated.
LIMITED is the default. For an index it reads "only the parent-level pages of the B-tree (that is, the pages above the leaf level)." For a heap it examines the PFS and IAM pages and scans the data pages. It returns NULL for avg_page_space_used_in_percent, record_count, the ghost record columns, the record size columns, and forwarded_record_count, so it tells you nothing about page density, the number that matters more.
SAMPLED reads 1 percent of the pages, and falls back to DETAILED when the index or heap has fewer than 10,000 pages.
DETAILED reads every page. Microsoft documents the scan and says nothing about the cache; what I see in production is a DETAILED sweep of a 200 GB database pulling 200 GB through the buffer pool and evicting what was in it, a cache flush you inflicted on yourself before the window did any work.
The function takes an IS lock on the object, and on a readable secondary that lock can block the redo thread's request for an X lock, so do not run this survey there during business hours. The query below puts both measurements next to the size of the object, so nothing gets ranked on a percentage alone.
SELECT
sch.name AS schema_name,
o.name AS object_name,
i.name AS index_name,
i.type_desc,
ips.partition_number,
ips.page_count,
CAST(ips.page_count * 8.0 / 1024.0 AS DECIMAL(12,1)) AS size_mb,
CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6,2)) AS logical_frag_pct,
CAST(ips.avg_page_space_used_in_percent AS DECIMAL(6,2)) AS page_density_pct,
ips.fragment_count,
CAST(ips.avg_fragment_size_in_pages AS DECIMAL(10,2)) AS avg_fragment_pages,
i.fill_factor
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
JOIN sys.objects AS o ON o.object_id = ips.object_id
JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
AND i.index_id > 0
AND ips.index_level = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.page_count >= 1000
ORDER BY ips.page_count DESC;
The page_count >= 1000 filter removes the noise that makes these reports useless. An index of 40 pages occupies 320 KB. One read-ahead operation covers up to 512 KB of contiguous pages, so even scattered across extents the whole index arrives in a handful of I/Os, and after the first touch it lives in the buffer pool. At 99 percent fragmentation it tops every report sorted by percentage, and rebuilding it changes nothing. Through SQL Server 2014 the engine also put small indexes on mixed extents shared by up to eight objects, so "the fragmentation in a small index might not be reduced after reorganizing or rebuilding it."
Run DETAILED against one index off that shortlist to see the whole B-tree:
SELECT
index_level,
page_count,
CAST(avg_fragmentation_in_percent AS DECIMAL(6,2)) AS logical_frag_pct,
CAST(avg_page_space_used_in_percent AS DECIMAL(6,2)) AS page_density_pct,
record_count,
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), OBJECT_ID(N'Sales.OrderLine'), 1, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
ORDER BY index_level;
A high ghost_record_count beside a low version_ghost_record_count points at ghost cleanup, not at fragmentation.
What a rebuild costs
Transaction log. Under the full recovery model, which every availability database must use, a rebuild is fully logged, and "the transaction log can't be truncated until the index operation is completed; however, the log can be backed up during the index operation." Microsoft publishes no ratio of index size to log volume, so the rest of this section is arithmetic you run on your own measurements. Plan on log of the same order as the index, then check it on a restored copy with sys.dm_db_log_space_usage before and after, and use your number instead of mine. If the file is already the thing under pressure, start with what full-recovery logging does to the file itself and come back to the index question after. Under simple or bulk-logged recovery, CREATE INDEX and ALTER INDEX REBUILD are minimally logged, offline or online. Availability groups take that option away.
Availability groups. All that log has to reach the secondary and be redone there. Check the replicas before you queue up maintenance:
SELECT
ag.name AS ag_name,
ar.replica_server_name,
adc.database_name,
drs.synchronization_state_desc,
drs.log_send_queue_size AS log_send_queue_kb,
drs.log_send_rate AS log_send_rate_kb_per_sec,
drs.redo_queue_size AS redo_queue_kb,
drs.redo_rate AS redo_rate_kb_per_sec,
CAST(drs.redo_queue_size AS DECIMAL(18,2))
/ NULLIF(drs.redo_rate, 0) AS est_redo_seconds,
drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag ON ag.group_id = ar.group_id
JOIN sys.availability_databases_cluster AS adc
ON adc.group_id = drs.group_id
AND adc.group_database_id = drs.group_database_id
WHERE drs.is_local = 0
ORDER BY drs.redo_queue_size DESC;
Queue sizes are in KB, rates in KB per second. Divide your measured log volume by log_send_rate from a quiet period. At 60,000 KB per second and 40 GB of log that is about twelve minutes for one index, before other workload competes for the link. Whatever your two numbers come to, rebuild thirty indexes on that schedule and the send queue becomes your RPO, which is a planning problem before it is a maintenance one: when the send queue becomes your recovery point. Microsoft describes the end state for the Azure equivalent: a replica that "can lag far behind the primary, causing the system to reseed it."
Locks. An offline rebuild "acquires a schema modification (Sch-M) lock on the table." Sch-M blocks everything, including readers under READ UNCOMMITTED. An online rebuild holds an intent shared (IS) lock during the main phase, then a short S or Sch-M lock at the end to swap the structures. Short still means granted, so one long report can hold the rebuild at the finish line while its lock queue blocks everyone behind it. WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT, since SQL Server 2014, decides who loses that fight.
Edition. Online index create and rebuild is not available in Standard or Express. The SQL Server 2022 matrix marks Standard, Web, and Express No; SQL Server 2025 dropped Web edition, and its three-column matrix marks Standard and Express No. Developer and Evaluation carry the Enterprise feature set, which is why this works on a laptop and fails in production. Index maintenance is one line in a longer ledger of what Standard gives up on index maintenance. SQL Server 2016 SP1 opened columnstore, in-memory OLTP, partitioning, and compression to Standard. Online index operations were not on that list, and advice written as if SP1 covered them circulates anyway. On Standard, every ALTER INDEX REBUILD takes a Sch-M lock and the table is offline for the duration.
REBUILD versus REORGANIZE
| REORGANIZE | REBUILD | |
|---|---|---|
| Availability | Always online, short-duration locks only | Online requires Enterprise; otherwise Sch-M for the duration |
| Scope | Leaf level only | All levels of the index |
| Page density | Compacts leaf pages to the index fill factor | Rebuilds all pages to the specified or current fill factor |
| Interruptible | Yes, progress to that point persists | Only with RESUMABLE = ON |
| Statistics | Not updated | Full scan of the index, except when partitioned or resumable |
| Log | Many small transactions, so the log can be truncated while it works | Held open until the operation commits |
Microsoft documents neither side of that log row in those terms; both are the practitioner reading of how the two operations commit, and the ordering is not unconditional. Reorganize on a large index with heavy fragmentation can move enough pages to out-log a rebuild.
Microsoft now names reorganize the default: "Reorganizing an index is less resource intensive than rebuilding an index. For that reason it should be your preferred index maintenance method, unless there's a specific reason to use index rebuild." It also fixes page density, which most summaries leave out: it "physically reorders the leaf-level pages to match the logical order of the leaf nodes, left to right" and "compacts index pages to make page density equal to the fill factor of the index." Cancel it and the work it finished stays done.
The statistics row is the trap. A rebuild scans every row to update the index's statistics; a reorganize updates none. Microsoft's own documentation argues that most of the improvement teams credit to defragmentation comes from those fresh full-scan statistics and the recompiles they trigger, at a fraction of the cost, and the case for that, the sampling rates behind it, and how to tell a stale-statistics regression from a fragmentation problem belong in one place: run the statistics update first and measure. What it means inside this decision is narrow. Swap a rebuild job for a reorganize job and change nothing else, and plans start regressing, because you dropped a full-scan statistics update from the schedule without noticing. Add UPDATE STATISTICS back.
Resumable rebuild arrived in SQL Server 2017, resumable create in SQL Server 2019. Both need ONLINE = ON, so both need Enterprise. SORT_IN_TEMPDB = ON is not supported with them, and the statistics update on a resumable operation uses the default sampling ratio instead of a full scan. The log behavior is the reason to care on an AG: "The overall log space usage for resumable index is less compared to regular online index rebuild and allows log truncation during this operation." MAX_DURATION appears twice below and means two different things: inside ONLINE = ON (...) it caps the wait on low priority locks, at the top level it caps how long the rebuild runs before pausing itself.
ALTER INDEX IX_OrderLine_ProductId ON Sales.OrderLine
REBUILD WITH (
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 2 MINUTES, ABORT_AFTER_WAIT = SELF)),
RESUMABLE = ON,
MAX_DURATION = 20 MINUTES,
MAXDOP = 4
);
-- window closed, hand the server back
ALTER INDEX IX_OrderLine_ProductId ON Sales.OrderLine PAUSE;
SELECT name, state_desc, percent_complete, total_execution_time, last_max_dop, page_count
FROM sys.index_resumable_operations;
A paused resumable rebuild keeps its overhead on every write to the indexed columns until you resume or abort it: "If you don't intend to complete a resumable index operation, abort it instead of pausing it."
Fill factor buys space you may never use
Fill factor sets the percentage of each leaf page filled at create or rebuild time. The default is 0, which the engine treats as 100. It applies once: "The Database Engine doesn't dynamically keep the specified percentage of empty space in the pages." Take a clustered index on a bigint IDENTITY key, 200 million rows, 400 bytes per row. An 8 KB page offers 8,096 bytes to rows and the slot array, so 20 fit and the leaf level needs about 10 million pages, 76 GB. Set fill factor to 80 and 16 fit, so the index needs 12.5 million pages, 95 GB. Those are binary GB, the same units the size_mb expression above produces; in decimal GB the same page counts read 82 and 102. You paid 19 GB of disk, 19 GB of every backup, and 25 percent more buffer pool for reserved space no insert will ever touch, because an ever-increasing key appends to the right edge and leaves the interior pages alone. Microsoft says the same: "if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index," so "the empty space in the index pages might not be filled."
The current recommendation is narrow: "Microsoft doesn't recommend setting fill factor to values other than 100 or 0, except in certain cases for indexes experiencing a high number of page splits. For example, this can occur in frequently modified indexes with the leading column that contains nonsequential GUID values." Lower it on the index that splits, inside the documented 70 to 95 range, and nowhere else. The IDENTITY case has a second half worth knowing, because sequential keys move the contention to the right edge instead of scattering it.
Page splits are the thing worth watching
Fragmentation is the residue. The page split is the event, and it charges you when it happens: a page allocated, rows moved, index and log records written, latches held. sys.dm_db_index_operational_stats counts them, since for an index "a page allocation corresponds to a page split."
SELECT
sch.name AS schema_name,
o.name AS object_name,
i.name AS index_name,
i.fill_factor,
ios.leaf_allocation_count,
ios.nonleaf_allocation_count,
ios.leaf_page_merge_count,
ios.leaf_insert_count,
ios.leaf_update_count,
ios.leaf_delete_count,
ios.range_scan_count,
ios.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
JOIN sys.indexes AS i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
JOIN sys.objects AS o ON o.object_id = ios.object_id
JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
AND i.index_id > 0
AND ios.leaf_allocation_count > 0
ORDER BY ios.leaf_allocation_count DESC;
Two warnings on this DMV. The counters zero out when the index metadata is brought into the metadata cache, so an index whose metadata was evicted and reloaded reports counts since the reload, not since the restart. And leaf_allocation_count counts every leaf allocation, including the harmless appends at the right edge of an ascending key. It cannot separate the split that added a page from the split that tore one in half.
Extended Events can. Jonathan Kehayias published the method at SQLskills: the sqlserver.page_split event, even after its SQL Server 2012 improvements, does not let you isolate the splits that cause fragmentation, so he filters on the log record only a mid-page split emits, LOP_DELETE_SPLIT. The map value 11 comes from his published session, not from Microsoft, and Extended Events map values are version-scoped. Confirm it on your build before you trust the output:
SELECT name AS map_name, map_key, map_value
FROM sys.dm_xe_map_values
WHERE map_value = 'LOP_DELETE_SPLIT';
If that returns a map_key other than 11, use the number it gives you:
CREATE EVENT SESSION [TrackMidPageSplits] ON SERVER
ADD EVENT sqlserver.transaction_log (
WHERE operation = 11 -- LOP_DELETE_SPLIT
AND database_id = 7 -- your database
)
ADD TARGET package0.histogram (
SET filtering_event_name = 'sqlserver.transaction_log',
source_type = 0, -- event column
source = 'alloc_unit_id'
);
GO
ALTER EVENT SESSION [TrackMidPageSplits] ON SERVER STATE = START;
The histogram shape comes from Kehayias; bucketing on alloc_unit_id instead of database_id is my variation, so test it before you schedule it. The target holds 256 slots by default and drops buckets past that on a busy server without telling you. Run it through a representative workload, then resolve the buckets to index names from inside that database:
WITH buckets AS (
SELECT
slot.value('(value)[1]', 'bigint') AS alloc_unit_id,
slot.value('(@count)[1]', 'bigint') AS split_count
FROM (
SELECT CAST(st.target_data AS XML) AS target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS st
ON st.event_session_address = s.address
WHERE s.name = 'TrackMidPageSplits'
AND st.target_name = 'histogram'
) AS x
CROSS APPLY x.target_data.nodes('HistogramTarget/Slot') AS t(slot)
)
SELECT
sch.name AS schema_name,
o.name AS object_name,
i.name AS index_name,
i.fill_factor,
b.split_count
FROM buckets AS b
JOIN sys.allocation_units AS au ON au.allocation_unit_id = b.alloc_unit_id
AND au.type IN (1, 3)
JOIN sys.partitions AS p ON p.hobt_id = au.container_id
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.objects AS o ON o.object_id = p.object_id
JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
ORDER BY b.split_count DESC;
The indexes at the top of that list earn a fill factor change, a key change, or a redesign. A leading uniqueidentifier from NEWID() is the usual culprit, and choosing the key is the real fix: random keys and the splits they cause. There are seldom more than a handful of these indexes, and they seldom match the top of a fragmentation report.
The order to work in before the next maintenance window
- Retire the database-wide DETAILED survey. Run the SAMPLED query above, filtered to
page_count >= 1000, and store the output with a timestamp so you read a trend and not one sample. - Rank by page density, not by
avg_fragmentation_in_percent. A 60 GB index at 55 percent density wastes 27 GB of memory and I/O on every read. A 400 MB index at 90 percent fragmentation wastes no space, though it still costs read-ahead on a scan. - Capture a baseline in Query Store first, using its A/B testing workflow, and baseline the window before you book it. If you cannot show a difference afterward, you cannot justify the window.
- Run
UPDATE STATISTICS ... WITH FULLSCANon the candidate before anything else, then measure again. It costs minutes, not hours. - If page density is the problem and statistics did not fix it, run
ALTER INDEX ... REORGANIZEon that index: online on every edition, interruptible, and it compacts to the fill factor. Follow it with a statistics update, because reorganize does not do one. - Reserve
REBUILDfor what reorganize cannot reach: a clustered index that needs a new fill factor, a nonclustered index whose upper levels are shredded, a rebuild ahead of a shrink. On Enterprise useONLINE = ON,RESUMABLE = ON, andWAIT_AT_LOW_PRIORITY. On Standard, schedule it as the outage it is. - On an availability group, measure your own log volume and send rate, then cap how many indexes the job touches per night. The send queue is a recovery point objective, not a performance counter.
- Run the mid-page split session for a week and fix the two or three indexes it names. That work lasts, which is more than the weekly rebuild job can claim.
Few databases never need a maintenance window. Plenty spend three hours of full-recovery logging every weekend to fix a number nobody has tied to a query.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.