SQL Server 2012: SORT_IN_TEMPDB used to Improve the Index Rebuild Performance

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server 2012 enhanced the performance of INDEX REBUILD by introducing the SORT_IN_TEMPDB option.
I observed that many SQL developers are not using this feature or maybe they don’t know about it.

The INDEX REBUILD operation is removing the fragmentation of indexes which improves the query performance. It scans the data pages of base table to retrieve the key values and builds an index leaf row for each row.

This process requires internal sorting which it stores in the buffer area.

Once the buffer filled with leaf index entries, it stores the sorted result into a disk as an intermediate sort run. Next, It again starts with the same process for further data pages.

As per the MSDN,

When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index.

Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index.

When you are rebuilding the large size of Indexes, SORT_IN_TEMPDB helps you lot by reducing the operational execution time.

Sample Script with option SORT_IN_TEMPDB: