創建索引時可以指定 WITH SORT_IN_TEMPDB 選項
該選項指示數據引擎使用 tempdb 存儲用於生成索引的中間排序結果
雖然該選項會增加用於創建索引的磁盤空間量
但是當 tempdb 與用戶數據庫位於不同的磁盤集上時
該選項可減少創建索引所需的時間
數據庫引擎在生成索引時經歷下面兩個階段
首先
數據庫引擎掃描數據頁以檢索鍵值
並為每個數據行生成索引葉行
當內部排序緩沖區被葉索引項填滿時
這些項被排序並作為中間排序進程寫入磁盤
然後
數據庫引擎繼續掃描數據頁
直到排序緩沖區再次被填滿
這種先掃描多個數據頁
然後排序並寫入排序進程的模式繼續進行
直到處理完基表中的所有行
在聚集索引中
索引的葉行是表的數據行
因此中間排序進程包含所有的數據行
在非聚集索引中
葉行不包含非鍵列的值
因此通常較小
然而
如果索引鍵很大
則非聚集的排序進程也可能很大
數據庫引擎將排序的索引葉行進程合並為單個的排序流
引擎的排序合並組件從每個排序進程的第一頁開始
在所有的頁中找出最小的鍵
並將那個葉行傳遞到索引創建組件
然後處理下一個最小的鍵
隨後再處理下一個
依此類推
當將最後一個葉索引行從排序進程頁中析取出來時
該進程從此排序進程切換到下一頁
當處理完某個排序進程擴展盤區中的所有頁時
釋放該擴展盤區
每個葉索引行在傳遞到索引創建組件時
均被放置在緩沖區的葉索引頁中
每個葉頁在填充時被寫入
當寫入葉頁時
數據庫引擎還會生成該索引的上級
每個上級索引頁在填充時被寫入
如果在已經有非聚集索引的表上創建聚集索引
一般過程是
重新分配非聚集索引
但保留索引的定義
空間直到包含 CREATE INDEX 語句的事務結束時才可用
因而
如果在事務回滾期間必須恢復舊索引頁
則這些舊索引頁仍是可用的
創建聚集索引
重新創建非聚集索引
如果未指定 SORT_IN_TEMPDB
則排序進程將存儲在目的文件組中
在創建索引的第一階段
基表頁的交替讀取和排序進程的寫入使讀寫磁頭從磁盤的一個區域移動到另一個區域
當掃描數據頁時
磁頭位於數據頁區域
當填充排序緩沖區並且當前的排序進程必須寫入磁盤時
讀寫磁頭移動到某個可用空間區域
然後當繼續掃描表頁時移回數據頁區域
在第二階段
讀寫磁頭的移動頻率較高
這時
排序進程一般正在從各排序進程區域交替讀取
目的文件組中將生成排序進程和新的索引頁
這意味著數據庫引擎在各排序進程間分布讀取的同時
還必須定期跳到索引擴展盤區
以便在填充索引頁時寫入新的索引頁
如果指定了 SORT_IN_TEMPDB 選項並且 tempdb 與目的文件組位於不同的磁盤集上
那麼在第一階段
對數據頁的讀取與對 tempdb 中排序工作區的寫入會發生在不同的磁盤上
這意味著對數據鍵的磁盤讀取在整個磁盤上會趨於更加連續
並且對 tempdb 磁盤的寫入也趨於連續
正如生成最終索引時的寫入操作一樣
即使其他用戶正在使用數據庫並且正在訪問不同的磁盤地址
指定 SORT_IN_TEMPDB 選項時的總體讀寫模式的效率也比沒有指定時要高
SORT_IN_TEMPDB 選項可能會提高索引擴展盤區的鄰接
尤其當不是並行處理 CREATE INDEX 時
排序工作區擴展盤區在數據庫中的釋放位置方面有些隨機
如果排序工作區包含在目的文件組中
則釋放排序工作區擴展盤區時
可通過請求來獲取它們
以使擴展盤區在生成時容納索引結構
這在某種程度上使索引擴展盤區的位置隨機化
如果在 tempdb 中單獨容納排序擴展盤區
則它們的釋放順序與索引擴展盤區的位置無關
另外
當中間排序進程存儲在 tempdb 中而不是目的文件組中時
目的文件組中將有更多的可用空間
從而增加了索引擴展盤區鄰接的機會
SORT_IN_TEMPDB 選項只影響當前的語句
沒有任何元數據記錄索引是否存儲在 tempdb 中
例如
如果使用 SORT_IN_TEMPDB 選項創建了某個非聚集索引
後來在沒有指定該選項的情況下創建了某個聚集索引
則當數據庫引擎重新創建那個非聚集索引時
將不使用該選項
可用空間要求
如果指定了 SORT_IN_TEMPDB 選項
則 tempdb 中必須有足夠的可用空間容納中間排序進程
而且目的文件組中必須有足夠的可用空間容納新的索引
如果沒有足夠的可用空間
並且由於某種原因數據庫不能自動增長以獲得更多的空間（如磁盤上沒有剩余空間或關閉了自動增長功能）
CREATE INDEX 語句將失敗
如果沒有指定 SORT_IN_TEMPDB
目的文件組中的可用空間必須大約等於最終索引的大小
在第一階段
生成排序進程並要求可用空間量大約等於最終索引的大小
在第二階段
處理每個排序進程擴展盤區後將其釋放
這意味著釋放排序進程擴展盤區的速度與獲取擴展盤區以容納最終索引頁的速度差不多相同
因此總的空間要求並沒有顯著超過最終索引的大小
這樣的一個副作用就是如果可用空間量非常接近最終索引的大小
則數據庫引擎傾向於在排序進程擴展盤區釋放後立即重新使用它們
因為排序進程擴展盤區的釋放方式有些隨機
所以在這種情形中將降低索引擴展盤區的連續性
如果沒有指定 SORT_IN_TEMPDB
那麼如果目的文件組中有足夠的可用空間
則可以從鄰接的池而不是從剛剛重新分配的排序進程擴展盤區分配索引擴展盤區
這將提高索引擴展盤區的連續性
執行 CREATE INDEX 語句時
必須有足夠的可用空間
創建非聚集索引時
如果指定了 SORT_IN_TEMPDB
則 tempdb 中必須有足夠的可用空間存儲排序進程
而且目的文件組中必須有足夠的可用空間存儲最終索引結構
排序進程包含索引的葉行
如果沒有指定 SORT_IN_TEMPDB
目的文件組中必須有足夠的可用空間存儲最終索引結構
如果有更多的可用空間
則可以提高索引擴展盤區的連續性
在沒有非聚集索引的表上創建聚集索引時
如果指定了 SORT_IN_TEMPDB
則 tempdb 中必須有足夠的可用空間存儲排序進程
包括表的數據行
目的文件組中必須有足夠的可用空間存儲最終的索引結構
包括表的數據行和索引 B 樹
粗略的估計值是原始表大小的
倍
但可能需要根據不同的因素調整估計值
如鍵的大小很大或填充因子的值很低
如果沒有指定 SORT_IN_TEMPDB
則目的文件組中必須有足夠的可用空間存儲最終表
包括索引結構
如果有更多的可用空間
則可以提高表和索引擴展盤區的連續性
在有非聚集索引的表上創建聚集索引時
如果指定了 SORT_IN_TEMPDB
則 tempdb 中必須有足夠的可用空間存儲最大索引（一般為聚集索引）的排序進程集合
而且目的文件組中必須有足夠的空間存儲所有索引的最終結構
包括表的數據行所在的聚集索引
如果沒有指定 SORT_IN_TEMPDB
則目的文件組中必須有足夠的可用空間存儲最終表
包括所有索引結構
如果有更多的可用空間
則可以提高表和索引擴展盤區的連續性
From:http://tw.wingwit.com/Article/os/xtgl/201311/8838.html
