Sort in TempDB for Faster Index Rebuilds: When It Helps

Sort in tempdb is an option on index builds that moves the sort workspace out of your database. The syntax is one clause, and the effect is narrower than many people expect.

What the Option Does

Building or rebuilding an index needs a sort. SQL Server reads the rows, puts them in key order, and writes the index pages. The sort needs working space, and by default that space comes from the same database that holds the index.

With SORT_IN_TEMPDB set to ON, the sort workspace goes to tempdb instead. The finished index is still built in your database, and the work of writing it stays there. Sort in tempdb moves only the intermediate sort runs, and the finished index is still built in your database.

The syntax is ALTER INDEX name ON table REBUILD WITH (SORT_IN_TEMPDB = ON). That clause is the whole change. It also works with CREATE INDEX, but REORGANIZE doesn’t use it, because a reorganize doesn’t sort the index again. The demo below creates a table and an index to try it on.

Build the Demo

The demo creates a database named RebuildSortDemo in the FULL recovery model. It fills a sales table with 200,000 rows and adds a nonclustered index. A full backup follows, so the log behaves like a production log. Change the backup folder to one that exists on your test server.

IF DB_ID(N'RebuildSortDemo') IS NULL CREATE DATABASE RebuildSortDemo;
GO
ALTER DATABASE RebuildSortDemo SET RECOVERY FULL;
GO
USE RebuildSortDemo;
GO
DROP TABLE IF EXISTS dbo.Sales;
CREATE TABLE dbo.Sales (SaleID int IDENTITY(1,1) NOT NULL PRIMARY KEY, CustomerID int NOT NULL, Amount decimal(10,2) NOT NULL, Note char(100) NOT NULL DEFAULT 'x');
INSERT INTO dbo.Sales (CustomerID, Amount)
SELECT TOP (200000) ABS(CHECKSUM(NEWID())) % 50000, ABS(CHECKSUM(NEWID())) % 10000 / 100.0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c;
CREATE INDEX IX_Sales_Customer ON dbo.Sales (CustomerID, Amount) INCLUDE (Note);
GO
BACKUP DATABASE RebuildSortDemo TO DISK = N'D:\data\RebuildSortDemo.bak' WITH INIT;

Does the Log Shrink?

Readers who test the option see the same log growth with it on and off. The test below confirms it. Each rebuild runs inside a transaction, so the query can read how much log the transaction wrote. The rollback then undoes the rebuild.

BEGIN TRANSACTION;
ALTER INDEX IX_Sales_Customer ON dbo.Sales REBUILD WITH (SORT_IN_TEMPDB = OFF);
SELECT N'OFF' AS SortInTempdb, CAST(database_transaction_log_bytes_used / 1048576.0 AS decimal(10,2)) AS LogMB
FROM sys.dm_tran_database_transactions
WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
ROLLBACK TRANSACTION;

Run the same test with the option on.

BEGIN TRANSACTION;
ALTER INDEX IX_Sales_Customer ON dbo.Sales REBUILD WITH (SORT_IN_TEMPDB = ON);
SELECT N'ON' AS SortInTempdb, CAST(database_transaction_log_bytes_used / 1048576.0 AS decimal(10,2)) AS LogMB
FROM sys.dm_tran_database_transactions
WHERE transaction_id = CURRENT_TRANSACTION_ID() AND database_id = DB_ID();
ROLLBACK TRANSACTION;

The two numbers are identical. The log records the index pages that the rebuild writes. Those pages go into your database whatever the sort does. If a rebuild fills your log, this option won’t help. Frequent log backups, or the bulk-logged recovery model, address that problem.

Where the Option Helps

The option helps in two situations. The first is a sort that is too big for memory. The runs are then written to disk. Tempdb on a faster or separate disk set can take that I/O off your data files. The second is a user database with little free space. The sort workspace no longer competes with the index for room.

In the demo, the sort stays in memory. There is nothing to spill to tempdb, so there is no reason to expect a gain. The demo measures the log, not the speed. The option does little for a small index on a server with plenty of memory.

Check Tempdb Before You Turn It On

The option shifts space use to tempdb. It needs room of up to the size of the index being built. If tempdb runs out of space, the rebuild fails. The query only reads the free space in tempdb.

USE tempdb;
GO
SELECT CAST(SUM(unallocated_extent_page_count) * 8 / 1024.0 AS decimal(12,2)) AS FreeTempdbMB
FROM sys.dm_db_file_space_usage;

Compare the result with the size of the largest index you rebuild. The value changes all the time, so check it near the time of the job.

Should It Always Be On?

You could argue that the option should always be on. Tempdb is built for scratch work, after all. That is a fair view when tempdb has its own fast storage and plenty of room.

If tempdb shares a slow disk with your data, the option moves the pressure and doesn’t remove it. A busy tempdb makes it worse. That’s why I treat it as a measured choice. Rebuild one large index with each setting, watch the elapsed time and the free space, and keep the faster setting.

Measure with a real rebuild. Record the elapsed time, the tempdb space and the log growth for each setting, on a copy of production data. Run each setting twice, because the first run warms the cache.

What to Remember

Sort in TempDB moves only the sort workspace. It doesn’t shrink the log, and it doesn’t speed up a sort that fits in memory. Check the free space in tempdb first, and time the rebuild both ways. Remove the demo and its backup file when you finish.

USE master;
GO
ALTER DATABASE RebuildSortDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE RebuildSortDemo;
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'RebuildSortDemo';

The last statement removes the demo’s backup history from msdb. The backup file stays on disk. The next block is PowerShell, not T-SQL, and it deletes exactly that file.

Remove-Item 'D:\data\RebuildSortDemo.bak'

A sort workspace is not an index, it is the scratch paper, and moving it doesn’t change the answer.

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