Use ONLINE and SORT_IN_TEMPDB Effectively

The ONLINE and SORT_IN_TEMPDB index options affect both the temporary space requirements and performance of the index create or rebuild operation. The advantages and disadvantages of each are covered in this section.
When considering the ONLINE option, you must weigh the need for a performant index operation versus the need for concurrent user access to the underlying data.
· To achieve the best performance, that is, the least time to create or rebuild an index, set ONLINE to OFF. However, this prevents all user access to the underlying table for the duration of the index create or rebuild operation.
· To achieve the best concurrency, that is, the least impact on other users accessing the table, set ONLINE to ON. However, the index operation will take more time.
You must also take into consideration the extra temporary space requirements of the online operation.
· To use the least amount temporary space while rebuilding a clustered index, set ONLINE to OFF.
· To use the least amount of temporary space while rebuilding a nonclustered index, set ONLINE to ON.
· If there are concurrent user transactions on the table during the online index operation, you must plan for additional space in tempdb for the version store.
For more information, see Determining the Amount of Temporary Space Used in this paper.
As we discussed earlier, when SORT_IN_TEMPDB is set to ON, sort runs and other intermediate tasks are stored in tempdb rather than the user database. Setting this option to ON can have two advantages:
· You can achieve the most contiguous space in the index. When the sort extents are held separately in tempdb, the sequence in which they are freed has no affect on the location of the index extents. Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. This increases the chance that index extents will be contiguous.

When both SORT_IN_TEMPDB and ONLINE are set to ON, the index transactions are stored in the tempdb transaction log, and the concurrent user transactions are stored in the transaction log of the user database. This allows you to truncate the transaction log of the user database during the index operation if needed. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space.