Enabling Sort in Tempdb option for indexes

You can direct the SQL Server Database Engine to use Tempdb to store the intermediate sort results that are used to build indexes by enabling the Sort in Tempdb option when you create or rebuild an 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.

Before using this option it is recommended that you:

  • Increase the size of Tempdb.

  • Add database files in Tempdb to prevent bottleneck when many operations want to allocate space.

Note: 

Sort in Tempdb will only take effect whenever Index is recreated or rebuilt during extraction.

To check the speed rate using ETL Statistics dialog, right-click the object and select Statistics.

The following steps are necessary to enable Sort in Tempdb:

Enabling Sort in Tempdb option per Index

  1. Right-click any DWD object where indexing is applicable.
  2. Access Detailed Properties > Indexes.
  3. Select an Index in Selected Index list where Sort in Tempdb will be enabled.
  4. Select Sort in Tempdb checkbox in Index Properties.
  5. Click OK.