[SQL SERVER]了解制式範圍和混和範圍
由於SQL Server所有操作(order by、group by、hash join..)中繼暫存資料皆會使用到tempdb
但偏偏tempdb優化卻是很容易被忽略的一個環節，
今年我有一個系統上線，我觀察tempdb存在嚴重的資源競爭問題，
雖然我已經切 8 data files但還是無法大幅改善tempdb資源競爭問題，
這篇紀錄 trace flag 1118幫我帶來的改善，但我必須強調一點，
啟用trace flag 1118是經過我個人反覆測試且確定可改善目前我tempdb問題，
由於每個資料庫環境和問題皆不同，所以我這裡並不保證trace flag 1118就是一帖良藥，
當你要在正式環境啟用時，必須清楚知道是否有什麼副作用，總之凡事三思而後行。
SQL Server最小儲存單位為頁(1 page 8kb)，早期我認知SQL Server每次都會分配一個範圍(1 extents 8*8=64kb)，
該範圍就是8個連續實體頁面的集合，之前我以為範圍的8個頁面都是相同物件所使用，
但其實範圍有兩種類型，第一就是制式範圍(8個頁面都是相同物件所使用)，
另一種就是混合範圍(8個頁面可以由不同物件所使用)，
因為SQL Server並不會配置整個範圍給少量資料的資料表或索引，但當資料表或索引成長至有8個分頁後，
後面都會配置制式範圍，SQL Server這樣處理是為了更有效率利用空間。
了解SQL Server預設行為後，我們知道當建立temp table並新增少量資料時，通常會從混和範圍中配置兩種頁面為IAM和single data page，
這也表示SGAM和PFS都必須存取並變更，當大量建立少資料的temp table且多核心環境中，
那麼這兩個頁面就有可能發生嚴重的資源競爭，而刪除這些temp table其過程又會再上演一次，
基本上我們可以透過切割tempdb的data file來減少這樣的競爭，
再來就是啟用TF1118 變更SQL Server預設行為改為分配制式範圍，簡單說就是不在分配混和範圍，
這樣一來可確保相同物件資料都存在連續頁面，同時減少資源(latch)競爭問題，
但微軟KB提到TF1118只會影響tempdb，但真是這樣嗎?下面我來實際測試一下
use tempdb
go
create table mytmptbl (c1 int, c2 char(8000))
go
sp_spaceused 'mytmptbl'
go
沒有資料所以沒有分配任何頁面。
新增一筆資料會分配2總頁面
insert into mytmptbl values (1, 'rico')
go
sp_spaceused 'mytmptbl'
go
可以看到個別分配兩個頁面(data page and index page(IAM))，總保留16kb。
未超過8個頁面，都是個別頁面分配
insert into mytmptbl values (1, 'rico')
go 7
sp_spaceused 'mytmptbl'
go
16+(7*8)=72kb
新增第9筆資料
insert into mytmptbl values (1, 'rico')
go
sp_spaceused 'mytmptbl'
go
超過8頁面後都採取制式範圍(72+(8*8))=136kb
啟用TF1118變更預設行為
DBCC TRACEON (1118,-1)
GO
use tempdb
go
create table mytmptblB (c1 int, c2 char(8000))
go
Insert into mytmptblB values (1, 'rico')
go
sp_spaceused 'mytmptblB'
go
SQL Server直接分配一個制式範圍8+(8*8)=72kb
接下來測試使用者資料庫會有意外結果
use AdventureWorks2012
go
create table mytmptblB (c1 int, c2 char(8000))
go
Insert into mytmptblB values (1, 'rico')
go
sp_spaceused 'mytmptblB'
go
TF1118看來也是會影響使用者資料庫，而該flag在SQL2005~2014皆有效果。
ps:因我的系統大量使用temp table，當connection量大時(約200)，PAGELATCH_UP 都在等待PFS or SGAM資源。
參考
[SQL SERVER][Performance] tempdb 優化
[SQL SERVER][Memo] tempdb datafile該切多少份?
Understanding Pages and Extents