SQL SERVER – Two Advantages of Sort in TempDB Options

Just another day a client of Comprehensive Database Performance Health Check asked me why I suggest enabling Sort in TempDB Options for creating and rebuilding indexes. Let us discover two advantages of the SORT_IN_TEMPDB Option.

SQL SERVER - Two Advantages of Sort in TempDB Options sortintempdb-800x495

Faster Sort in TempDB

when any index is created or rebuilt, they need to sort the data in the order of the keys included in the index definition. If you have a TempDB on the different physical drives, when you have enabled this sorting of the data in the TempDB,  you will notice significant performance improvement over the traditional sorting in the same filegroup where the Index is located.

Size of Log File

One of the biggest complaints I have received from my clients who have a table ranging in the 500GB plus in size is that whenever they try to build the index, their log file grows significantly and they often started to face space crunch. This is common when tables are huge and sorting happens in the same filegroup as the index. When you enable the sort in the TempDB option the size of the TempDB increases but this helps to keep the size of the log under control for your user database.

Additionally, space which is used in the TempDB gets reused by other tables and database on the same instance.

Well, these are the primary two reasons, I suggest that users should enable sort in TempDB option.

Here is the script which demonstrates how you can use the sort_in_tempdb option.

ALTER INDEX [NameOfTheIndex] ON [SchemaName].[TableName]
REBUILD PARTITION = ALL WITH (SORT_IN_TEMPDB = ON)
GO

If you want to learn more about this subject you can read here: SQL SERVER – Improve Index Rebuild Performance by Enabling Sort In TempDB. You can also watch my SQL in Sixty Seconds video for further learning about SQL Server Performance Tuning.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts, SQL Server, SQL TempDB
Previous Post
SQL SERVER – Compression Delay for Columnstore Index
Next Post
SQL SERVER – Finding Fragmentation in Forwarded Records

Related Posts

2 Comments. Leave new

  • Does this advice apply in Azure SQL Database, where tempdb size is not under our control? If large index rebuilds cause tempdb to hit a size limit, what happens?

    Reply
  • Any thoughts on enabling this rebuild option when rebuilding big indexes in Azure SQL Managed Instance. There will be more IO I understand with sort in tempdb but will more be in TempDB. With IOPS and throughput limitations on file size, then could enabling this put some of that IO on tempdb thus not maxing out IO of the user database. Am finding index rebuilds of large 1GB+ indexes slow.

    Reply

Leave a Reply