RiCo技術農場

從我接觸SQL SERVER以來，我一直不是Trigger愛好者(程度大概和巢狀交易相當吧)，

為什麼呢?因為Trigger協同處理資料會讓簡單的事情變複雜(讓SQL SERVER自動幫你處理事情，

有時是需要付出昂貴代價的)，我在SQL SERVER這條路上，

始終保持3KS(Keep Simple、Keep Stupid、Keep Study)，

當你理解背後行為過程，其實很多規畫和設計都要依當時環境和需求來處理，

所以我有時不會走所謂的"正常道路"，雖然Trigger還是有好處，

但我一隻手就可以數出來，anyway~~該學員問題主因是頁面分割引起，

這裡我示範Trigger如何造成頁面分割，另外關於頁面分割你能回答以下問題嗎?

1. 什麼操作會引起頁面分割?
2. 頁面分割會造成什麼影響?
3. 交易rollback，頁面分割也會rollback嗎?
4. 頁面分割會耗用記憶體資源嗎?
5. 僅資料頁會發生頁面分割嗎?

Note:我blog有幾篇提到頁面分割(可參考最後連結)

大部分系統一定有刪除需求，而真實世界，資料類型選擇經常是被忽略的環節，

我得到多數答案如下:

1. 開發時程短，所以資料表先這樣。
2. 寫商業邏輯比較重要，功能需求先達到，後面有時間、有問題再來改資料類型。
3. 避免資料長度不一或後續修改麻煩甚至觀念錯誤，一開始選擇寬欄位(nvarchar(4000)、varchar(8000)..等)或LOB。
4. 效能問題交給DBA，我只重視需求是否符合(我深知"職責分離"的好處~XD)。
5. 前人設計就是這樣，我不能輕易修改的(我理解"敵不動我不動"的道理~XD)。

但偏偏多數人又喜愛用Trigger，也因這樣無形中就埋下效能地雷，

這篇讓我簡單重現Trigger After Delete，如何造成我厭惡的頁面分割。

 

我的Trigger很懶惰，資料刪除後不做任何事情

-刪除後，很簡單的不做任何事情
create trigger UF_TR_Del
on dbo.testPageSplitDel
AFTER DELETE
as return

 

執行刪除作業前

--查看頁面分割數量
select COUNT_BIG(*) as [PageSplits]
from sys.fn_dblog(NULL,NULL) fdblog
where fdblog.[Transaction Name] = N'SplitPage';

--查看資料表 data page資訊
select ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.max_record_size_in_bytes
from sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID(N'dbo.testPageSplitDel', N'U'), NULL, NULL, 'DETAILED') AS ips
where ips.index_level = 0

Pagecount:31。

--查看緩衝區中page資訊
select t3.[file_id],
    t3.page_id,
    t3.page_type,
    t3.row_count,
    t3.free_space_in_bytes,
    t3.is_modified
from sys.partitions AS t1
join sys.allocation_units AS t2
    on t2.container_id = t1.hobt_id
join sys.dm_os_buffer_descriptors AS t3 ON 
    t3.allocation_unit_id = t2.allocation_unit_id
where t1.[object_id] = OBJECT_ID(N'dbo.testPageSplitDel', N'U')
order by t3.page_id;

每一page空白大小:21。

 

執行刪除作業後

--Delete datas
delete testPageSplitDel
where ID % 20 = 0;

查詢資料表

select * from testPageSplitDel where id>=100 and id<3000

上面測試資料表大小看起來很正常，但下面我將修改欄位長度，符合我看到的真實世界。

drop table testPageSplitDel
-- Test table
create table dbo.testPageSplitDel
(
    ID    int identity(1,1) PRIMARY KEY,
    c1    nvarchar(50) NULL,
    c2    varchar(8000) NULL,--長度由4000改為8000
)

--執行刪除後查看頁面分割和page資訊
-- Delete datas
delete testPageSplitDel
where ID % 20 = 0;

查詢資料

select * from testPageSplitDel where id>=100 and id<3000

這就是執行刪除作業後，資料表大小不減反增的主因。

 

我建立的Trigger在資料刪除後沒做任何事情，但同樣刪除500筆資料操作，

第二次測試卻在改變欄位大小後，

資料表大小不減反增，相關作業效能一路走下坡，

這些看似正常的行為，卻往往隱藏效能地雷，如果DBA或開發人員觀念錯誤，

更是容易引爆(魔鬼總是藏在細節裡)。

另為什麼我更改欄位長度會發生頁面分割?

很多人不是說，新增或更新資料才會發生頁面分割，為什麼刪除資料也會發生頁面分割?

刪除資料後，為什麼資料表大小沒有立即減少?SQL SERVER在偷懶嗎?

上面問題答案你可以從我的blog找到，

因為這絕對不是Bug ，且SQL SERVER也沒在偷懶  :) 。

頁面分割答案如下:

1.什麼操作會引起頁面分割?

Ans:新增、更新、刪除、查詢(特例)。

2.頁面分割會造成什麼影響?

Ans:內部碎片、page變多，進而影響效能。

3.交易rollback，頁面分割也會rollback嗎?

Ans:不會。

4.頁面分割會耗用記憶體資源嗎?

Ans:會。因為是在記憶體中處理且成本昂貴。

5.僅資料頁會發生頁面分割嗎?

Ans:否，索引頁也會發生。

 

結論:

Trigger是萬惡源頭。

 

參考

[SQL SERVER][Memo]再談 Clustered Index

[SQL SERVER][Performance]小心使用With NoLock

[SQL SERVER]小心使用With NoLock(續)

[SQL SERVER][Memo]頁面分割影響交易記錄檔大小

[SQL SERVER][Maintain]管理交易記錄檔(5)

[SQL SERVER]重視轉發紀錄

sys.dm_db_index_physical_stats (Transact-SQL)

Row Versioning Resource Usage