The Test
Five identical tables, ~300,000 rows each, clustered on a deterministic pseudo-random key (CONVERT(UNIQUEIDENTIFIER, HASHBYTES('MD5', n)) for a fixed sequence of n, so every table starts from the same reproducible fragmentation pattern instead of relying on true randomness) so the operations being compared are working against genuinely comparable data, not five different levels of luck:
| Table | Operation | Recovery model | Starting state |
|---|---|---|---|
| Heavy_Reorg_Full | REORGANIZE | FULL | 95.1% fragmented, 51,611 pages |
| Heavy_Reorg_Simple | REORGANIZE | SIMPLE | 95.1% fragmented, 51,611 pages |
| Heavy_Rebuild_Full | REBUILD | FULL | 95.1% fragmented, 51,611 pages |
| Heavy_Rebuild_Simple | REBUILD | SIMPLE | 95.1% fragmented, 51,611 pages |
| Light_Reorg_Full | REORGANIZE | FULL | 41.2% fragmented (rebuilt clean, then ~8% of rows churned), 42,110 pages |
Log generated per operation was measured as the delta in sys.dm_db_log_space_usage.used_log_space_in_bytes between a reset baseline (CHECKPOINT, plus a fresh BACKUP LOG under FULL recovery) and immediately after the operation completed, on an otherwise idle test database with nothing else running.
The Results
| Test | Frag before → after | Density before → after | Log generated | Duration |
|---|---|---|---|---|
| Heavy, REORGANIZE, FULL | 95.1% → 0.0% | 59.8% → 92.3% | 677.58 MB | 48.3 s |
| Heavy, REORGANIZE, SIMPLE | 95.1% → 0.0% | 59.8% → 92.3% | 678.12 MB | 46.8 s |
| Heavy, REBUILD, FULL | 95.1% → 0.0% | 59.8% → 92.6% | 248.35 MB | 2.9 s |
| Heavy, REBUILD, SIMPLE (minimally logged) | 95.1% → 0.1% | 59.8% → 92.6% | 1.50 MB | 0.4 s |
| Light (41.2%), REORGANIZE, FULL | 41.2% → 0.1% | 73.3% → 92.2% | 671.96 MB | 38.2 s |
The underlying table occupies roughly 403 MB on disk (51,611 pages × 8 KB). REORGANIZE generated more transaction log than the table's own size, twice over.
Reading the Numbers
1. REORGANIZE doesn't care about your recovery model
677.58 MB under FULL, 678.12 MB under SIMPLE: statistically the same number. That's expected, and worth stating plainly because it surprises people: minimal logging is a REBUILD feature, not a REORGANIZE feature. REORGANIZE walks the leaf level and moves rows page by page, and every one of those moves is fully logged regardless of recovery model. Switching a database to SIMPLE buys you nothing on the REORGANIZE side.
2. REORGANIZE generated nearly 3× the log of a full REBUILD, on the same data
677.58 MB vs. 248.35 MB for identical starting fragmentation, same table, same recovery model. This contradicts the common assumption. REBUILD, even fully logged under FULL recovery, writes new index pages as bulk page allocations; REORGANIZE's row-by-row page-content shuffle is closer, log-wise, to a very large number of individual DELETE/INSERT operations. "Online and incremental" does not mean "cheap to log."
3. Fragmentation % is a poor predictor of REORGANIZE's log cost
The "light" table started at 41.2% fragmentation, less than half of the "heavy" table's 95.1%, yet generated 671.96 MB of log: within 1% of the heavy table's 677.58 MB. REORGANIZE's log cost tracks the volume of data it has to move to reach its target page density (roughly 92% in both runs here), not the avg_fragmentation_in_percent number everyone actually looks at. A table just past the "medium" threshold can cost almost as much log to reorganize as one that's completely shredded.
What This Means for IndexOptimize Settings
Ola Hallengren's IndexOptimize is the de facto standard for running exactly this decision (REORGANIZE vs. REBUILD vs. do nothing) on a schedule. Its defaults are sane, but two of them interact directly with what was just measured. For the broader decision framework these thresholds come from, and where fragmentation percentages stop being a useful signal on their own, see Index Optimization and Statistics Update: Pros and Cons.
| Parameter | Stock default | Recommendation | Why |
|---|---|---|---|
@FragmentationLevel1 |
5 | Keep at 5 | Below 5% fragmentation, doing nothing is correct: neither operation's cost is justified |
@FragmentationLevel2 |
30 | Keep at 30, don't raise it | Raising it to "prefer REORGANIZE longer" is the opposite of a log-saving move. The measured data shows REORGANIZE is already log-expensive well before 30%; widening its range only means using the expensive operation more often |
@FragmentationMedium |
INDEX_REORGANIZE, |
Keep default, but see the AG note below | REORGANIZE is still the right choice in this band on Standard Edition without Enterprise's online rebuild, since offline REBUILD means downtime; the log cost is the trade-off you're accepting for staying online |
@MinNumberOfPages |
1000 | Keep at 1000, or raise on very large databases | Skips indexes under ~8 MB; maintaining tiny indexes generates log for no measurable benefit |
@SortInTempdb |
N | Set to Y if tempdb is on fast, separate storage |
Moves REBUILD's sort I/O out of the user database's data/log files and into tempdb, reducing contention during the maintenance window |
@MaxDOP |
NULL (instance default) | Set explicitly for maintenance windows | Decouple index maintenance parallelism from the instance-wide OLTP setting; higher during an off-hours window, capped if maintenance runs concurrently with production load |
@LOBCompaction |
Y | Keep at Y | No measured downside; compacts LOB pages during REORGANIZE at negligible extra cost |
@UpdateStatistics / @OnlyModifiedStatistics |
NULL / N | 'ALL' / 'Y' |
REBUILD updates statistics with a full scan for free as a side effect; REORGANIZE does not touch statistics at all. Without this, reorganized indexes silently run on stale statistics until the next stats job |
@Resumable |
N | Y, where supported | Given how long a heavily fragmented REBUILD can run on a genuinely large table, the ability to pause and resume without losing progress is worth having, not just a nice-to-have |
@LockTimeout |
NULL | Set a bounded value (e.g. 300 seconds) | An unbounded REBUILD ONLINE waiting on a schema-stability lock during business hours can block far longer than the maintenance window intends |
Outside of IndexOptimize's own filtering, ad hoc statistics refreshes (after a one-off bulk load, for example) can go through Invoke-sqmUpdateStatistics, which applies the same "only what's actually changed" logic as @OnlyModifiedStatistics='Y' above.
A Baseline IndexOptimize Call
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@MinNumberOfPages = 1000,
@SortInTempdb = 'Y',
@MaxDOP = 4,
@LOBCompaction = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@Resumable = 'Y',
@LockTimeout = 300,
@TimeLimit = 3600,
@LogToTable = 'Y';Adjust @MaxDOP and @TimeLimit to the actual maintenance window and core count available; the values above are illustrative, not universal.
To see where a database currently stands before scheduling any of this, Get-sqmIndexFragmentation reports the same fragmentation numbers IndexOptimize itself would act on, without waiting for the next maintenance window to find out.
If You're Running Synchronous Availability Groups, Read This Twice
Every byte of log measured above is a byte that has to cross the wire to every synchronous secondary and be hardened there before the primary's session can proceed, exactly the mechanism covered in Diagnosing HADR_SYNC_COMMIT. Running IndexOptimize with a wide @FragmentationMedium band against a large, badly fragmented database on a synchronous AG is a self-inflicted version of exactly that incident: hundreds of megabytes of REORGANIZE log, generated deliberately, all needing synchronous acknowledgment from every secondary while user transactions are competing for the same commit path.
• Prefer REBUILD over REORGANIZE wherever Enterprise Edition's online rebuild is available; the measured log difference is not small
• Schedule index maintenance for the lowest-traffic window, and consider whether a brief switch to asynchronous commit for the maintenance window (with a deliberate switch back afterward) is acceptable for your RPO
• Use
@MaxDOP and @TimeLimit to keep any single run bounded rather than letting one big table's REORGANIZE run unsupervised
Best Practices Checklist
- ☐ Don't assume REORGANIZE is the "log-light" option; measure it, it frequently isn't
- ☐ If log volume matters, prefer REBUILD under SIMPLE/BULK_LOGGED recovery over REORGANIZE under any recovery model
- ☐ Leave
@FragmentationLevel2at or below the default 30; don't widen the REORGANIZE band to "save" log - ☐ Pair index maintenance with
@UpdateStatistics='ALL', @OnlyModifiedStatistics='Y', since REORGANIZE never updates statistics on its own - ☐ On synchronous Availability Groups, weight the REBUILD/REORGANIZE choice by its impact on
HADR_SYNC_COMMIT, not just by fragmentation percentage - ☐ Set
@LockTimeoutand@TimeLimitso maintenance can't silently run past its window - ☐ Test your own numbers on your own hardware and data shapes before trusting anyone's defaults, including these
Every sqmSQLTool function referenced above is documented at sqmSQLTool commands.
The Bottom Line
On this test, REORGANIZE cost nearly three times the transaction log of a full REBUILD for the same defragmentation result, was completely indifferent to recovery model, and cost almost as much at 41% fragmentation as at 95%. None of that is what conventional wisdom predicts. The practical takeaway for IndexOptimize is narrower than it sounds: keep the fragmentation thresholds where Ola set them, don't lean on REORGANIZE as a log-saving move, and if you're on a synchronous AG, treat every megabyte of maintenance log as a megabyte your secondaries have to acknowledge before your application's writes can proceed.