SQL Server Rebuild All Indexes On A Table

by

In this article, I’m going to walk you through the “why,” “when,” and “how” of rebuilding indexes. We’ll cover the T-SQL commands you need, the difference between a Reorganize and a Rebuild, and the best practices.

SQL Server Rebuild All Indexes On A Table

Understanding Fragmentation: Why Rebuild at All?

Before we get into the code, we need to talk about the enemy: Fragmentation.

In SQL Server, data is stored in 8KB pages. When you insert, update, or delete data, these pages can become out of order or leave behind large gaps of empty space. This is known as logical fragmentation. Imagine a library where books are shelved randomly rather than by call number—finding anything takes twice as long because the librarian (or the SQL Storage Engine) has to jump all over the place.

When you rebuild an index, you are essentially dropping the old, messy structure and recreating it from scratch. This compresses the data, removes the gaps, and physically reorders the pages to match the logical order of the index.

The Impact of Fragmented Indexes

  • Increased I/O: SQL Server has to read more pages to get the same amount of data.
  • Wasted Disk Space: Pages that are only 50% full take up twice the space they should.
  • Slower Backups: Larger physical files mean longer maintenance windows.
  • Poor Query Performance: Your SELECT statements start dragging, and your users start complaining.

How to Rebuild All Indexes: The T-SQL Way

1. The “Nuclear” Option: ALL

The most efficient way to handle a specific table is the ALTER INDEX command with the ALL keyword. This tells SQL Server to target every clustered and non-clustered index on that specific object.

SQL

-- Rebuilding all indexes on the dbo.Orders table
ALTER INDEX ALL ON dbo.Orders
REBUILD;
GO

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Rebuild All Indexes On A Table

2. The Online Rebuild (The “Gold Standard”)

If you are working in a 24/7 environment—like a hospital in Chicago or an e-commerce hub in Seattle—you cannot afford to lock your tables. If you have the Enterprise Edition or are using Azure SQL Database, always use the ONLINE = ON clause.

SQL

ALTER INDEX ALL ON Sales.Orders
REBUILD WITH (ONLINE = ON);
GO

Note: This allows users to continue reading and writing to the table while the rebuild is happening in the background.

3. Adding Extra Muscle: MAXDOP and FILLFACTOR

Sometimes you need the rebuild to finish yesterday. You can specify the degree of parallelism (MAXDOP). Also, if your table is “insert-heavy,” you might want to leave some “breathing room” on the pages using a FILLFACTOR.

SQL

ALTER INDEX ALL ON Sales.Orders
REBUILD WITH (
    FILLFACTOR = 80, 
    MAXDOP = 4, 
    ONLINE = ON
);
GO

In this example, we are leaving 20% of each page empty to accommodate future growth, reducing the chance of immediate fragmentation.

Step-by-Step Tutorial: Identifying and Rebuilding

If I were sitting next to you at your desk right now, this is the workflow I would teach you. We don’t just guess which tables need help; we use data.

Step 1: Check the Fragmentation Levels

Use the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats. This is the “X-ray” for your database.

SQL

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.tables t ON ips.object_id = t.object_id
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

Step 2: Evaluate the Table Size

Before you hit “Execute” on a rebuild, check the table size. Rebuilding a 500GB table during business hours is a great way to get a call from your CTO at 2:00 PM on a Tuesday. Use sp_spaceused to get a feel for the volume.

Step 3: Execute the Rebuild

Once you’ve confirmed the fragmentation is high (over 30%), run your ALTER INDEX ALL command.

Step 4: Verify

Run the script from Step 1 again. Your avg_fragmentation_in_percent should now be close to zero.

The “Pro” Checklist: What to Watch Out For

  • Transaction Log Growth: A rebuild is a massive operation. If you are rebuilding a 100GB table, your transaction log needs at least 100GB of free space to track the changes. If you run out of disk space, the operation rolls back, and you’ve wasted hours.
  • TempDB Pressure: SQL Server uses TempDB to sort the data during a rebuild if you specify SORT_IN_TEMPDB = ON. Ensure your TempDB is on fast storage (NVMe/SSD) and has room to grow.
  • Locking and Blocking: Even an ONLINE rebuild requires a very short “Shared” lock at the beginning and a “Schema Modification” lock at the very end. On a very busy system, the rebuild might “wait” for a long-running transaction to finish, causing a bottleneck.

Best Practices

  1. Don’t Rebuild Small Tables: If a table is under 1,000 pages, SQL Server might not even bother with a perfect index structure. Fragmentation on tiny tables rarely impacts performance.
  2. Automate with Ola Hallengren: Don’t write your own loops from scratch. The industry standard is the Maintenance Solution by Ola Hallengren. It’s used by almost every major US corporation. It intelligently decides whether to Rebuild or Reorganize based on thresholds.
  3. Schedule for Low Traffic: Even with ONLINE = ON, there is a CPU overhead. Schedule your rebuilds for 2:00 AM EST (or whenever your specific region’s traffic dips).
  4. Update Statistics: A rebuild automatically updates statistics with a full scan. This is a huge “hidden” benefit because it helps the Query Optimizer choose better execution plans.

Common Questions (FAQs)

Can I rebuild indexes on a table with a Clustered Columnstore Index?

Yes, but the syntax is slightly different. For Columnstore, you usually use REORGANIZE to compress rowgroups, but a REBUILD will completely re-compress the data into the most efficient format.

Does ALTER INDEX ALL affect the primary key?

Yes. In SQL Server, the Primary Key is usually the Clustered Index. Rebuilding ALL will include the Primary Key.

What happens if I cancel a Rebuild halfway through?

If it’s an Offline Rebuild, it will roll back everything it did, which can take a long time. If it’s an Online Rebuild (in newer versions of SQL), it might be resumable, but generally, you want to let it finish.

Final Thoughts

Rebuilding all indexes on a table is one of the most powerful tools. It’s the equivalent of a “tune-up” for your car—it keeps the engine running smoothly and prevents long-term damage. Just remember: Analyze the fragmentation first, check your log space, and always use Online operations if your license allows it.

You may also like the following articles: