0

I am familiar with the indexing concepts from developer's perspective but new to creating and maintaining indexes. Most of our indexes are created with SORT_IN_TEMPDB=On option, which tells SQL Server to use tempdb for the sorting operations involved during the indexing.

Using the tempdb for indexing should speed up the sorting and reduce some IO load from the user database by using tempdb resources instead.

I recently run into an index definition which had SORT_IN_TEMPDB=Off, which tells the SQL server to sort in the user database.

Questions:

  1. Would not SORT_IN_TEMPDB=off slow down the user database because the sorting will be done using user database resources?
  2. What factors generally contribute to the decision of using SORT_IN_TEMPDB=off?
HappyTown
  • 6,036
  • 8
  • 38
  • 51
  • 1
    The [documentation](https://msdn.microsoft.com/en-us/library/ms188281.aspx) explains this on a quite detail level – James Z Feb 04 '17 at 07:34
  • 1
    If the doc doesn't answer you questions, post your question to http://dba.stackexchange.com/questions/tagged/sql-server. – Dan Guzman Feb 04 '17 at 11:17

0 Answers0