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.
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)
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?
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.