曲演杂坛--页拆分

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

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

首先生成测试数据

--=========================================--使用TestDB数据库来测试USE TestDBGODROP TABLE TB01GO--=======================================--创建测试表TB01CREATE 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 TWHERE T.RID<422AND 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 TWHERE T.RID<100INSERT 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 TWHERE T.RID>100AND T.RID<200--====================================--插入一行数据导致页拆分INSERT INTO TB01(ID,C1)SELECT 100,REPLICATE(N'1',2000)

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

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

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

总结：

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

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

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

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

PS:

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

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

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