SQL Server Index Maintenance: Rebuild vs Reorganize

2018-02-03 · mssql, performance, maintenance

Indexes fragment over time as rows are inserted, updated and deleted. Logical fragmentation means the pages are out of order on disk; low page density means each page is only partly full, so the same data needs more pages and more reads. SQL Server offers two ways to fix this: REORGANIZE, which shuffles leaf pages in place, and REBUILD, which drops and recreates the index from scratch. They behave very differently in terms of locking, logging and duration, and choosing the wrong one for a large table can take down an application for the night. I've seen it happen, and I've been the one who started it.

Here's how I measure fragmentation, decide between the two, and automate it on SQL Server 2016 and 2017.

Measure first

The DMV sys.dm_db_index_physical_stats does the work. In LIMITED mode it only reads the non-leaf levels and is fast enough to run on a production instance:

SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS schema_name,
       OBJECT_NAME(ps.object_id) AS table_name,
       i.name AS index_name,
       ps.index_type_desc,
       ps.avg_fragmentation_in_percent,
       ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.page_count > 1000
  AND ps.avg_fragmentation_in_percent > 5
ORDER BY ps.avg_fragmentation_in_percent DESC;

Ignore indexes under about 1000 pages (8 MB). Their fragmentation percentage is meaningless because a handful of extents can never be perfectly ordered, and they fit in the buffer pool anyway.

Reorganize for moderate fragmentation

The long-standing guideline is: 5 to 30 percent fragmentation, reorganize; above 30 percent, rebuild. Reorganize is always an online operation, takes only short-duration page locks, can be stopped at any time without losing the work done so far, and is minimally logged in bulk-logged and simple recovery. It does not update statistics.

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;
ALTER INDEX ALL ON dbo.Orders REORGANIZE WITH (LOB_COMPACTION = ON);

The downside is that it only compacts and reorders leaf pages within existing allocations, so it cannot fix a badly chosen fill factor or reclaim space from a table that had half its rows deleted.

Rebuild for heavy fragmentation

A rebuild creates a brand new copy of the index, respecting the fill factor, and then swaps it in. It updates the index's statistics with a full scan as a side effect, which is often the real reason a rebuild "made things faster".

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 4);

On Standard Edition a rebuild takes a schema-modification lock for its full duration: the table is unavailable. On Enterprise Edition add ONLINE = ON, which keeps the table readable and writable except for brief locks at the start and end. Since SQL Server 2014 you can control that final lock:

ALTER INDEX ALL ON dbo.Orders
REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));

SQL Server 2017 adds resumable online rebuilds, which is a real help on a huge table when the maintenance window ends before the rebuild does:

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);
-- later
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders PAUSE;
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders RESUME;

Let a script do it, not a maintenance plan

The built-in Maintenance Plan "Rebuild Index" task rebuilds every index every time regardless of fragmentation. I stay away from it. Ola Hallengren's IndexOptimize procedure is free, widely used, and applies thresholds per index:

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,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y';

The comma-separated list is a preference order: try an online rebuild, fall back to offline if the edition or the index (for example one with a legacy LOB column) does not support online. Schedule this weekly in a SQL Server Agent job.

Checking the result

Rerun the fragmentation query from the top. Then check the CommandLog table that IndexOptimize writes to, which records each command, its duration and any error:

SELECT DatabaseName, ObjectName, IndexName, Command, StartTime, EndTime, ErrorNumber
FROM dbo.CommandLog
WHERE StartTime > DATEADD(DAY, -1, GETDATE())
ORDER BY DATEDIFF(SECOND, StartTime, EndTime) DESC;

Odd cases

"Msg 2725: An online operation cannot be performed for index ... because the index contains column ... of data type text, ntext, image, or FILESTREAM." On SQL Server 2012 and later the xml, varchar(max) and similar types are fine online, but the legacy LOB types are not. Rebuild that index offline or, better, change the column type.

Transaction log fills during the rebuild. A rebuild is fully logged in the full recovery model, roughly the size of the index. Make sure log backups run frequently during the window, or size the log for the largest index.

Fragmentation returns within days. The index has a sequential-looking key that is not actually sequential (a uniqueidentifier with NEWID(), for example). Lower the fill factor to 80 or 90 on that index so page splits have room, or switch the default to NEWSEQUENTIALID().

Rebuild is not making queries faster. Fragmentation matters much less on SSDs and when the data is already in memory. Often the win was the statistics update; try UPDATE STATISTICS ... WITH FULLSCAN on its own before scheduling nightly rebuilds.

Honestly, most of the "index maintenance problems" I see are really stale statistics. I'd look at that before touching the rebuild schedule.