The Guard Clauses Your Index Rebuild Script Needs
Writing a loop that rebuilds fragmented indexes takes about ten minutes. Writing one you can safely leave running unattended at 2 AM takes considerably longer, and the difference is entirely in the guard clauses: the checks that decide when not to touch an index, and which options are even legal for the index in front of you.
Years ago I published my index reorg/rebuild script, and it still runs nightly on instances I look after. This post is about the parts of that script people tend to skim past: the guards. Each one exists because something, somewhere, once went wrong without it.
Guard 1: Minimum page count
The script skips any index smaller than a configurable page count, defaulting to 1,000 pages (about 8 MB). Fragmentation in a small index is irrelevant: the whole thing fits in a handful of extents, likely stays in the buffer pool, and the “fragmentation” percentage swings wildly because the denominator is tiny. A 40-page index at 87% fragmentation is not a problem; it is arithmetic noise.
The 1,000-page threshold traces back to guidance Paul Randal wrote into Books Online while he was at Microsoft, and he has been refreshingly honest that the numbers were made up as a reasonable starting point rather than derived from benchmarks[1]. They remain a sensible default precisely because they are conservative.
Guard 2: A fragmentation floor, and a reorg/rebuild cutoff
Two separate thresholds, doing two different jobs:
- Below the floor (default 5%), do nothing. The index is fine. Leave it alone.
- Between the floor and the cutoff (default 20%),
REORGANIZE. Reorganize is always online, always interruptible, and only logs the pages it actually moves. - Above the cutoff,
REBUILD. At high fragmentation, rebuilding is cheaper than shuffling most of the leaf level page by page.
Before tuning these numbers, read Erik Darling’s take on what rebuilding indexes actually improves[2]. On modern storage the win is usually smaller than people expect, and sometimes the real benefit is just the statistics update that a rebuild happens to include. The floor exists so your maintenance window is not spent polishing indexes nobody would ever notice.
Guard 3: ALLOW_PAGE_LOCKS = OFF means reorganize is off the table
This is the guard that surprises people. ALTER INDEX ... REORGANIZE acquires page locks as it works. If the index was created with ALLOW_PAGE_LOCKS = OFF, a reorganize attempt fails with error 2552. So the script checks the setting and force-promotes those indexes to REBUILD, even when the fragmentation number says reorganize would do:
|
1 2 3 4 5 |
IF (@AvgFragmentationInPercent >= @reorg_fragmentation_limit) OR (@AllowPageLocks = N'ALLOW_PAGE_LOCKS = OFF') BEGIN SET @Rebuild = 1; END; |
If your maintenance job has ever died overnight with a page-lock error, this is almost certainly why.
Guard 4: LOB columns block ONLINE rebuilds
Indexes that contain text, ntext, image, or FILESTREAM columns cannot be rebuilt with ONLINE = ON[3]. For a clustered index, that means any such column in the table disqualifies the rebuild from running online, because the clustered index carries every column. The script builds a list of these offline-only indexes up front:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
WITH [OffLineOnlyIndexes] AS ( SELECT [o].[object_id] , [i].[index_id] FROM [sys].[indexes] AS [i] INNER JOIN [sys].[objects] AS [o] ON [i].[object_id] = [o].[object_id] INNER JOIN [sys].[index_columns] AS [ic] ON [i].[object_id] = [ic].[object_id] AND [i].[index_id] = [ic].[index_id] INNER JOIN [sys].[columns] AS [c] ON ( /* non-clustered indexes: just the index columns */ [i].[type] <> 1 AND [ic].[object_id] = [c].[object_id] AND [ic].[column_id] = [c].[column_id] ) OR ( /* clustered indexes: every column in the table */ [i].[type] = 1 AND [o].[object_id] = [c].[object_id] ) INNER JOIN [sys].[types] AS [ty] ON [c].[system_type_id] = [ty].[system_type_id] WHERE [ty].[name] IN (N'text', N'ntext', N'image', N'FILESTREAM') GROUP BY [o].[object_id] , [i].[index_id] ) |
Without this guard, the rebuild statement fails at runtime. With it, the index still gets rebuilt, just without the ONLINE = ON option, which brings us to the next check.
Guard 5: Is ONLINE even licensed here?
Online index rebuilds are an Enterprise Edition feature (which includes Developer and Evaluation). Instead of hard-coding a list of edition names, check the engine edition:
|
1 2 3 4 5 6 7 |
/* EngineEdition 3 = Enterprise, Developer, and Evaluation engines */ SET @OnlineOperations = CASE WHEN CONVERT(int, SERVERPROPERTY('EngineEdition')) = 3 THEN 1 ELSE 0 END; |
Run an ONLINE = ON rebuild on Standard Edition and it fails immediately. A related self-inflicted variant: developing your maintenance script against Developer Edition (where everything works) and deploying it to Standard (where it does not). If cross-edition surprises are a topic you enjoy, I wrote about the same class of problem from the database side in downgrading from Enterprise to Standard Edition.
The script also exposes @no_online_operations as an explicit override, because there are times you want offline rebuilds even on Enterprise: offline is faster, and if the table is not in use during the window, faster wins.
Guard 6: Preserve what the index already has
A rebuild does not just defragment; it re-declares the index. If your script issues a bare ALTER INDEX ... REBUILD without carrying over the existing settings, per-index customizations survive, but the moment you start specifying options you must specify all of the ones you care about. The script reads and re-applies, per index: PAD_INDEX, FILLFACTOR (translating the stored 0 back to the instance default), ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, IGNORE_DUP_KEY, and DATA_COMPRESSION.
That last one matters more than the others: rebuild a page-compressed index without specifying DATA_COMPRESSION = PAGE on a partitioned table’s partition and you can silently decompress it, growing the table and evicting a chunk of your buffer pool in one move.
Guard 7: MAXDOP = 1 on the rebuild
Counterintuitive, but deliberate: a parallel index rebuild can produce a fragmented result, because each worker thread builds its portion of the index independently and the portions interleave. Rebuilding an index to defragment it and getting fragmentation back is a special kind of irritating. MAXDOP = 1 trades rebuild speed for a cleanly ordered result:
|
1 2 3 4 5 6 7 |
ALTER INDEX [ix_example] ON [dbo].[some_table] REBUILD WITH ( SORT_IN_TEMPDB = ON , MAXDOP = 1 , ONLINE = ON ); |
SORT_IN_TEMPDB = ON is there for the same “do no harm” reason: it keeps the sort out of the user database, at the cost of tempdb space, and it is exposed as a parameter so you can flip it when tempdb is the scarcer resource.
Guard 8: Skip the indexes that should never be touched
The candidate list filters out disabled indexes, hypothetical indexes (leftovers from the Database Engine Tuning Advisor), heaps, indexes on table-valued function return tables and table types, and anything not in a real filegroup or partition scheme. It also restricts itself to index types it knows how to handle: clustered, nonclustered, and XML. Columnstore indexes, spatial indexes, and full-text indexes have their own maintenance semantics and deserve their own scripts rather than a general-purpose loop guessing at them.
One more structural note: the loop itself uses a LOCAL FORWARD_ONLY STATIC READ_ONLY cursor, which snapshots the candidate list into tempdb at OPEN time instead of holding the metadata views open while rebuilds run. I wrote about why that cursor pattern is my default in a better cursor pattern.
The point
None of these guards make the happy path faster. They exist for the unhappy paths: the page-lock error at 2 AM, the offline rebuild that blocked the overnight ETL, the decompressed partition, the maintenance job that spent forty minutes polishing 50-page indexes. If you maintain your own rebuild script, diff it against this list. If you use a community solution, most of these checks are why it is longer than yours.
The full script, with all parameters and the complete cursor loop, is in the original post.
What guard clause did your maintenance script learn the hard way? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- Where do the Books Online index fragmentation thresholds come from? – Paul Randal, SQLskills. The origin of the 5% / 30% / 1,000-page guidance, from the person who wrote it. ↩
- What Metrics Does Rebuilding Indexes Improve? – Erik Darling, erikdarling.com. A measured look at what index rebuilds actually buy you on modern storage. ↩
- ALTER INDEX (Transact-SQL) – Microsoft Learn. The full option matrix, including which index and column types disqualify ONLINE operations. ↩