🚫 Ad Blocker Detected

Please disable your AD blocker to continue using this site. Ads help us keep the content free! please press keyboard F5 to refresh page after disabled AD blocker

請關閉廣告攔截器以繼續使用本網站。廣告有助於我們保證內容免費。謝謝! 關閉後請按 F5 刷新頁面

0%

資料庫新增欄造成Page split

前言:

使用DB新增欄位一般很快就可以執行完畢,但最近我們在prod新增一個bit欄位卻需要跑快45分鐘…

經後面追查找到原因才有本篇文章.

問題重現

下面語法會建立兩個Table.

  • Test:新增10,000,000筆Sample Data
  • TestSplit:新增1,000,000筆Sample Data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
DROP TABLE IF EXISTS [dbo].[Test]

CREATE TABLE [dbo].[Test](
[ID] [int] NOT NULL IDENTITY(1,1),
[CustomerID] [VARCHAR](40) NOT NULL,
[col1] [VARCHAR](100) SPARSE NULL,
[col2] [VARCHAR](100) SPARSE NULL,
[col3] [VARCHAR](100) SPARSE NULL,
[col4] [VARCHAR](100) SPARSE NULL,
[CreateDate] [datetime2](3) NOT NULL,
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX CIX_ID ON [dbo].[Test](ID)

INSERT INTO [dbo].[Test] ([CustomerID],Col1,Col2,Col3,Col4,[CreateDate])
SELECT TOP 10000000
REPLICATE('ABCD',10),
REPLICATE('A',100),
REPLICATE('B',100),
REPLICATE('C',100),
REPLICATE('D',100),
SYSDATETIME()
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2

DROP TABLE IF EXISTS [dbo].[TestSplit]

CREATE TABLE [dbo].[TestSplit](
[ID] [int] NOT NULL IDENTITY(1,1),
[CustomerID] [VARCHAR](40) NOT NULL,
[col1] [VARCHAR](2000) SPARSE NULL,
[col2] [VARCHAR](2000) SPARSE NULL,
[col3] [VARCHAR](2000) SPARSE NULL,
[col4] [VARCHAR](2000) SPARSE NULL,
[CreateDate] [datetime2](3) NOT NULL,
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX CIX_ID ON [dbo].[TestSplit](ID)

INSERT INTO dbo.[TestSplit] ([CustomerID],Col1,Col2,Col3,Col4,[CreateDate])
SELECT TOP 1000000
REPLICATE('ABCD',10),
REPLICATE('A',2000),
REPLICATE('B',2000),
REPLICATE('C',2000),
REPLICATE('D',2000),
SYSDATETIME()
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2

我們先在test table新增欄位語法如下

執行下面語法瞬間完成

1
2
3
4
5
6
IF COL_LENGTH('dbo.[Test]','Col6') IS NULL
BEGIN
ALTER TABLE dbo.Test
ADD Col6 BIT NOT NULL
CONSTRAINT DF_Test_Col6 DEFAULT 0
END

但在執行TestSplit語法時跑很久…
在我電腦花了1分32秒

1
2
3
4
5
6
IF COL_LENGTH('dbo.TestSplit','Col6') IS NULL
BEGIN
ALTER TABLE dbo.TestSplit
ADD Col6 BIT NOT NULL
CONSTRAINT DF_TestSplit_Col6 DEFAULT 0
END

明明TestSplit table比Test table資料少10倍,為什麼還比較慢?

這就要說到資料表底層的儲存原理.

Table size is 8k byte

資料表儲存資料最小單位是

一頁是存放8K Byte資料(準確來說是8060 byte,因為每頁有一些meta data需要存放).

Test一筆資料大約500 byte,但TestSplit一筆快等於一頁資料…

我們知道一個row資料是連續放置,如果有欄位新增且此頁已經放不下此欄位大小資訊就會發生Page Split.

Page split會影響系統效能,且當資料頁面不連續時會影響Disk IO讀取速度

產生問題和如何解決

我們在做DDL操作時會對於Table上Sch-M的lock,這個lock會和所有其他索互斥,所以會導致一大堆blocking,假如你新增欄位是對於一張大表那會有非常嚴重的後果…

可以嘗試建立另一張表並新增你想要的欄位和JOIN此表的條件,這樣可以避免線上產生問題,之後對於要使用的查詢可以使用OUTER JOIN或在背景把資料Patch完成.

__此文作者__:Daniel Shih(石頭)
__此文地址__: https://isdaniel.github.io/sqlserver-create-column-tips/
__版權聲明__:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!

如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^