DBCC DBREINDEX vs ALTER INDEX REBUILD in SQL Server

calendar Jun 3, 2026 / · 6 min read · sql server index maintenance index fragmentation database maintenance performance tuning maintenance scripts sql scripts dbcc commands ALTER INDEX sys.dm_db_index_physical_stats sys.indexes sys.objects  ·
Share on: linkedin copy

Open the SQL Agent job history on an instance that has been running for a decade and you will still find DBCC DBREINDEX in a nightly maintenance step, quietly doing its job. It works — which is exactly why nobody has touched it. But it has been deprecated for the better part of twenty years, it locks tables it does not need to lock, and it cannot do the things modern index maintenance takes for granted. This post compares it to its replacement and shows the one-afternoon migration to ALTER INDEX.

Purpose and Overview

DBCC DBREINDEX has been on the deprecation list since SQL Server 2005, kept alive only for backward compatibility — yet legacy maintenance jobs across countless instances still call it, and Microsoft has warned for years that it will be removed in a future version. Every release that ships with it is borrowed time. The command rebuilds one or all indexes on a table, reclaiming space and removing fragmentation, and for a 2003-era workload it was perfectly adequate.

The replacement, ALTER INDEX, is not merely a renamed equivalent — it is a more capable statement that does things DBREINDEX was never built for. It can rebuild indexes ONLINE so the table stays available to readers and writers during the operation, it can target a single partition rather than the whole index, and it offers REORGANIZE as a lighter-weight alternative that defragments the leaf level without a full rebuild. Because ALTER INDEX is part of the supported DDL surface and DBREINDEX is not, every new feature lands on ALTER INDEX and never on the old command.

The migration is mechanical. Most DBREINDEX calls map one-to-one onto ALTER INDEX ... REBUILD, and the only real decision is whether to fold in a fragmentation threshold so you stop rebuilding indexes that do not need it. The sections below give the syntax mapping and a threshold-driven script that picks REORGANIZE or REBUILD per index based on actual fragmentation.

Code Breakdown

The first three statements show the old form and its modern equivalents; the cursor is the production pattern — rebuild only what is fragmented enough to justify it.

 1-- OLD (deprecated): rebuild all indexes on a table at fill factor 90.
 2DBCC DBREINDEX ('dbo.Orders', '', 90);
 3
 4-- NEW: the direct equivalent, with the option to stay online.
 5ALTER INDEX ALL ON dbo.Orders
 6    REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);
 7
 8-- LIGHTER: defragment the leaf level without a full rebuild. Always online.
 9ALTER INDEX ALL ON dbo.Orders REORGANIZE;
10
11-- PRODUCTION PATTERN: choose REORGANIZE vs REBUILD per index by fragmentation.
12SET NOCOUNT ON;
13DECLARE @sql nvarchar(max);
14
15DECLARE idx_cursor CURSOR LOCAL FAST_FORWARD FOR
16    SELECT
17        CASE
18            WHEN ps.avg_fragmentation_in_percent > 30
19                THEN N'ALTER INDEX ' + QUOTENAME(i.name)
20                   + N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id))
21                   + N'.' + QUOTENAME(o.name) + N' REBUILD WITH (FILLFACTOR = 90);'
22            ELSE N'ALTER INDEX ' + QUOTENAME(i.name)
23                   + N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id))
24                   + N'.' + QUOTENAME(o.name) + N' REORGANIZE;'
25        END
26    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
27    JOIN sys.indexes AS i
28         ON i.object_id = ps.object_id AND i.index_id = ps.index_id
29    JOIN sys.objects AS o
30         ON o.object_id = i.object_id
31    WHERE ps.avg_fragmentation_in_percent > 5
32      AND ps.page_count > 1000          -- ignore tiny indexes
33      AND i.name IS NOT NULL;           -- skip heaps
34
35OPEN idx_cursor;
36FETCH NEXT FROM idx_cursor INTO @sql;
37WHILE @@FETCH_STATUS = 0
38BEGIN
39    EXEC sys.sp_executesql @sql;
40    FETCH NEXT FROM idx_cursor INTO @sql;
41END
42CLOSE idx_cursor;
43DEALLOCATE idx_cursor;

The deprecated DBREINDEX syntax

DBCC DBREINDEX (table, index, fillfactor) takes the table name, an index name (empty string means all indexes), and a fill factor. It always rebuilds offline, taking a table lock for the duration, and it has no concept of partitions or a reorganize-only mode. Its arguments are positional and undocumented in modern guidance precisely because the command is on the way out.

ALTER INDEX REBUILD options

ALTER INDEX ... REBUILD accepts a WITH clause that DBREINDEX never had: ONLINE = ON keeps the index available during the rebuild (with a brief lock at start and finish), FILLFACTOR controls leaf-page density, SORT_IN_TEMPDB = ON offloads the sort, and REBUILD PARTITION = n rebuilds a single partition of a partitioned index. You can also rebuild ALL indexes or name one specifically.

REORGANIZE vs REBUILD thresholds

The widely-used guidance is fragmentation-driven: below roughly 5% leave the index alone, between 5% and 30% REORGANIZE (always online, fully logged, interruptible without losing work), and above 30% REBUILD. REORGANIZE compacts and reorders leaf pages in place; REBUILD drops and recreates the index, which also refreshes its statistics with a full scan as a side effect. The cursor encodes exactly this rule.

Migration

The mapping is direct: DBCC DBREINDEX('t','',f) becomes ALTER INDEX ALL ON t REBUILD WITH (FILLFACTOR = f), and a single-index DBREINDEX('t','ix',f) becomes ALTER INDEX ix ON t REBUILD WITH (FILLFACTOR = f). Add ONLINE = ON where the edition supports it, and replace blanket rebuilds with the threshold cursor so you stop rebuilding indexes that are barely fragmented.

Key Benefits and Use Cases

  • Online availability — ONLINE = ON keeps tables readable and writable during a rebuild, something DBREINDEX could never do.
  • Partition granularity — rebuild or reorganize a single partition instead of the entire index, slashing maintenance time on large tables.
  • REORGANIZE option — a lighter, always-online, interruptible operation for moderately fragmented indexes.
  • Supported going forward — ALTER INDEX receives every new option; DBREINDEX is frozen and slated for removal.
  • Resumable rebuilds — modern versions support RESUMABLE = ON, letting a long rebuild pause and continue.
  • Threshold-driven efficiency — skip indexes that do not need work, cutting maintenance windows and log generation.

Performance Considerations

  • ONLINE has prerequisites: on versions before 2019 online rebuild is Enterprise-only, and it needs extra space plus tempdb for the row-versioning shadow copy.
  • REBUILD refreshes statistics: a rebuild updates the index's statistics with a full scan, so a separate UPDATE STATISTICS on that index afterward is redundant.
  • REORGANIZE is always online: it is fully logged and can be stopped midway without rolling back completed work.
  • Log growth in FULL recovery: rebuilds are heavily logged; under FULL recovery they can balloon the transaction log and lengthen the next backup.
  • Locking differences: offline rebuilds take restrictive locks for the whole operation; online rebuilds take only brief locks at the boundaries.

Practical Tips

  • Replace DBREINDEX in legacy jobs — migrate now, on your schedule, rather than during the upgrade that finally removes the command.
  • Use a threshold-based script — drive maintenance from sys.dm_db_index_physical_stats (or an open-source maintenance solution) instead of rebuilding everything nightly.
  • Don't rebuild low-fragmentation indexes — under ~5% fragmentation, maintenance costs more than it returns.
  • Set FILLFACTOR thoughtfully — too low wastes space and inflates reads; leave volatile indexes some headroom and static ones near 100.
  • Skip the redundant stats update — because REBUILD already fullscans, do not follow it with a manual UPDATE STATISTICS on the same index.

Conclusion

DBCC DBREINDEX still runs, but it is living on borrowed time and lacks every capability that makes modern index maintenance safe and efficient. Migrating to ALTER INDEX REBUILD/REORGANIZE is a low-risk afternoon that buys online operations, partition control, and a future-proof maintenance path. For the full toolkit, see the complete DBCC command reference.

References

Posts in this series