Disk Space Needed to Build a Clustered Index on a Big Table

The disk space needed to build a clustered index is not the size of the finished index. You need room for the old table, the new index, the sort work and the log, all at once. Plan for the peak, not for the end result.

Why the free space on the drive misleads you

Picture a junior DBA asking this before a weekend change: “The table is 200 GB and the drive has 250 GB free. We are fine, right?” I wish the answer were yes. Too often the build fails near the end, rolls back, and the drive is full again.

Here is why. To turn a heap into a clustered index, SQL Server reads every row, sorts it, and writes new index pages. Only after that does it drop the old heap. For a while you hold both copies. Every page written is also logged, and a rollback needs log room too.

Let me show you the overlap on a small table. The demo creates the SqlAuthorityDemo database and drops it at the end.

Build a small table you can measure

The table has 100,000 rows with a 200-byte payload, and one nonclustered index on CustomerId. Pages are 8 KB each.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.BuildDemo (Id int NOT NULL, CustomerId int NOT NULL, Payload char(200) NOT NULL);

INSERT dbo.BuildDemo (Id, CustomerId, Payload)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       ABS(CHECKSUM(NEWID())) % 5000, 'x'
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE NONCLUSTERED INDEX IX_BuildDemo_Customer ON dbo.BuildDemo (CustomerId);

Now measure. The first result lists each structure. The heap is index_id 0 and uses 2,704 pages, about 21 MB. The nonclustered index uses 225. The second result counts the pages in use in the whole data file: 3,408.

SELECT i.index_id, i.name, SUM(p.used_page_count) AS used_pages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.BuildDemo')
GROUP BY i.index_id, i.name
ORDER BY i.index_id;

SELECT FILEPROPERTY(N'SqlAuthorityDemo', 'SpaceUsed') AS pages_in_use,
       size AS file_pages
FROM sys.database_files
WHERE type = 0;

Watch the space double during the build

I build the index inside an open transaction so we can look at the middle of the work. Do not hold a transaction like this open on a production server. Here it only freezes the picture.

BEGIN TRANSACTION;

CREATE UNIQUE CLUSTERED INDEX CX_BuildDemo ON dbo.BuildDemo (Id) WITH (SORT_IN_TEMPDB = ON);

SELECT FILEPROPERTY(N'SqlAuthorityDemo', 'SpaceUsed') AS pages_in_use_mid_build;

SELECT CAST(database_transaction_log_bytes_used / 1048576.0 AS decimal(10, 1)) AS log_used_mb
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID()
  AND transaction_id = (SELECT transaction_id FROM sys.dm_tran_current_transaction);

COMMIT TRANSACTION;

Pages in use jumped from 3,408 to 6,088. That is almost double, for a table that holds the same rows. The old heap and the new index sit side by side.

The log used 22.9 MB, a little more than the table itself. My demo database uses FULL recovery. Your recovery model changes that number, so measure it on your own server.

The space comes back, but not at once

Right after the commit, the old heap is still allocated. SQL Server frees it in the background. The loop waits until the usage drops, then prints the final numbers.

DECLARE @Tries int = 0;
WHILE @Tries < 30 AND FILEPROPERTY(N'SqlAuthorityDemo', 'SpaceUsed') > 4500
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @Tries += 1;
END;

SELECT FILEPROPERTY(N'SqlAuthorityDemo', 'SpaceUsed') AS pages_in_use_after;

SELECT i.index_id, i.name, SUM(p.used_page_count) AS used_pages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.BuildDemo')
GROUP BY i.index_id, i.name
ORDER BY i.index_id;

Usage is back to 3,384 pages, close to where we started. The clustered index uses 2,710 pages. The nonclustered index shrank from 225 to 176 pages. It used to point to rows with an 8-byte row id. Now it points with the 4-byte clustered key. A wide clustered key does the opposite and makes every other index bigger.

What to check before your change window

First, look for free space inside the data file. Unused space in the file serves the build before the file has to grow. The volume’s free space only matters for growth. Several files can share one volume, so do not count the same free gigabytes twice. Check the growth settings and MAXSIZE too.

Second, remember tempdb. SORT_IN_TEMPDB moves the sort work there, but the finished index still lands in its own filegroup. Tempdb needs its own room.

Third, rehearse on a restored copy and watch the file and the log while it runs. A page count taken afterward cannot tell you the peak. I avoid any magic multiplier, because row width, compression and other work on the server change the answer. Keep a reserve and decide in advance when you will stop the build.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Give your next index build a bit more room than you think it needs.

Build space is not the final size, it is the peak of the work.

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