Index Build Speed: Which Rebuild Settings Change the Time

Index build speed depends on a choice and four settings, and only some of them change the time. The demo below rebuilds one index under each of them and measures the result. One setting changes the clock. One costs time for availability. One helps only a sort that spills, and one changes the log.

Build the Demo

The demo creates a database named IndexBuildSpeedDemo with a table of one million order rows and one nonclustered index. The index holds about 120 MB. The script sizes the data file and the log first, so file growth does not hide the timings. Run it on a test server, because the later scripts change the recovery model of the demo database.

IF DB_ID(N'IndexBuildSpeedDemo') IS NULL CREATE DATABASE IndexBuildSpeedDemo;
GO
ALTER DATABASE IndexBuildSpeedDemo MODIFY FILE (NAME = N'IndexBuildSpeedDemo', SIZE = 1200MB);
ALTER DATABASE IndexBuildSpeedDemo MODIFY FILE (NAME = N'IndexBuildSpeedDemo_log', SIZE = 600MB);
ALTER DATABASE IndexBuildSpeedDemo SET RECOVERY FULL;
GO
USE IndexBuildSpeedDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Note       char(100) NOT NULL DEFAULT 'x'
);
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (1000000) n % 50000, n % 997 / 10.0
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS t;
CREATE INDEX IX_Orders_Customer ON dbo.Orders (CustomerID, Amount) INCLUDE (Note);

Rebuild or Reorganize First

The fastest rebuild is the one you skip, so check the index first. The procedure below reports the fragmentation and the page count of the demo index. It uses the cheap LIMITED scan mode, which reads only the upper levels of the index.

CREATE OR ALTER PROCEDURE dbo.IndexShape
AS
SELECT i.name AS IndexName, CAST(s.avg_fragmentation_in_percent AS decimal(5,1)) AS FragPct, s.page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Orders'), 2, NULL, N'LIMITED') AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id;

Now the demo adds 200,000 rows with scattered customer numbers. New rows land in the middle of the index and split its pages.

INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (200000) (n * 7919) % 50000, 1
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS t;
EXEC dbo.IndexShape;

The index doubled in size and every page is out of order. Two commands can fix it. A reorganize moves pages one at a time, uses one processor, and works online. A rebuild creates the index again. Time the reorganize first.

DECLARE @start datetime2 = SYSDATETIME();
ALTER INDEX IX_Orders_Customer ON dbo.Orders REORGANIZE;
SELECT DATEDIFF(MILLISECOND, @start, SYSDATETIME()) AS ReorganizeMs;
EXEC dbo.IndexShape;

The reorganize needed about eleven seconds on this run. The next script scatters a similar set of rows again and then times a rebuild with four processors.

INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (200000) (n * 7919) % 50000, 1
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS t;
DECLARE @start datetime2 = SYSDATETIME();
ALTER INDEX IX_Orders_Customer ON dbo.Orders REBUILD WITH (MAXDOP = 4);
SELECT DATEDIFF(MILLISECOND, @start, SYSDATETIME()) AS RebuildMs;
EXEC dbo.IndexShape;

The rebuild needed about half a second here, more than ten times faster. A second server measured about seven times. A reorganize is lighter on locks and can be stopped, but it is not faster. For index build speed on a heavily fragmented index, the rebuild wins.

Setting One: MAXDOP

Index build speed grows with the number of processors, and the MAXDOP option sets how many a rebuild can use. When you leave it out, the instance setting decides, and on the test server that setting is 2. The next script rebuilds the index five times under each option. It prints the average and the fastest time in milliseconds. It sets the recovery model to SIMPLE first, so log writes do not hide the differences.

ALTER DATABASE IndexBuildSpeedDemo SET RECOVERY SIMPLE;
GO
DECLARE @res TABLE (Setting nvarchar(60), Ms int);
DECLARE @opts TABLE (n int IDENTITY(1,1), opt nvarchar(60));
INSERT @opts (opt) VALUES (N'MAXDOP = 1'), (N'MAXDOP = 4'), (N'MAXDOP = 8'), (N'MAXDOP = 8, SORT_IN_TEMPDB = ON'), (N'MAXDOP = 8, ONLINE = ON');
DECLARE @round int = 0, @i int, @opt nvarchar(60), @sql nvarchar(300), @start datetime2;
WHILE @round < 5
BEGIN
    SET @round += 1;
    SET @i = 0;
    WHILE @i < 5
    BEGIN
        SET @i += 1;
        SELECT @opt = opt FROM @opts WHERE n = @i;
        SET @sql = N'ALTER INDEX IX_Orders_Customer ON dbo.Orders REBUILD WITH (' + @opt + N');';
        SET @start = SYSDATETIME();
        EXEC (@sql);
        INSERT @res VALUES (@opt, DATEDIFF(MILLISECOND, @start, SYSDATETIME()));
    END;
END;
SELECT Setting, AVG(Ms) AS AvgMs, MIN(Ms) AS MinMs FROM @res GROUP BY Setting ORDER BY Setting;

The numbers come from one run on a server with 16 processors. Your milliseconds will differ, and other work on the server can hide the gaps. One processor needed two to three times as long as four processors. Four against eight is inside the noise of this run. More processors help only while the work is big enough to share. A parallel rebuild also takes CPU away from everything else.

Setting Two: ONLINE Costs Time

The ONLINE = ON option keeps the table readable and writable during the rebuild. Not every edition offers it, so check yours first. In the fastest runs the online rebuild took two to three times as long, 722 ms against 269 ms. Use it when users need the table at night. Skip it in a maintenance window when nobody does.

Setting Three: SORT_IN_TEMPDB

The option moves the sort workspace into tempdb. On this server the time did not change beyond the noise between runs, because the sort fits in memory. Tempdb needs room for the sort, about the size of the index leaf rows. The space comes from tempdb, and the log of your database does not shrink. The option helps when a large sort spills and tempdb sits on faster or separate disks.

Room for an Index Build: Free Space and TempDB Placement measures the room and the placement.

Setting Four: The Recovery Model

The recovery model changes the log more than the clock. In the full recovery model a rebuild logs every page it writes. In the bulk-logged and simple models it logs only the allocations. The next script switches the demo database through all three models and measures the log of one rebuild in each. The rollback removes the rebuild again.

DECLARE @log TABLE (RecoveryModel nvarchar(20), LogMB decimal(10,2));
DECLARE @models TABLE (n int IDENTITY(1,1), model nvarchar(20));
INSERT @models (model) VALUES (N'FULL'), (N'BULK_LOGGED'), (N'SIMPLE');
DECLARE @i int = 0, @model nvarchar(20), @sql nvarchar(200);
WHILE @i < 3
BEGIN
    SET @i += 1;
    SELECT @model = model FROM @models WHERE n = @i;
    SET @sql = N'ALTER DATABASE IndexBuildSpeedDemo SET RECOVERY ' + @model;
    EXEC (@sql);
    BEGIN TRANSACTION;
    ALTER INDEX IX_Orders_Customer ON dbo.Orders REBUILD WITH (MAXDOP = 1);
    INSERT @log SELECT @model, database_transaction_log_bytes_used / 1048576.0 FROM sys.dm_tran_database_transactions WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
    ROLLBACK TRANSACTION;
END;
SELECT RecoveryModel, LogMB FROM @log;

After the 400,000 extra rows, the index holds about 168 MB. The full model wrote 171 MB of log for it. The other two wrote under one megabyte. A big log is slow to write and to back up, and it can fill a disk. Switching a production database to bulk-logged changes what a log backup can restore. Plan that change with your backup schedule.

Is Build Time Worth the Effort?

You could argue that index build speed hardly matters, because the job runs at night. That holds until the window shrinks or the table triples in size. In the demo, four processors finished in less than half the time that one needed. I set MAXDOP for the maintenance job. I leave the other options at their defaults until a measurement asks for them.

What to Remember

Index build speed comes mostly from the choice of a rebuild and from MAXDOP. ONLINE costs time and buys availability. SORT_IN_TEMPDB helps only a sort that spills. The recovery model decides the log, not the clock. Time each option on your own table, because your disks and processors decide the result.

When you finish, run the cleanup script. It removes the demo database.

USE master;
GO
ALTER DATABASE IndexBuildSpeedDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IndexBuildSpeedDemo;

A faster rebuild is not a bigger server, it is the right setting for the job.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.