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)
