Room for an Index Build: Free Space and TempDB Placement

Room for an index build is the free space that your database and tempdb need before the build starts. The SORT_IN_TEMPDB option decides which of the two holds the sort workspace. The demo below checks both places, breaks a build on purpose, and shows what the option moves.

Two Places That Need Room for an Index Build

An index build touches two places. The finished index always lands in your database, so that database needs free space for the whole index. The sort runs are the sorted pieces that the build merges. They land in your database by default, and in tempdb when you write SORT_IN_TEMPDB = ON.

That gives the option two advantages. Your database needs less free space, because the runs leave. And when tempdb sits on other disks than your data files, the sort writes stop competing with the index writes. Both are documented, and both exist only when the sort spills to disk. A sort that fits its memory grant writes no runs.

Two other beliefs are wrong. The option does not shrink the transaction log, and it does not change the finished index. Sort in TempDB for Faster Index Rebuilds: When It Helps measures the log on a rebuild.

Build the Demo

The demo creates a database named BuildRoomDemo with one 64 MB data file. Autogrowth is off, so the file size is a hard limit. It loads 300,000 shipment rows into a table. Run it on a test server.

IF DB_ID(N'BuildRoomDemo') IS NULL CREATE DATABASE BuildRoomDemo;
GO
ALTER DATABASE BuildRoomDemo MODIFY FILE (NAME = N'BuildRoomDemo', SIZE = 64MB, FILEGROWTH = 0);
GO
USE BuildRoomDemo;
GO
DROP TABLE IF EXISTS dbo.Shipments;
CREATE TABLE dbo.Shipments (
    ShipmentID int NOT NULL CONSTRAINT PK_Shipments PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Note       char(100) NOT NULL DEFAULT 'x'
);
INSERT INTO dbo.Shipments (ShipmentID, CustomerID, Amount)
SELECT TOP (300000) n, n * 7919 % 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;

Check the Room for an Index Build First

The planned index holds CustomerID, Amount and Note, plus the clustering key. This query adds up the width of those four columns. It adds 11 bytes per row as a rough allowance for the row header, and multiplies by the row count. Then it reads the free space in tempdb and in your database.

DECLARE @rows bigint = (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.Shipments') AND index_id IN (0, 1));
DECLARE @bytes int = (SELECT SUM(max_length) FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Shipments') AND name IN (N'ShipmentID', N'CustomerID', N'Amount', N'Note'));
SELECT CAST(@rows * (@bytes + 11) / 1048576.0 AS decimal(10,2)) AS EstimateMB,
       (SELECT CAST(SUM(unallocated_extent_page_count) * 8 / 1024.0 AS decimal(12,2)) FROM tempdb.sys.dm_db_file_space_usage) AS TempdbFreeMB,
       (SELECT CAST(SUM(size - FILEPROPERTY(name, N'SpaceUsed')) / 128.0 AS decimal(12,2)) FROM sys.database_files WHERE type_desc = N'ROWS') AS UserFreeMB;

The index needs about 36.62 MB, and the database has 23.25 MB free. The check says the build will fail. Tempdb has plenty of room. Its free space changes all the time. The other values differ by a fraction of a megabyte per run.

What Happens When the Database Has No Room

Run the build with the option off, then on. The batches are separate, so the second statement runs after the first one fails.

CREATE INDEX IX_Shipments_Customer ON dbo.Shipments (CustomerID, Amount) INCLUDE (Note) WITH (SORT_IN_TEMPDB = OFF);
GO
CREATE INDEX IX_Shipments_Customer ON dbo.Shipments (CustomerID, Amount) INCLUDE (Note) WITH (SORT_IN_TEMPDB = ON);

Both statements print the same message.

Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'BuildRoomDemo' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated.

The option did not rescue the build. Tempdb can take the sort runs, but the finished index still needs its pages in BuildRoomDemo. The index filled this file, not the sort. The fix is more room in the database, not another option.

Add Room and Build With the Option

Grow the file to 100 MB and read the free space again.

ALTER DATABASE BuildRoomDemo MODIFY FILE (NAME = N'BuildRoomDemo', SIZE = 100MB);
GO
SELECT CAST(SUM(size - FILEPROPERTY(name, N'SpaceUsed')) / 128.0 AS decimal(12,2)) AS UserFreeMB FROM sys.database_files WHERE type_desc = N'ROWS';

Now 59.25 MB is free, more than the estimate. The next script builds the index with the option on. It reads the tempdb pages that the session has allocated before and after the build, and prints the difference.

DECLARE @before bigint = (SELECT internal_objects_alloc_page_count FROM tempdb.sys.dm_db_session_space_usage WHERE session_id = @@SPID);
CREATE INDEX IX_Shipments_Customer ON dbo.Shipments (CustomerID, Amount) INCLUDE (Note) WITH (SORT_IN_TEMPDB = ON);
SELECT internal_objects_alloc_page_count - @before AS TempdbPagesAllocated FROM tempdb.sys.dm_db_session_space_usage WHERE session_id = @@SPID;

The build worked, and it sent no page to tempdb. The sort stayed in memory, so there were no runs to move. This is why a small demo cannot show the two advantages. They are documented here, not measured.

SELECT name AS FileName,
       CAST(size / 128.0 AS decimal(10,2)) AS SizeMB,
       CAST(FILEPROPERTY(name, N'SpaceUsed') / 128.0 AS decimal(10,2)) AS UsedMB,
       CAST((size - FILEPROPERTY(name, N'SpaceUsed')) / 128.0 AS decimal(10,2)) AS FreeMB
FROM sys.database_files WHERE type_desc = N'ROWS';
SELECT CAST(SUM(reserved_page_count) * 8 / 1024.0 AS decimal(10,2)) AS IndexMB FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(N'dbo.Shipments') AND index_id = 2;

The index measures 36.40 MB, close to the estimate of 36.62 MB. The used space grew by about that much, from 40.75 MB to 77.13 MB.

A Rebuild Needs Room for Two Copies

A rebuild builds the new index before it drops the old one, so both exist for a while. With 22.88 MB free and a 36.40 MB index, the rebuild fails even with the option on.

ALTER INDEX IX_Shipments_Customer ON dbo.Shipments REBUILD WITH (SORT_IN_TEMPDB = ON);

It prints the same Msg 1101, State 12 as before. A reorganize needs no such room, but it rejects the option with Msg 155, because a reorganize does not sort. On a database that must stay small, plan the free space for a rebuild before you plan the sort.

Is Tempdb on Other Disks?

The second advantage depends on placement. This query counts the data files of tempdb and of the demo database on each volume.

SELECT DB_NAME(mf.database_id) AS DatabaseName, vs.volume_mount_point AS Volume, COUNT(*) AS DataFiles
FROM sys.master_files AS mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE mf.type_desc = N'ROWS' AND mf.database_id IN (DB_ID(N'BuildRoomDemo'), 2)
GROUP BY mf.database_id, vs.volume_mount_point;

Both sit on C:\ here, so this server cannot show the second advantage. Where tempdb has its own volume, the sort runs write to other disks than the index. Check this before you promise a speed gain.

Managed Instances and Azure

Readers asked about Azure SQL Database and Managed Instance. The room for an index build is the same two places there. The service tier limits the size of tempdb, so read the limit for your tier in the service documentation first. A build that needs more tempdb space than the limit allows fails.

Another reader asked about wear on solid state drives. The option moves the sort writes to another file, so the total written stays about the same. It changes where the wear lands. On a managed instance with tight limits on user files, moved writes can help. Time one build both ways.

What to Remember

Check the room for an index build in both places before you start. The finished index needs space in your database whatever the option says, and a rebuild needs space for two copies. The option moves only the sort runs, and only a sort that spills has runs to move.

The setting that changes the clock is a different one. Index Build Speed: Which Rebuild Settings Change the Time compares them. When you finish here, run the cleanup script. It removes the demo database.

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

A build is not done when the sort ends, it is done when every page has a home.

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