前幾天在SQL PASS的講座中，聽到老師提到一個有趣的問題
heap
table在insert資料時，在某個條件下，即使檔案的某些page還有足夠的空間容納新增的資料，但系統不會寫進該page，而是新增一個page再將資料寫入新的page，條件是：
一、
該page現有資料所佔空間 > 50%（頁面空間）
二、
insert進來的資料長度 > 20%（頁面空間）
例如page現有資料量為51%，這時有筆資料要insert進來（資料量為page的21%），照理說這個page的剩餘空間是可以容納這筆資料的；但是，SQL SERVER卻是又配置一個新的page來存放新insert的資料！
這會造成空間的浪費，從大面向來看你會發現total
insert進來的資料假設只有1G，但或許資料檔（mdf/ndf）卻成長了2G，這會令人疑惑；也會造成內部碎片。
因時間有限，老師有提到heap table的空間分配是由PFS控管，沒有辦法再細說原因；我覺得這個問題蠻有趣的，我想在此依個人瞭解針對這個問題再做個測試及筆記。
PFS（Page Free
Space）
PFS是一個系統的頁面（page）,它主要用來紀錄：
一、各個頁面的型態（例如是否為混合頁、IAM頁）
二、各個頁面是否閒置（閒置頁面表示可用，可以重分配）
三、各個頁面的空間使用率
PFS也是一個8K的page，扣除page header及meta
data後，有8088個bytes可以用來表示相對page的狀態；亦即一個PFS，可以管理8088個page，在資料檔（mdf/ndf）中每隔8088頁就會有一個PFS來管理。
PFS可用來表示page的「大約已使用空間」以及page的種類、page是否已分配給TABLE等。從PFS的設計來看，它只將page空間的使用情況分成5等級：
0%、50%、80%、95%、100%
所以，假設我們有一個空的TABLE，塞入一筆只有幾個byte的資料(例如只佔不到1%的空間)，在PFS中，它會標示這個page的空間已被使用50%。
測試驗證(1)
我們做個實驗來確認是否如此：
create table
HeapSpaceTest (ID varchar(8000)) --建立一個TABLE，只有一個varchar欄位
go
insert into
HeapSpaceTest values('A') --新增一筆資料，只有一個byte
go
建立一個TABLE，並insert一筆資料，這筆資料內容只有1個byte，我們先使用DMV查詢頁面的真實使用空間
SELECT object_name(object_id) as objname,
page_count,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('HeapSpaceTest'), NULL, NULL, 'DETAILED')
我們可以看到頁面真實的使用空間僅有0.14%。
接著我們看看PFS的紀錄
先用dbcc ind查出該TABLE的資料頁面編號為1:536
從上面的截圖可以看到，PFS紀錄1:536頁的頁面資料使用率為50%。
這是因為PFS只將頁面使用率分為5個等級(如上面PFS結構圖)。以這個例子來說，頁面真實的使用率為0.14%，落在>0-50之間，這個級距在PFS中紀錄它的頁面使用率都將為50%（這個差很大啊..>_<||.. 真實的資料佔比僅0.14%，但PFS卻紀錄為50%）。
至此我們驗證了PFS所紀錄的頁面空間使用率只分為5個級距，與真實的頁面空間使用率有不小的落差。
Heap table的資料新增
vs PFS
一筆資料的真實長度
= 資料內容長度 + internal overhead + slot offset
Internal overhead是指描述這筆資料的資料，例如這筆資料有幾個欄位、哪個欄位是null…等，它的長度不固定，其長度視資料有多少欄位而不同，至少有7個 bytes，欄位越多則Internal overhead會越長。至於slot offset則是固定為2 bytes。
Heap table在實體資料儲存上不需要考慮排序的問題。換句話說SQL SERVER不需要考慮這筆資料要寫到哪個頁面；只要哪個頁面有空間就都可以寫。
那麼，要如何找到有足夠空間的page呢？..首先SQL SERVER掃瞄該TABLE的IAM頁取得該TABLE的所有頁面編號，接下來有下列兩種方法，來確定哪個頁面的空間足夠容納資料：
一、 從頭開始去檢查每個page的page
header的m_FreeCnt，找到符合空間條件的page後即停止檢查。
二、 去PFS頁面檢查相關page的空間用量。
上面兩種尋找可用空間的方法，顯然第二種要有效率的多，它只要掃瞄PFS一個頁面即可。而第一種方法在極端的情況下(所有頁面都沒有足夠空間)，有可能會full table scan，造成I/O的負載。
因此，Heap table在insert資料時，SQL SERVER使用這筆資料的真實長度，再去PFS中依照其紀錄的空間使用率，就可以很快的確定哪個頁面可以容納這筆資料，並將資料寫入該頁面中。
PFS頁面上紀錄的是頁面使用率，所以SQL
SERVER在計算該頁面可用空間時，使用如下的公式：
頁面可用空間
= (1-PFS頁面使用率) × 8096
註：
經測試SQL SERVER在PFS計算可用空間是以8096為基底來算，它的由來是「頁面大小-頁面標頭 = 8192 – 96 = 8096」，但page中一筆資料的長度最長仍限制為8060，超過的話若有可變長度欄位則會另外以ROW_OVER_FLOW或BLOB方式存放。
以前面測試的這個例子來說：
PFS頁面使用率=50%
我們可以從PFS的角度算一下這個頁面可接受insert資料的空間
(1-0.0014)*8096=8084 (bytes) --頁面真實可用空間
(1-0.5)*8096=4048 (bytes) --從PFS角度來看的頁面可用空間
也就是說下一次insert進來的資料長度如果大於4048
Bytes，即使真實頁面空間足夠容納這筆資料，SQL
SERVER也會分配新的頁面，並將資料寫入新的頁面。這是因為SQL SERVER將參考PFS的空間使用率作為判斷要將資料寫到哪個page中而不會參考真實的頁面空間。
測試驗證(2)
我們以前面的例子繼續測試：
(1-0.0014)*8096=8084 (bytes) --頁面真實可用空間
(1-0.5)*8096=4048 (bytes) --從PFS角度來看的頁面可用空間
如果再insert一筆內容長度為4048 bytes的資料，再加上internal overhead，實際資料長度會略大於4048 bytes，也就是說真實頁面空間足以容納這筆資料，但從PFS角度來看頁面空間則是不足。依照上一節所說的，SQL SERVER應該會分配一個新的頁面並將資料寫入新頁面中。
--再新增一筆資料，長度為 4048 bytes
insert into
HeapSpaceTest values(replicate('A',4048))
新增後，用DMV檢查頁面
果然系統多分配了一個頁面，用來存儲新insert的資料，我們再用dbcc ind看一下，確實多了一個1:537頁面
從上面的簡單測試，我們了解到insert資料到Heap table時，SQL SERVER是透過PFS去尋找空間合適的頁面而不考慮真實的頁面空間。
回顧
heap
table在insert資料時，在某個條件下，即使檔案的某些page還有足夠的空間容納新增的資料，但系統不會寫進該page，而是新增一個page再將資料寫入新的page，條件是：
一、 該page現有資料所佔空間
> 50%（頁面空間）
二、
insert進來的資料長度 > 20%（頁面空間）
這種狀況可用下面的圖來表示
結論
Heap table的資料存放不需考慮資料排序的問題，表示當有資料insert進來時，它可以被放在這個TABLE中任何有足夠空間的頁面；在這個條件下SQL SERVER將使用PFS去尋找哪個page有足夠的空間。這樣的尋找空間方式會比逐頁檢查空間要快的多。
所以，如果你設計的系統中，有TABLE要承接大量的insert資料的話，Heap table是個可以的選擇；但由於PFS只將空間分為5個級距，所以使用PFS去確認空間使用率會存在較大的偏差，故必然會造成較高的內部碎片以及空間的浪費，查詢時的I/O也會增加。
而Cluster table資料因為是有序的儲存，所以SQL SERVER本來就必需透過b-tree找到資料應該要存在哪個頁面上，才能做有序的儲存。因此它不能透過PFS去找，PFS只是紀錄頁面的空間，它無法知道資料的順序。
寫到這裡突然想到，Heap table就好像高鐵自由座，只要有空位都可以坐；而Cluster table則像是對號座，不能隨便亂坐……..呵呵~~~^__^….
沒有留言:
張貼留言