How Much Transaction Log Does REORGANIZE Really Generate? I Measured It
A while back I published the guard clauses my index rebuild script uses, including the classic advice: reorganize in the middle band of fragmentation, rebuild above it, because “reorganize only logs the pages it actually moves.” In a LinkedIn discussion, Jeff Moden challenged that line, arguing that REORGANIZE generates far more transaction log than people expect, enough that it deserves to be the exception rather than the default.
I will admit my first instinct was to defend the received wisdom. My second instinct was better: build a test harness and measure it. This post is what came out of that, and Jeff deserves credit for prompting it, because the measurements surprised me in ways neither of us predicted.
The setup
Everything here runs in FULL recovery, on purpose. If your database is in an availability group, feeds log shipping, or has a recovery point objective shorter than your full/differential backup interval, FULL recovery is your reality and log volume is a first-class operational concern. (If you can run index maintenance in SIMPLE or BULK_LOGGED, most of this post matters much less to you; see my post on recovery models for where that line falls.)
The test table is deliberately hostile: a clustered primary key on a random GUID.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[guid_target] ( [id] uniqueidentifier NOT NULL CONSTRAINT [df_guid_target_id] DEFAULT NEWID() , [filler] char(400) NOT NULL CONSTRAINT [df_guid_target_filler] DEFAULT REPLICATE('x', 400) , CONSTRAINT [pk_guid_target] PRIMARY KEY CLUSTERED ([id]) ); |
I seeded it with 2.5 million rows (about 420 bytes per row, 19 rows per page), rebuilt at fill factor 100 for a clean 1.0 GB baseline of 131,579 leaf pages, and took a full backup. Then I inserted random GUIDs in small batches until the index reached 10%, 30%, and 80% fragmentation, backing up each state so every test run starts from a byte-identical layout.
Each run restores a state, takes a log backup to reset the baseline, runs exactly one operation (REORGANIZE, or REBUILD WITH (ONLINE = ON)), and measures the log generated:
|
1 2 |
SELECT [ls].[log_since_last_log_backup_mb] FROM [sys].[dm_db_log_stats](DB_ID()) AS [ls]; |
A separate monitor process samples active log usage every five seconds, and each combination runs twice: once with no log backups during the operation (total volume, worst-case peak) and once with a log backup every 15 seconds (the availability group / log shipping scenario, where truncation can happen mid-operation).
Round one: total log volume
| Fragmentation | Density | REORGANIZE log | REBUILD log | Ratio |
|---|---|---|---|---|
| 11% | 94.3% | 2,152 MB | 1,748 MB | 1.23x |
| 30% | 85.1% | 2,656 MB | 1,761 MB | 1.51x |
| 80% | 63.4% | 3,410 MB | 1,851 MB | 1.84x |
Jeff was right on the direction, and the details are worth staring at. REBUILD’s log cost is essentially flat: about 1.7 times the index size, no matter how fragmented the index is. That makes sense, because a rebuild writes a complete new copy of the index and logs it fully in FULL recovery.
REORGANIZE out-logged REBUILD at every level. At 11% fragmentation, barely over the floor where most scripts start acting at all, reorganize generated over twice the index’s size in log. The “only logs the pages it actually moves” framing is not wrong as far as it goes; the problem is the number of pages it actually moves.
Round two: how many pages does reorganize actually move?
Here is where it got strange. As a control, I also ran both operations against a freshly rebuilt index with a monotonically increasing bigint clustered key: 0.03% fragmentation, 99.74% page density. Nothing to fix.
REORGANIZE logged 2,576 MB. On a pristine index. More than it logged against the 11%-fragmented GUID index.
To find out why, I built a small 100,000-row copy so fn_dblog stays manageable, reorganized it, and aggregated the log records:
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP (15) [d].[Operation] , [d].[Context] , [record_count] = COUNT_BIG(*) , [total_mb] = CONVERT(decimal(12, 1), SUM([d].[Log Record Length]) / 1048576.0) , [avg_bytes] = CONVERT(int, AVG([d].[Log Record Length] * 1.0)) FROM [fn_dblog](NULL, NULL) AS [d] GROUP BY [d].[Operation], [d].[Context] ORDER BY SUM([d].[Log Record Length]) DESC; |
|
1 2 3 4 5 6 |
Operation Context record_count total_mb avg_bytes LOP_INSERT_ROWS LCX_CLUSTERED 10364 80.6 8158 LOP_MODIFY_HEADER LCX_HEAP 62179 4.9 82 LOP_INSYSXACT LCX_CLUSTERED 72538 4.3 62 ... LOP_FORMAT_PAGE LCX_UNLINKED_REORG_PAGE 10521 0.8 84 |
Those LOP_INSERT_ROWS records average 8,158 bytes: entire pages of rows being written to newly formatted pages. On a 5,268-page index with 0.13% fragmentation, reorganize physically relocated roughly every leaf page, twice over. Follow-up tests ruled out my first two theories: it is not fill factor compaction (rebuilding at fill factor 80 produced the same full rewrite with density unchanged at 83.95% before and after), and it is not parallelism alone (a MAXDOP = 1 rebuild halved the page moves to one per page, but reorganize still rewrote the whole leaf level).
Two more results complete the picture. Running REORGANIZE a second time, immediately, logged 0.0 MB, so reorganize does converge on a layout it is happy with. And the same experiment against my GUID-keyed baseline cost only 91 MB, not 2,566 MB, for two indexes that look identical in sys.dm_db_index_physical_stats: near-zero fragmentation, 99.7% density.
The honest conclusion: REORGANIZE decides how much work to do using an internal physical-ordering criterion that avg_fragmentation_in_percent does not measure and cannot predict. Depending on the physical layout the previous rebuild happened to produce, reorganizing a “clean” index can cost anywhere from a tenth of the index size to two and a half times the index size in log. You cannot forecast it from the DMV your maintenance script reads.
Round three: the steady-state scenario
A fair objection: nobody reorganizes a freshly rebuilt index. A real nightly job reorganizes an index that was reorganized last night, so maybe the full-rewrite cost is a one-time transition tax and steady-state nightly reorg is cheap.
I tested that too: reorganize the GUID index to convergence (verified: an immediate second pass logs 0.0 MB), back up that state, apply a “day” of churn (the same random-GUID insert volumes that produced the 10/30/80% states), and reorganize again.
| Churn | Fragmentation after churn | REORGANIZE from converged state | REORGANIZE from rebuilt state |
|---|---|---|---|
| 8,000 rows | 11.1% | 2,144 MB | 2,152 MB |
| 26,000 rows | 30.1% | 2,656 MB | 2,656 MB |
| 154,000 rows | 80.3% | 3,408 MB | 3,410 MB |
No savings. None. Random-key churn re-dirties the physical layout thoroughly enough that every nightly reorganize is a full leaf-level rewrite. The convergence discount only exists for an index nothing writes to, and an index nothing writes to does not fragment in the first place.
Round four: the test nobody needed to run (but should see)
For every fragmentation state above, I also ran the identical insert volumes into the bigint-keyed twin of the table (filler widened by eight bytes so row size matches exactly).
| Rows inserted | GUID key: frag / density / pages | Sequential key: frag / density / pages |
|---|---|---|
| 8,000 | 11.0% / 94.3% / 139,349 | 0.03% / 99.74% / 132,003 |
| 26,000 | 30.0% / 85.1% / 155,048 | 0.03% / 99.74% / 132,950 |
| 154,000 | 80.2% / 63.4% / 221,050 | 0.06% / 99.74% / 139,688 |
The same 154,000 inserts that drove the GUID index to 80% fragmentation and 58% more pages left the sequential index at 0.06% fragmentation and full density. With a 5% action floor, the sequential index never qualifies for maintenance at all. Zero log, zero maintenance window, zero debate about which operation to use. The cheapest index maintenance is the maintenance you never have to run, and that decision gets made when you choose the clustering key, not at 2 AM.
Where reorganize genuinely wins: peak log, not total log
There is one measurement that lands on the other side of the ledger, and it matters for exactly the environments FULL recovery implies. REBUILD is a single transaction. Until it commits, none of its log can be truncated, so its peak active log equals its total, no matter how often you back up the log. REORGANIZE runs as many small transactions, and log backups taken during the operation truncate as it goes.
With log backups every 15 seconds:
| Fragmentation | REORGANIZE peak active log | REBUILD peak active log |
|---|---|---|
| 11% | 2,026 MB (op too fast to benefit) | 1,392 MB |
| 30% | 1,464 MB | 1,707 MB |
| 80% | 1,132 MB | 1,676 MB |
At 80% fragmentation, reorganize’s peak log footprint was a third lower than rebuild’s, even though its total volume was 84% higher. If your constraint is log file size, log-shipping bandwidth per interval, or availability group redo bursts, rather than total log volume, reorganize with aggressive concurrent log backups is measurably gentler. It is also interruptible mid-flight without losing completed work, which a maintenance window sometimes demands.
Reconfiguring your rebuild/reorg jobs
Everything below stays inside what these tests actually measured. Adjust for your own workload, and test before trusting me or anyone else.
- Keep the floor. A do-nothing threshold at 5% fragmentation (with a minimum page count guard) costs you nothing and protects you from the degenerate case these tests exposed, where reorganize rewrites an entire leaf level that had nothing wrong with it.
- Stop treating reorganize as the low-log option in the 5-30% band. That is precisely the range where it generated 1.2 to 1.5 times more total log than an online rebuild, ran slower, and left a worse index (0.29% fragmentation and 95-96% density versus rebuild’s 0.01% and 99.7%). If you can rebuild online and your log can absorb roughly 1.7 times the index size, lower your rebuild cutoff and let more indexes rebuild.
- Budget reorganize at a full leaf rewrite, every time. When you do run it, size expectations at roughly 2 times the index size in log, regardless of the fragmentation number that triggered it. Do not estimate log volume from
avg_fragmentation_in_percent; these measurements show it has essentially no predictive power for reorganize’s cost. - If you keep reorganize, pair it with frequent log backups during the maintenance window. Its entire advantage is that concurrent truncation caps the peak. A reorganize running between two hourly log backups gets you the worst of both worlds: rebuild-sized-or-larger total volume and no truncation relief. Shorten the log backup interval during maintenance (I used 15 seconds in testing; even one minute changes the picture for longer operations).
- Reserve reorganize for the cases where its properties are the point: editions or indexes where
ONLINErebuild is unavailable, windows too short to fit a rebuild’s single uninterruptible transaction, and log-file or redo-throughput ceilings where peak matters more than total. - Add a statistics update wherever reorganize replaces a rebuild. Rebuild refreshes statistics as a side effect; reorganize does not. A reorg-heavy schedule silently ages your statistics unless you follow it with
UPDATE STATISTICS. - Pre-size the log for maintenance and stop reacting to “growth.” In every one of my runs the log file never grew, because it was sized for the operation up front. Log growth during index maintenance is a sizing decision you have deferred, not a property of either command.
- Spend the effort on clustering keys instead. The most effective change in this entire post is the one that eliminates the job: sequential keys took 19 times the churn without ever crossing a 5% action floor. Every random-key clustered index you fix removes an index from the maintenance debate permanently.
Limitations
This is one table shape, one storage subsystem, single-user, on SQL Server 2019 Developer Edition. I tested insert-only churn; deletes and updates create ghost records and forwarded fragments that give reorganize’s compaction phase real work with different economics. The 15-second log backup cadence is at the aggressive end. And the internal ordering criterion reorganize uses remains, from the outside, undocumented; I can show you what it costs, but not its exact rulebook. If your measurements disagree with mine, I would genuinely like to see them.
The scorecard
Jeff’s core claim held up: REORGANIZE generates substantially more total transaction log than most of us were taught, and the “it only touches what it moves” defense obscures the fact that it can move everything. My counterpoint survived in narrower form than I originally argued it: reorganize’s small transactions genuinely cap peak log usage when log backups run concurrently, which is a real advantage in availability group and log shipping environments, and my own script’s thresholds would have chosen REBUILD for the badly fragmented cases anyway. The measurement neither of us had in hand is that fragmentation percentage predicts almost nothing about reorganize’s cost.
That is the fun of measuring things: everybody’s model turns out to be simpler than the server.
Got measurements of your own, or a workload where these numbers bend the other way? I would love to hear about it in the comments, or find me on Bluesky and LinkedIn.