只是淺談MS SQL Server的Page Splits運作原理

2017-12-10

一直以來，很多文章或書籍都會提到資料庫在對資料做增刪修都會因為資料異動導致Page Splits的產生。一旦過度的分割就會提高所謂的邏輯片段，而要降低邏輯片段就得對資料表或索引做Rebuild或Reorganize，今天心血來潮突然想LAB看看SQL對Page Splits的運作原理為何，所以簡易做個實驗。

下圖中我建立資料庫DB1，在資料庫建立一個資料表Tb1，每一列為2004個Bytes，這樣一來每個Page最多只能存放4筆資料(一個Page為8060個Bytes)。我用Tb1的 id欄位來當叢集索引，並寫入了16筆紀錄。

SQL儲存的最小單位是Page，但SQL在給空間時會一次配置8個Page，也就是一個Extents(64K Bytes)。而我Insert了16筆資料會佔了4個Page再加上1個Page用來紀錄Index紀錄的中繼層級頁面，所以這個Extents也就用掉了5個頁面，配置如下圖所示。

接下來我們用 DBCC IND('DB1','Tb1',1) 指令來看一下Tb1的頁面配置狀況。下圖中紅色區塊是存放資料的頁面，而藍色的區塊頁面則是放索引的頁面，所以透過下表我們可以看到該Table目前用了5個Page，Page編號是 232 ~ 236 (注意同一個Extents其Page是連號的)。而該資料表的資料排序是 232→234→235→236 ，如下圖左邊中我畫上的編號順序。頁面順序可以從表中的NextPagePID及PrevPagePID的欄位內容來排序。

接下來我們用DBCC PAGE('DB1',1,233,3)指令來看看紀錄Index紀錄的中繼層級頁面233裡存了甚麼。如下圖所示，他存了4筆資料分別記錄了叢集欄位id(1、2、3、4)存放的頁面位置。

接下來我故意要塞入一筆 id=1 name='Cary' 的資料進資料表，由於id=1所以可以知到這筆資料會被塞入存放id=1的頁面中。但由於該頁面已滿，無法寫入第5筆資料，為了寫入資料勢必要透過Page Splits來取得空間。

下圖中我塞入一筆 id=1 name='Cary' 的資料後馬上檢視該Table的Page配置，結果多了一個 Page 237 出來如下圖中紅色區塊。透過NextPagePID及PrevPagePID的欄位內容來重新排序一下。發現順序變為232→237→234→235→236 新增加的頁面順序卻擺在第二順位。

重新檢視存放索引資料的 Page 233 頁面，可以發現多了一筆紀錄。該紀錄顯示id=1的值存放頁面是Page 237，如下圖紅色區塊區。

接下來我們看看這一頁新增的頁面裡到底存了什麼內容，我們用指令 DBCC PAGE('DB1',1,237,3) 來檢視頁面資料。如下圖所示該頁面存了3筆資料，剩餘空間2033 Bytes，資料存放的皆是id=1的內容。我們發現id=1 name=Cary的資料也是存放在這一個頁面。

根據上述資料，我們可以知道目前該Table的資料配置如下圖，id=1的資料有5筆並分配在兩個頁面內。

接下來我再塞入一筆 id=2 name='Cary' 的資料進資料表，由於id=2所以可以知到這筆資料會被塞入存放id=2的頁面中。但由於該頁面也是滿的，無法寫入第5筆資料，為了寫入資料勢必也要透過Page Splits來取得空間。

下圖中我塞入一筆 id=2 name='Cary' 的資料後馬上檢視該Table的Page配置，結果多了一個 Page 238 出來如下圖中紅色區塊。透過NextPagePID及PrevPagePID的欄位內容來重新排序一下。發現順序變為 232→237→234→238→235→236 新增加的頁面順序卻擺在第四順位。

重新檢視存放索引資料的 Page 233 頁面，可以發現多了一筆紀錄。該紀錄顯示id=2的值存放頁面是 Page 238，如下圖紅色區塊區。

接下來我們看看這一頁新增的頁面裡到底存了什麼內容，我們用指令 DBCC PAGE('DB1',1,238,3) 來檢視頁面資料。如下圖所示該頁面存了3筆資料，剩餘空間2033 Bytes，資料存放的皆是id=2的內容。我們發現id=2 name=Cary的資料也是存放在這一個頁面。

根據上述資料，我們可以知道目前該Table的資料配置如下圖，id=1的資料有5筆並分配在兩個頁面內，id=2的資料也有5筆也分配在兩個頁面內。

接下來我要塞入4筆 id=5 的資料到Table中，這樣一來一個 Extents(8個Page) 都會被吃滿。製造完這樣的環境後我們一樣再來測試Page Splits。

Insert Into 4筆id=5 name=Rock的資料進Tb1資料表。

檢視一下Tb1資料表的頁面配置，如下圖所示 Page 232 到 Page 239 共8個頁面都被使用到了(下圖紅色區塊)。

此刻我們用 DBCC SHOWCONTIG(Tb1,1) 來檢視資料表片段狀況。可以看見該Table目前 資料頁數是7頁及Extents數是1，由於Splits多次所以邏輯掃描片段是71%(資料頁面7，因不連號而來回了5次，5/7=0.71428左右)。

當整個Extents沒有空間再應付Page Splits的需求時，接下來我再塞入一筆 id=3 name='Cary' 的資料進資料表，由於id=3所以可以知到這筆資料會被塞入存放id=3的頁面中。因為整個Extents是滿的，接下來的空間配置SQL會如何做呢?

下圖中我塞入一筆 id=3 name='Cary' 的資料後馬上檢視該Table的Page配置，結果多了一個 Page 256 出來如下圖中紅色區塊。透過NextPagePID及PrevPagePID的欄位內容來重新排序一下。發現順序變為 232→237→234→238→235→256→236→239 新增加的頁面序號跳到 256，順序擺在第6順位。

重新檢視存放索引資料的 Page 233 頁面，可以發現多了一筆紀錄。該紀錄顯示id=3的值存放頁面是Page 256，如下圖紅色區塊區。

接下來我們看看這一頁新增的頁面裡到底存了什麼內容，我們用指令 DBCC PAGE('DB1',1,256,3) 來檢視頁面資料。如下圖所示該頁面存了3筆資料，剩餘空間2033 Bytes，資料存放的皆是id=3的內容。我們發現id=3 name=Cary的資料也是存放在這一個頁面。

由於第一個Extents的最後一個Page編號是239但是這一個新的Page不是接續的給Page 240而是跳號到256，由此可以判斷兩個Extents是不接續的。下圖是簡易的顯示目前Table的頁面配置。

我們透過 sys.dm_db_database_page_allocations 這個DMV可以更清楚的看見Extents跟Pages彼此之間的關聯。

這時我們再用 DBCC SHOWCONTIG(Tb1,1) 來檢視資料表片段狀況。可以看見該Table目前資料頁數是8頁及Extents多了1個變成2了，由於Splits多次所以邏輯掃描片段是87.5%(資料頁面8，因不連號而來回了7次，7/8=0.875左右)。至於範圍掃描片段變成50%則是因為目前有2個Extents但第一個跟第二個Extents是跳號的不接續的，所以1/2=0.5就是50%囉。

這真的是一個又臭又長的LAB，希望大家看得懂Rock在寫什麼。透過簡易的LAB後我們可以知到每一次的Splits ，SQL會先取得一個空的新Page然後將部分資料改寫到新的Page去，然後改寫頁面的順序指標。當這樣的動作很頻繁時不僅耗費系統資源且會造成資料片段嚴重，當資料頁面不連續時也會影響Disk IO讀取的速度。所以如何減少Page Splits的發生在資料表設計時就需考慮進去，例如索引(叢集跟非叢集)欄位的選擇，還有FillFactor設定值等，片段率達到多少時就Rebuild或Reorganize。

補充 : Page Splits時SQL會盡量拆分為50% 50%，以下是找到的一段說明。The page will split *either* as close to 50/50 as it can (the general case) or at the insertion point, depending on the distribution of index key values on the splitting page.