常常聽說,也常常看到書上這樣寫,一個Page有8K,即8192 Bytes,而能夠用來儲存資料的空間為8060 Bytes,這是因為必須扣除Page Header占用的96 Bytes,以及Page尾端的Row Offset所保留使用的36 Bytes(此數據可能會因record的數量而有所變動),所以8192 Bytes - 96 Bytes - 36 Bytes=8060 Bytes,一個簡單Page Structure的示意圖如下:
當使用DBCC PAGE指令來檢視Page中的內容時,會發現到一個欄位「 m_freeCnt 」欄位,它代表的是該Page的剩餘空間(注意: 不是指該Page可以用來存儲record的剩餘空間),來看看它是如何被計算出來的,以下指令用來建立用來測試用的database、table及insert data,為了極度簡化以方便計算及說明,以下的資料表都是使用固定長度的欄位資料型別,所以每一筆insert的資料,在Page中都是一樣的長度,而且我們也不建立Primary Key及index,所以此例是一個很簡單的堆積(Heap)資料表。
CREATE DATABASE myDB
GO
USE myDB
GO
CREATE TABLE tb_PageFree
(
FirstName CHAR(200),
LastName CHAR(300),
Email CHAR(200),
BirthDay DATE --需使用3個Bytes
)
GO
INSERT INTO tb_PageFree VALUES('F1','L1','F1.L1@yahoo.com','1983-01-01');
INSERT INTO tb_PageFree VALUES('F2','L2','F2.L2@yahoo.com','1984-01-01');
INSERT INTO tb_PageFree VALUES('F3','L3','F3.L3@yahoo.com','1985-01-01');
INSERT INTO tb_PageFree VALUES('F4','L4','F4.L4@yahoo.com','1986-01-01');
INSERT INTO tb_PageFree VALUES('F5','L5','F5.L5@yahoo.com','1987-01-01');
INSERT INTO tb_PageFree VALUES('F6','L6','F6.L6@yahoo.com','1988-01-01');
INSERT INTO tb_PageFree VALUES('F7','L7','F7.L7@yahoo.com','1989-01-01');
GO
執行以下指令,目前該資料表只使用了兩個Page,一個IAM Page(PagePID=79),一個data page(PagePID=78)
進一步,我們來檢視PagePID=78這一個data page的內容,記得啟用旗標3604,不然訊息會被拋到error log中喔,這樣在SSMS的畫面就看不到該Page相關內容了
DBCC TRACEON(3604)
GO
DBCC PAGE('myDB',1,78,3)
GO
先來看看「 PAGE HEADER:」這個區塊,
1.m_type=1表示該Page的類型為"data page",而m_type=10表示該Page的類型為"IAM page"
2.因為我們建立的是heap table(無Clustered Index),且資料不多,也沒有建立任何非叢集索引,所以m_prevPage及m_nextPage皆為(0:0),也就是此Page沒有與其他Page產生水平的鏈結關係
3.m_slotCnt=7表示此Page已寫入7筆record
4.m_freeCnt=3112,表示此Page還剩餘3112 Bytes的空間(這也是本章想要瞭解的部分)
我們繼續往下看看「 Slot 0 」區塊,也就是第一筆record的描述區塊的部分,前面有提到,因為資料表設計的緣故,所以每一筆record都會是一樣的長度,所以我們只分析第一筆record就可以了。
第一張圖的 Record Size=710 即表示系統認為第一筆record用掉了710 Bytes的Page空間,但是在第二張圖,若將Column1~Column4這四個欄位所使用的長度相加,也只不過吃了703 Bytes,兩者相差了7 Bytes,這是因為這7 Bytes是屬於Row Overhead,是為了記錄record其他資訊所必須要額外使用到的空間(如: 記錄column的數量、variable width column的數量等等),而且此Row Overhead所使用的空間並不是固定的喔,可能會因為每筆資料的欄位多寡(NULL Bitmap),或是變動長度欄位數量的多寡(Variable Width Columns Offset Array),而會視情形增加所使用的Bytes數量,Row Overhead這部分可參考此篇:初步瞭解Data Record結構。不過以我們設計的例子來說,每一筆record所耗用的Row Overhead都是耗用7 Bytes,所以計算起來相對容易。
以下讓我們開始計算,這m_freeCnt(Page剩餘空間)=3112 Bytes是如何獲得的:
(1) 首先,我們知道整個Page大小為8 K,即8192 Bytes。
(2) Page Header佔用了96 Bytes。
(3) 7筆record真正用來儲存資料的空間為 (200+300+200+3) * 7筆資料 = 4921 Bytes。
(4) 7筆record的Row Overhead所佔用的空間 (7 Bytes * 7筆資料) = 49 Bytes。
(5) Page尾端的Row Offset所配置的36 Bytes,是用來指出每一筆record在Page中的偏移量,每一筆record使用2個Bytes,所以總共耗費(2 Bytes * 7筆資料) = 14 Bytes。
算式: (8192 - 96 - 4921 - 49 - 14) = 3112 Bytes
從上述算式的結果,總算可以知道m_freeCnt這個值是如何算出來的,但如果依此結果認為我們還有3112 Bytes來存放資料,這可能會有些誤解,因為前面提到,m_freeCnt代表的是該Page的剩餘空間,而不是指該Page可以用來存儲record的剩餘空間,所以m_freeCnt的值其實還包含著剩餘未使用的Row Offset空間約 (原配置36 Bytes - (每筆record耗用2 Bytes * 7筆資料)) = 22 Bytes
所以,扣除上述部分空間後,該Page可以用來存儲record的剩餘空間為(3112 - 22) = 3090 Bytes
[備註]:因為每筆record會耗用2 Bytes的Row Offset空間,如果資料筆數超過18筆(即36 Bytes / 2 Bytes = 18),則可能會開始佔用到record的剩餘空間(即3090 Bytes)喔!
Jay Huang