延續上週新增資料表欄位Default Value + Not Null是否會給舊資料預設值的題目。上一篇確認Default Value + Not Null會給也必須要給舊資料預設值,但為何相同資料量測試環境飛快但正式環境跑1小時?這種飛快,感覺不太真實,資料分頁真的有修改到?還是只有改定義?
SQL版本差異(SQL 2008 vs 2014)
因為客戶專案要升級SQL,正式環境是SQL 2008 R2,測試環境是SQL 2014,會不會是DB版本造成效能的明顯差異?!
經過簡單的實驗,發現我們又踩了DB版本差異的坑,2012之後,她真的沒有去改資料分頁,她靠一種冰魔法來維持ACID的一致性,筆記實驗的過程:
對照組(SQL2008 R2)
建立資料表,然後新增4筆冰雪奇緣的人物;然後新增欄位C3,用預設值'Frozen' + NOT NULL
SELECT @@version
use tempdb
CREATE TABLE T1(
C1 INT IDENTITY,
C2 VARCHAR(30)
)
--冰雪奇緣(安娜,艾莎,雪寶,阿克)
INSERT INTO T1
VALUES ('Anna'), ('Elsa'), ('Olaf'), ('Kristoff')
--新增冰雪奇緣Frozen
ALTER TABLE T1
ADD C3 VARCHAR(30) DEFAULT 'Frozen' NOT NULL
SQL 版本
查看資料列所在的分頁
SELECT
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,a.* from T1 a
CROSS APPLY fn_PhysLocCracker(%%physloc%%) b
查看資料列對應的分頁資訊
查看分頁
dbcc traceon (3604,-1)
dbcc page(2,1,114,2)
dbcc page參數說明
dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
- tempdb id固定為2
- file id=1
- page id=114
- 2=page header plus whole page hex dump
Page Demp
的確有新增Frozen,而且是整筆重新新增,可以注意Anna、Elsa、olfa、kristoff..都有兩個,分別是新增欄位前與後的資料列內容,我想這也是正式環境執行超過1小時的原因。
實驗組(SQL 2014)
建立資料表,然後新增4筆冰雪奇緣的人物;然後新增欄位C3,用預設值'Frozen' + NOT NULL
SELECT @@version
USE tempdb
CREATE TABLE T1(
C1 INT IDENTITY,
C2 VARCHAR(30)
)
--冰雪奇緣(安娜,艾莎,雪寶,阿克)
INSERT INTO T1
VALUES ('Anna'), ('Elsa'), ('Olaf'), ('Kristoff')
--新增冰雪奇緣Frozen
ALTER TABLE T1
ADD C3 VARCHAR(30) DEFAULT 'Frozen' NOT NULL
查看資料列所在的分頁
SELECT
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,a.* from T1 a
CROSS APPLY fn_PhysLocCracker(%%physloc%%) b
查看資料列對應的分頁資訊
查看分頁
dbcc traceon (3604,-1)
dbcc page(2,1,118,2)
沒有Frozen也!這代表資料分頁沒有修改。
不過,查詢資料有Frozen。
select * from t1
查詢結果集:
這是一種SQL Server的冰魔法! 資料分頁雖然沒有實際給了預設值,但查詢有額外的預設值定義處理,帶了我們設定的預設值。
難怪測環境試很快(SQL2014只修改定義),正式慢(SQL2008要一筆一筆新增)。
新增欄位後新的資料
再試試新增一筆壞漢斯王子,然後查看Page Dump
INSERT into T1 VALUES('Hans','Frozen')
dbcc traceon (3604,-1)
dbcc page(2,1,118,2)
Page Demp
漢斯的尾巴有出來了,有Frozen! 不過舊資料(Elsa、Anna..)後面還是沒有Frozen。
哪一個版本開始出現
相同魔法在SQL2012就出現了!
檢查Page Header是否有修改(用SQL 2012測)
新增欄位前
最後的lsn=(45:56721:291)
新增欄位後
新增欄位時,選擇Default Value + Not Null,實際的Data Page最後的lsn=(45:56721:291)總長度也沒改變,真的沒改Data Page。
小結:
- 發現是自己智慧未開,原來SQL2012 優化了Alter Table Default Value Not Null的作法,新增欄位時不需要再對舊資料一筆一筆給預設值,她直接修改了內部partition column的定義,select list回傳結果集時自動幫我們用default value替代掉沒有值(Null)的欄位。
- Default Value + Not Null會給值,但是2012之後有冰魔法,以邏輯取代物理的方式確保ACID的一致性,她更快了。
- 版本坑+1,測試和正式的DB版本要一樣。
- SQL Server 2008 R2 SP3 主流支援2014年7月結束,預計2019年7月將停止延伸性(包含安全性)的支援。
最愛雪寶的summer歌曲
2017.02 東京迪士尼
參考:
How to use DBCC PAGE
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/
UNDOCUMENTED VIRTUAL COLUMN: %%LOCKRES%
https://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/