[SQL SERVER][Memo]頁面分割影響交易記錄檔大小
前言
我們都知道頁面分割(page splits)是相當耗費系統資源的(會造成額外的I/O讀取和空間浪費)，
所以我們應該儘可能減少頁面分割(page splits)產生頻率，
由於之前我一直相當在意查詢效能，直到幾天前才發現，
頁面分割也是會造成昂貴的交易記錄檔大小成本，
下面就來實際測試並紀錄一下。
--drop table test1
create table test1
(
c1 int,
c2 char(1000)
)
--預設順序 asc
create clustered index cidx_1
on test1(c1)
--遺漏 3
insert into test1
values (1,'rico'),(2,'rico'),(4,'rico'),(5,'rico'),(6,'rico'),(7,'rico'),(8,'rico')
--依造順序新增資料
begin tran
insert into test1 values(9,'rico')
--查看系統交易所使用的位元組數
select database_transaction_log_bytes_used as '代表交易之系統交易所使用的位元組數'
from sys.dm_tran_database_transactions
where database_id = 14
恢復上個交易後再次新增資料並製造頁面分割
--製造頁面分割
begin tran
insert into test1 values(3,'rico')
--查看系統交易所使用的位元組數
select database_transaction_log_bytes_used as '代表交易之系統交易所使用的位元組數'
from sys.dm_tran_database_transactions
where database_id = 14
1 page=8kb(maximum row size=8060 bytes)，當我新增 c1=3 資料時因為SQL Server要維護資料表順序，
所以這時交易就會連帶產生頁面分割動作(也超過大小限制)，而你也可以看該交易使用的位元組數是之前約2.4倍，
當你交易中包含未經排序資料時，不僅會產生額外的交易記錄檔，
而交易記錄檔過大也將會影響備份、Mirror、log shipping..等相關效能。
參考
How expensive are page splits in terms of transaction log?
sys.dm_tran_database_transactions (Transact-SQL)