曲演杂坛--页拆分

以下测试基于版本：SQL SERVER 2008

很多同行会问起页拆分的相关的问题，自己对页拆分页迷迷糊糊，有点云里雾里的感觉，今天来测试测试。

首先生成测试数据

--=========================================
--使用TestDB数据库来测试
USE TestDB
GO
DROP TABLE TB01
GO
--=======================================
--创建测试表TB01
CREATE TABLE TB01
(
    ID INT PRIMARY KEY,
    C1 NVARCHAR(MAX)
)
GO
--=======================================
--插入420条数据，所有数据存放在一个8KB的数据页中
INSERT INTO TB01(ID,C1)
SELECT T.RID,N'C' FROM (
SELECT 
ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
FROM sys.all_columns
) AS T
WHERE T.RID<422
AND T.RID<>418
--====================================

 现在表TB01上有一个数据页(接近填满)，使用DBCC查看

然后尝试插入数据导致页拆分：

--====================================
--插入一行数据
INSERT INTO TB01(ID,C1)
SELECT 418,REPLICATE(N'1',4000)
--====================================
--查看数据页
DBCC IND('TestDB','TB01',1)

我们可以很清楚地发现，在插入一行数据后，数据页由原来的一页变成了9页（一个非叶子节点页和8个叶子节点页），是不是很不科学呢？ 新插入的数据只需要一个数据页来存放，加上原来的数据，只需要2个数据库便可以存放，为什么会造成这么多页面使用呢？

通过上面的图，可以清楚看到数据有两层，非叶子节点（也是根节点）页是5170，使用该页来查看数据分布情况：

--===================================
--查看非叶子节点来查看数据和页的对应情况
DBCC PAGE('TestDB',1,5170,3)

观察上图的ID，我们可以发现以下规律

211=1+420/2

316=211+420/2/2

368=316+420/2/2/2

394=368+420/2/2/2/2

407=394+420/2/2/2/2/2

从上面的数据不难看出，每页数据逐渐一半一半地减少。再通过sys.fn_dblog(NULL,NULL)来查看事务，最后一次插入操作引发1次插入事务和8个页拆分事务。

由此，我们推断出在上面的插入过程中，发生了以下操作：

1. 新事务开始，一行新数据需要插入到数据页中，该数据行不是数据页最尾数据行

2. 判断页中剩余空间，发现数据页不能存放新插入行，需要页拆分

3. 开启一个新事务，将页中一半数据移动到一个新的页面，关闭事务

4. 循环第2步和第3步，直到有一数据页能存放新插入的行

5. 插入数据，提交事务

到此，很多人就会疑问，拆分一半到底是数据行数的一半还是数据占用空间大小的一半呢？

让我们再做一个实验

--==========================================
--清除表中数据
TRUNCATE TABLE TB01
--=======================================
--插入198条数据，所有数据存放在一个8KB的数据页中
--前99条数据和后99天数据的大小不相同
INSERT INTO TB01(ID,C1)
SELECT T.RID,N'C' FROM (
SELECT 
ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
FROM sys.all_columns
) AS T
WHERE T.RID<100

INSERT INTO TB01(ID,C1)
SELECT T.RID,N'CCCCCCCCCCCC' FROM (
SELECT 
ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
FROM sys.all_columns
) AS T
WHERE T.RID>100
AND T.RID<200
--====================================
--插入一行数据导致页拆分
INSERT INTO TB01(ID,C1)
SELECT 100,REPLICATE(N'1',2000)

同样适用根节点来数据分布：

 

 由于后99行数据占用的空间大小较大，在页拆分时，没有将后99条全部拆分到新的数据页上，因此我们得出结论，页拆分时是按照数据占用空间大小来拆分的，与数据行数无关。

--=====================================================================================================

总结：

1.发现在页拆分时，会按照页中数据占用空间的情况，将占用空间一半的数据移动到新的数据页上

2.如果拆分后仍无法存放新数据，则继续页拆分，知道有数据页可以存放新数据为止，因此一次插入操作可能会引起多次页拆分。

3.每次页拆分会被当成一个事务处理,页拆分的事务单独提交(在提交插入事务之前已提交)，及时插入失败，页拆分的事务也不会回滚。

4.更新导致的页拆分情况与插入导致的页拆分类似

PS:

1. 在测试中，未发现没有按照一半空间拆分的情况，但没有找到相关官方文档来证明。

--===========================================================================================================

 

小仓优子，各位大神应该知道的，不用我多说吧。吼吼