When to Use sort_in_tempdb for Rebuilding Indexes? – Interview Question of the Week #207

Question: When should you use SORT_IN_TEMPDB for an index rebuild?

Answer: Consider it when tempdb has suitable capacity and a storage path that can handle the intermediate sort work efficiently. Test it against the default for the index and storage layout you actually have.

This came up near the end of a performance health check while we were discussing an index-maintenance job. A DBA asked where the rebuild’s sorting happens. My practical experience was that a separate, fast tempdb storage path could help; sharing the same busy physical storage often gave little improvement.

-- Review the intended index, space and maintenance window first.
ALTER INDEX [NameOfTheIndex] ON [SchemaName].[TableName]
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON);

With the option ON, SQL Server stores intermediate sort runs in tempdb when they need disk space. With it OFF, those runs use the destination filegroup. The finished index still belongs in the user database. If the sort is unnecessary or can stay entirely in memory, this option is ignored.

Two drive letters do not necessarily mean separate storage. On modern SSDs, virtual disks and shared storage, measure contention and throughput rather than assuming that a separate letter makes the rebuild faster. Allow enough tempdb space for sort runs and enough destination-filegroup space for the final index. Moving the work does not make that final space requirement disappear.

The option can help when sort workspace is putting pressure on the data drive, but it is not a substitute for capacity planning. It also does not make an offline rebuild online. SORT_IN_TEMPDB = ON is not supported for resumable index operations; choose a supported combination for the version, index and maintenance approach.

The command above retains the original rebuild example with placeholders. Substitute the intended index and table after reviewing the operation. The related Improve Index Rebuild Performance by Enabling Sort in TempDB post discusses the setting in more detail.

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