Question: When to Use sort_in_tempdb for Rebuilding Indexes?
Answer: This question was received during one of the recent Comprehensive Database Performance Health Check consulting engagement. One of the last thing which we did during the consulting was to set up the rebuilding indexes job. While we set this one up, one of the DBA asked this question.
Here is the answer in very simple possible words – you should use sort_in_tempdb option for indexes when you are TempDB on the drives which are extremely fast and are different from your MDF and LDF drives.
If you have your data and log files on the same drive as on the tempDB, I have personally not seen much improvement in using this option. You can also use this feature if you are running out of the space on your data drive and you must to rebuild an index.
Here is the script which demonstrates how you can use 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.
When we rebuild our indexes, it uses lots of buffer memory due to a requirement of sorting. When buffer memory is filled up, the index has to use a disk to store the database. If you have your TempDB on a separate disk drive, by enabling the soft in TempDB, you effectively reduce the disk pressure from our database disk and move it to TempDB.
Reference: Pinal Dave (https://blog.sqlauthority.com)
