[SQL Server]善用sort_in_tempdb

使用該選項時,請注意你的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]了解制式範圍和混和範圍

[SQL SERVER][Performance] tempdb 優化

[SQL SERVER][Memo] tempdb datafile該切多少份?

[SQL SERVER]降低tempdb競爭

下面我簡單示範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]造成交易記錄檔案暴增的常見原因

CREATE INDEX Monitoring Progress

Write-Ahead Transaction Log

SQL Server Transaction Log Architecture and Management Guid