SQL Server Maintenance: Index Fragmentation, Rebuild vs Reorganize, Statistics Updates, DBCC CHECKDB and Ola Hallengren

Version note: The T-SQL examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, Enterprise Developer Edition) in a Linux container against the series' Bookstore lab database. The 2026-09-12 release of Ola Hallengren's SQL Server Maintenance Solution (MIT licensed) was used.

Guess First: Is REORGANIZE Light and REBUILD Heavy?

In the series' lab database (the database of Bookstore, the online bookstore used as the shared example across this site's series) I simulated a month: the lines of one fifth of the old orders were archived and deleted, and 200,000 lines were added for new orders. Because the new lines have random BookId values, they were written into the middle of the IX_OrderItems_BookId index:

DELETE FROM dbo.OrderItems WHERE OrderId % 5 = 0 AND OrderId <= 600000;
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
SELECT 900000 + g.value / 2, 1 + (g.value * 7919) % 40000, 1, 10.00
FROM GENERATE_SERIES(1, 200000) AS g;

Then I kept the database in FULL recovery and put this index through maintenance in six different ways. Before each attempt I restored the database from the same backup and took a log backup, so every row starts from the same point. After each attempt I took this measurement:

SELECT CAST(ips.avg_fragmentation_in_percent AS decimal(5,1)) AS frag_pct,
       CAST(ips.avg_page_space_used_in_percent AS decimal(5,1)) AS density_pct,
       ips.page_count, sp.rows, sp.rows_sampled
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.OrderItems'), 4, NULL, 'DETAILED') AS ips
CROSS APPLY sys.dm_db_stats_properties(ips.object_id, ips.index_id) AS sp
WHERE ips.index_level = 0;
SET STATISTICS IO ON;
SELECT COUNT_BIG(*) AS lines FROM dbo.OrderItems WITH (INDEX (IX_OrderItems_BookId));
SET STATISTICS IO OFF;
SELECT CAST(log_since_last_log_backup_mb AS decimal(8,1)) AS log_mb FROM sys.dm_db_log_stats(DB_ID());

frag_pct  density_pct  page_count  rows     rows_sampled
--------  -----------  ----------  -------  ------------
39.1      77.0         5390        2500496  2500496

lines
-------
2399937

Table 'OrderItems'. Scan count 11, logical reads 5470, ...

The index is 39% fragmented and its pages are 77% full on average. Now guess: REORGANIZE is known as the "light" option. Which operation produces the most log, and which one updates statistics?

ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems REORGANIZE;
ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems REBUILD;
ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems REBUILD WITH (ONLINE = ON);
ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems REBUILD WITH (ONLINE = ON, RESUMABLE = ON);
ALTER INDEX IX_OrderItems_BookId ON dbo.OrderItems REBUILD;
UPDATE STATISTICS dbo.OrderItems IX_OrderItems_BookId;

Four results in this table go against intuition. REORGANIZE produced 3.6 times the log of an offline REBUILD, because it moves rows page by page and logs every move, while the offline rebuild writes the new index as whole pages. The ONLINE rebuild produced seven times the log, because it keeps two copies in step so that users can keep writing to the table. REORGANIZE didn't touch the statistics: rows still counts the deleted rows. And the UPDATE STATISTICS run after the rebuild "just to be safe" replaced the statistics the rebuild built with a full scan (FULLSCAN) with an 11% sample; the resumable rebuild did the same on its own. On the read side, logical reads follow the page count: page density decides, not fragmentation. In this post we build index maintenance, statistics, DBCC CHECKDB and Ola Hallengren's scripts, which run them together, on top of these measurements.

Fragmentation, Page Density and Microsoft's Current Guidance

sys.dm_db_index_physical_stats gives two numbers. avg_fragmentation_in_percent is how far the logical order of the leaf-level pages differs from their physical order on disk (logical fragmentation). avg_page_space_used_in_percent is how full the pages are (page density). LIMITED mode computes only fragmentation, cheaply; density needs SAMPLED or DETAILED, and on big tables DETAILED reads the whole index.

For years the rule was "reorganize between 5 and 30 percent, rebuild above 30". The index maintenance article on Microsoft Learn doesn't recommend these thresholds today. Its essence: maintenance decisions should be made by measuring against the workload, not by a fixed fragmentation or density threshold; in many workloads raising page density makes more difference than reducing fragmentation; a large part of the speed-up seen after a rebuild actually comes from the rebuild updating statistics with a full scan, and the same effect can be had much more cheaply by updating statistics alone. The harm of fragmentation is that on large range scans SQL Server issues many small reads instead of a few large ones; on modern SSDs, where sequential and random reads cost the same, the effect shrinks. The strategy the article recommends follows from this: measure the effect of maintenance before and after (A/B) with Query Store (post 8), try updating statistics instead if a rebuild helps, track fragmentation and density over time, and maintain only the indexes of queries whose performance drops.

Fill factor matters in this context too. Setting fill factor below 100 deliberately leaves pages empty, so you lower page density yourself. Microsoft recommends a value other than 100 only for indexes with very frequent page splits (for example, frequently updated indexes keyed on non-sequential GUIDs). Remember the NEWID experiment from post 2 of the series.

REBUILD, REORGANIZE, ONLINE and RESUMABLE

REORGANIZE is always online, it only reorders the leaf level and compacts pages up to the fill factor. If it's interrupted, the work done isn't lost; you can reorganize a big index piece by piece over several nights. It doesn't work on an index with ALLOW_PAGE_LOCKS = OFF and doesn't update statistics.

REBUILD writes the index from scratch, removes fragmentation at every level and, for rowstore indexes, updates statistics with a full scan. An offline rebuild locks the table for the duration but is the cheapest path; above it produced the least log. ONLINE = ON keeps the table available until the end, at the cost of more log, tempdb and time. RESUMABLE = ON makes an online rebuild pausable. I started a rebuild of the big clustered index in one session and paused it from a second one:

ALTER INDEX PK_OrderItems ON dbo.OrderItems REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 1);

ALTER INDEX PK_OrderItems ON dbo.OrderItems PAUSE;
SELECT name, state_desc, CAST(percent_complete AS decimal(5,1)) AS percent_complete, page_count
FROM sys.index_resumable_operations;

name           state_desc  percent_complete  page_count
-------------  ----------  ----------------  ----------
PK_OrderItems  PAUSED      54.9              7265

The first session disconnected with this message:

Msg 1219, Level 16, State 1, Server sql1, Line 1
Your session has been disconnected because of a high priority DDL operation.

ALTER INDEX PK_OrderItems ON dbo.OrderItems RESUME;
SELECT COUNT(*) AS resumable_ops FROM sys.index_resumable_operations;

resumable_ops
-------------
0

The rebuild continued from 54.9% and finished. If the maintenance window closes at six in the morning, MAX_DURATION = 120 MINUTES pauses the rebuild on its own and you can continue the next night from where it stopped. There are two costs: until a paused resumable operation finishes, every write to the table also updates the second copy of the index, so if you won't complete it, ABORT it; and, as in the table, a resumable rebuild updates statistics with the default sample, not a full scan. ONLINE and RESUMABLE are Enterprise edition features (Developer edition included); on Standard edition you have offline rebuild and reorganize.

Updating Statistics

We saw what statistics mean to the optimizer, the histogram and the automatic update threshold in post 4. For maintenance, three rules are enough. Don't touch the statistics of a rebuilt index again the same night; the last row above shows what replacing full-scan statistics with sampled ones looks like. Plan updates separately for reorganized indexes and for column statistics not tied to an index (_WA_Sys_...). And update only statistics that changed: modification_counter in sys.dm_db_stats_properties tells you that.

AUTO_UPDATE_STATISTICS should stay on. AUTO_UPDATE_STATISTICS_ASYNC keeps a query from waiting at compile time on outdated statistics and pushes the update into the background; consider it if you see OLTP queries waiting on a statistics update of a big table the moment the threshold is crossed. For some statistics the sample isn't enough; then WITH SAMPLE ... PERCENT, PERSIST_SAMPLE_PERCENT = ON fixes the sampling rate for later automatic updates as well.

DBCC CHECKDB: How Often, and First Steps When Corruption Is Found

A backup backs up a corrupt database just as well; WITH CHECKSUM (post 20) only catches pages whose page checksum doesn't match. The only tool that checks the logical and physical consistency of a database is DBCC CHECKDB. To see corruption, in my own test database I damaged a leaf page of IX_OrderItems_BookId with the undocumented DBCC WRITEPAGE, writing directly to disk and bypassing the buffer pool. Do this only in a throwaway test database:

SELECT TOP (1) allocated_page_file_id, allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.OrderItems'), 4, NULL, 'DETAILED')
WHERE page_type_desc = 'INDEX_PAGE' AND page_level = 0
ORDER BY allocated_page_page_id DESC;

ALTER DATABASE sqls_21 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC WRITEPAGE (N'sqls_21', 1, 58908, 4000, 4, 0x00000000, 1);
ALTER DATABASE sqls_21 SET MULTI_USER;

The application only finds out when a query reads the damaged page:

SELECT COUNT_BIG(*) AS lines FROM dbo.OrderItems WITH (INDEX (IX_OrderItems_BookId));

Msg 824, Level 24, State 2, Server sql1, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x73eb344b; actual: 0x52ca156a). It occurred during a read of page (1:58908) in database ID 43 at offset 0x0000001cc38000 in file '/var/opt/mssql/data/sqls_21.mdf'. ...

DBCC CHECKDB (sqls_21) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Msg 8978, Level 16, State 1, Server sql1, Line 1
Table error: Object ID 1349579846, index ID 4, partition ID 72057594048348160, alloc unit ID 72057594055098368 (type In-row data). Page (1:55584) is missing a reference from previous page (1:58908). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Server sql1, Line 1
Table error: Object ID 1349579846, index ID 4, partition ID 72057594048348160, alloc unit ID 72057594055098368 (type In-row data). Page (1:58908) was not seen in the scan although its parent (1:55456) and previous (1:58907) refer to it. Check any previous errors.
Msg 8939, Level 16, State 98, Server sql1, Line 1
Table error: Object ID 1349579846, index ID 4, partition ID 72057594048348160, alloc unit ID 72057594055098368 (type In-row data), page (1:58908). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Server sql1, Line 1
Object ID 1349579846, index ID 4, partition ID 72057594048348160, alloc unit ID 72057594055098368 (type In-row data): Page (1:58908) could not be processed.  See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'OrderItems' (object ID 1349579846).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'sqls_21'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (sqls_21).

The last line is scary: "at least repair_allow_data_loss is needed". The worst thing to do at this point is to run that option; as the name says, it reaches consistency by deleting rows. The first step is to read where the error is: every message says index ID 4. Index ID 0 is a heap and 1 a clustered index, that is the data itself; 2 and above are nonclustered indexes and carry a copy of the data. Dropping and recreating a damaged nonclustered index reads the data again from the clustered index and loses nothing:

DROP INDEX IX_OrderItems_BookId ON dbo.OrderItems;
CREATE INDEX IX_OrderItems_BookId ON dbo.OrderItems (BookId);
DBCC CHECKDB (sqls_21) WITH NO_INFOMSGS;
SELECT COUNT_BIG(*) AS lines FROM dbo.OrderItems WITH (INDEX (IX_OrderItems_BookId));
DELETE FROM msdb.dbo.suspect_pages WHERE database_id = DB_ID('sqls_21');

lines
-------
2399937

This time CHECKDB printed nothing (with NO_INFOMSGS a clean result is silent) and all 2,399,937 rows are there. I dropped and recreated instead of using REBUILD because an online rebuild can use the existing index as its source and carry the damage over. If the clustered index or heap is damaged (index ID 0 or 1), the right path is a restore from backup: you can restore only the damaged pages with RESTORE DATABASE ... PAGE = '1:58908' followed by the log chain (on Enterprise edition the database stays online meanwhile). Unreadable pages are written to msdb.dbo.suspect_pages; the table is limited to 1,000 rows, so it should be cleaned once the incident is closed. And corruption almost always comes from the storage layer: other things on the same disk may be breaking too.

Microsoft has no single number for frequency; a common practice is this: a full CHECKDB once a week on small and medium databases. On big databases, WITH PHYSICAL_ONLY at night (it checks page checksums and the allocation structure, skips the logical checks and takes much less time) and a full CHECKDB once a week or on every restore drill. Running the full check on the restored copy instead of the production server moves the load and also proves that the backup really opens.

Ola Hallengren's Maintenance Solution

You can write all of this yourself, but there's a widely used, well-tested solution: Ola Hallengren's MIT-licensed SQL Server Maintenance Solution. A single script installs the IndexOptimize, DatabaseIntegrityCheck and DatabaseBackup procedures and the dbo.CommandLog table that records results. I changed two lines at the top of the script so that it installs its objects into my own database instead of master and doesn't create Agent jobs (we'll build the jobs in post 22):

USE [sqls_21]
DECLARE @CreateJobs nvarchar(max)          = 'N'

First I called it with @Execute = 'N', which runs nothing, to see what it would do, on the database restored to its pre-maintenance state:

EXEC dbo.IndexOptimize
    @Databases = 'sqls_21',
    @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',
    @Execute = 'N';

Command: ALTER INDEX [IX_OrderItems_BookId] ON [dbo].[OrderItems] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, FileStream: No, HasClusteredColumnstore: No, AllowPageLocks: Yes, PageCount: 5390, Fragmentation: 39.1095
Outcome: Not Executed

Command: ALTER INDEX [IX_OrderItems_OrderId] ON [dbo].[OrderItems] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, FileStream: No, HasClusteredColumnstore: No, AllowPageLocks: Yes, PageCount: 4764, Fragmentation: 18.3249
Outcome: Not Executed

The thresholds are still based on fragmentation (the parameters are as in the script's examples), but there are two good decisions: it doesn't touch indexes smaller than 1,000 pages, and it tries an online rebuild first. If nobody writes to the table at night, @FragmentationHigh = 'INDEX_REBUILD_OFFLINE' means seven times less log. I ran it with @Execute = 'Y' and read the result from the table:

SELECT CommandType, ISNULL(IndexName, StatisticsName) AS object_name,
       DATEDIFF(MILLISECOND, StartTime, EndTime) AS ms, ErrorNumber
FROM dbo.CommandLog
WHERE DatabaseName = 'sqls_21' AND ErrorNumber IS NOT NULL
ORDER BY ID;

CommandType         object_name                ms    ErrorNumber
------------------  -------------------------  ----  -----------
UPDATE_STATISTICS   PK_CommandLog              4     0
UPDATE_STATISTICS   PK_OrderItems              65    0
ALTER_INDEX         IX_OrderItems_BookId       795   0
ALTER_INDEX         IX_OrderItems_OrderId      1860  0
UPDATE_STATISTICS   IX_OrderItems_OrderId      82    0
UPDATE_STATISTICS   _WA_Sys_00000002_5070F446  56    0
UPDATE_STATISTICS   PK_Payments                48    0
UPDATE_STATISTICS   PK_Shipments               48    0

The rows to note: the statistics of the rebuilt IX_OrderItems_BookId aren't in the list, while those of the reorganized IX_OrderItems_OrderId and the auto-created column statistic are. So the script avoids both traps from the Guess First table. Integrity check and backup work the same way:

EXEC dbo.DatabaseIntegrityCheck @Databases = 'sqls_21', @CheckCommands = 'CHECKDB', @LogToTable = 'Y';
EXEC dbo.DatabaseBackup
    @Databases = 'sqls_21',
    @Directory = '/var/opt/mssql/backup',
    @DirectoryStructure = '{DatabaseName}{DirectorySeparator}{BackupType}',
    @FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}.{FileExtension}',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @Verify = 'Y',
    @LogToTable = 'Y';

Command: DBCC CHECKDB ([sqls_21]) WITH ALL_ERRORMSGS
CHECKDB found 0 allocation errors and 0 consistency errors in database 'sqls_21'.
Outcome: Succeeded
Command: BACKUP DATABASE [sqls_21] TO DISK = N'/var/opt/mssql/backup/sqls_21/FULL/sqls_21_FULL_20261114_235903.bak' WITH CHECKSUM, COMPRESSION (ALGORITHM = MS_XPRESS, LEVEL = LOW)
Outcome: Succeeded
Command: RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/backup/sqls_21/FULL/sqls_21_FULL_20261114_235903.bak' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded

I set @DirectoryStructure and @FileName on purpose: the default folder structure includes the server name. These are T-SQL calls that work the same on Windows and Linux and can be kept in source control; that's the biggest difference from the Maintenance Plan Wizard in SSMS. The plan the wizard produces is stored in msdb as an SQL Server Integration Services (SSIS) package, its diff can't be read, and it offers side by side exactly the options that build the trap above, such as "rebuild every index every night, then update all statistics". Another ready-made solution Microsoft refers to on Learn is Adaptive Index Defrag.

Bookstore: Building the Nightly Maintenance Window by Measuring

At Bookstore, orders are at their lowest between 01:00 and 05:00, but not zero: institutional customers' integrations write orders at night too. So an offline rebuild only suits small indexes that finish quickly; big tables like OrderItems need online. The measurements also set the frame of the plan: on this index an online rebuild produced 252 MB of log and a reorganize 128 MB; if every big index goes through maintenance on the same night, log backups and replica lag grow by that much. The plan we set up for Bookstore: every night, only changed statistics; once a week, an online rebuild for indexes larger than 1,000 pages whose page density drops below a chosen threshold; no reorganize (on this index its log cost is higher than a rebuild's and it doesn't update statistics); PHYSICAL_ONLY every night and a full CHECKDB on Sunday on the restore drill database. 80% is a starting value for the density threshold; the right way is to track the duration of reporting queries in Query Store together with density for a few weeks and decide. Because Ola's IndexOptimize doesn't take page density as a threshold, a query picks the indexes that match this rule and passes them in the @Indexes parameter.

What's different in SQL Server 2019/2022: RESUMABLE rebuild came in 2017 and resumable online index creation in 2019; MAX_DURATION and WAIT_AT_LOW_PRIORITY exist in all three versions. 2022 added the AUTO_DROP option for statistics. In 2025 ordered columnstore indexes can be built online and readable secondaries get persisted statistics. The syntax and behavior of DBCC CHECKDB are the same in all three versions.

Watch Out in Production

Account for the log of maintenance. Above, the online rebuild produced log seven times larger than the index itself (about 33 MB); in FULL recovery it goes to log backups, in an availability group to the secondaries. Watch the first log backup and replica lag after a big rebuild.

A rebuild needs room on disk for a second copy of the index; if you use SORT_IN_TEMPDB = ON for the sort, in tempdb too.

Watch and clean CommandLog. Failed commands are there with their ErrorNumber; the table doesn't shrink on its own, and Ola's installation creates a separate job named "CommandLog Cleanup" for this.

Common Pitfalls

Rebuilding every index every night. It costs log, CPU and replica lag; much of the gain usually comes only from the statistics update.

Updating statistics a second time after a rebuild. The last row of the table: full-scan statistics replaced by an 11% sample.

Assuming REORGANIZE is always cheap. On this index it produced 3.6 times the log of an offline rebuild.

Never running DBCC CHECKDB, or treating REPAIR_ALLOW_DATA_LOSS as the first step when you see an error. Read the index ID first; if it's a nonclustered index, drop and recreate it.

Setting fill factor to 80 on every index "to avoid page splits". You permanently lower the page density of every index to 80%.

A Small Experiment

★☆☆ List the fragmentation and page density of all indexes in your own database in SAMPLED mode. Is there an index larger than 1,000 pages with density below 70%?

★★☆ Repeat the Guess First experiment in SIMPLE recovery. How does the offline rebuild's log change? And the reorganize's?

★★★ Run IndexOptimize with @Resumable = 'Y' and @TimeLimit and look at how a rebuild left halfway appears in sys.index_resumable_operations. Does the next run resume it?

Every job in this post is waiting for a scheduler: they'll run at night and tell someone when they fail. In the next post we'll look at SQL Server Agent's structure of jobs, steps, schedules, alerts and operators, Database Mail, and Agent's limits on Linux and in containers.