使用該選項時，請注意你的tempdb有足夠空間，並且也和User DB分開存放。
Database Engine建立索引時，一開始會先掃描base table’s data pages來擷取index key’s value，
並將每筆row拿來建立索引分葉資料列(index leaf rows)，這些中繼資料會先進入內部buffer，
如果內部buffer滿了，將會進行排序(依照index key邏輯排序且平行處理)並寫入disk，直到base table的所有資料列都處理完畢為止。
由於建立(or rebuild)index過程都隱含交易，所以會產生大量的紀錄寫入交易紀錄檔，
並讓交易紀錄檔案快速成長(可參考我以前文章[SQL SERVER][Maintain]造成交易記錄檔案暴增的常見原因)，
但建立index這些中繼排序操作，如果我們能移置tempdb來處理的話，我認為有2點好處
1增加索引範圍連續機會。
2減少我們建立(or rebuild)index的時間(雖然多CPU才是主要因素)和User DB的硬碟空間。
由於tempdb在處理交易紀錄，不像一般採取WAL(而是採取lazy)，
所以會比User DB來得比較有效率，但也有兩點壞處
1 tempdb空間需求增加
2 tempdb負擔變大，建議要優化tempdb。可以參考我對tempdb優化的幾篇文章
[SQL SERVER][Performance] tempdb 優化
[SQL SERVER][Memo] tempdb datafile該切多少份?
下面我簡單示範sort_in_tempdb=on來減少建立索引花費時間。
sort_in_tempdb=off
DBCC SQLPERF(logspace)
UserDB交易紀錄檔使用空間:0.81%
tempdb交易紀錄檔使用空間:4.59%
set statistics profile,time on
go
create index [idx2] on loopjointest
(
c3 ,
c4
)
with(sort_in_tempdb=off)
go
--tracking
select session_id, request_id, physical_operator_name, node_id,
thread_id, row_count, estimate_row_count
from sys.dm_exec_query_profiles with(nolock)
order by node_id desc, thread_id
CPU 時間 = 134171 ms，經過時間 = 129510 ms。
UserDB交易紀錄檔使用空間增為76.6%，tempdb依然維持4.59%。
接下來先checkpoint後，我們繼續測試Sort_in_tempdb=on
UserDB交易紀錄檔使用空間:0.67%。
CPU 時間 = 137125 ms，經過時間 = 121173 ms
UserDB交易紀錄檔使用增為75.3%(看來FULL模式下，無法省下太多交易紀錄空間)、Tempdb的交易紀錄檔使用增為65.15%。
結果:
使用Sort_in_tempdb=on，建立索引快8337 ms。
由於該選項預設off，因為tempdb只有一個，如果所有資料庫的中繼排序作業都移至tempdb，那可能會適得其反，
實務上，我可能會把大索引移至tempdb來縮短索引建置時間。
-- estimate_percent_complete
select
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count) as estimate_percent_complete
from sys.dm_exec_query_profiles with(nolock)
where session_id=54
group by node_id,physical_operator_name
order by node_id desc;
參考
SORT_IN_TEMPDB Option For Indexes
Configure the index create memory Server Configuration Option
[SQL SERVER][Maintain]造成交易記錄檔案暴增的常見原因