sqlserver 数据存储

测试数据存储

准备工作

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

-- 创建测试数据库

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

create database test

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

-- 创建测试表

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

use test

create table test(

id int identity(1,1) not null,

name varchar(10) not null

)

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

-- 导入数据

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

declare @i int

set @i=0

while @i<100

begin

insert into test(name) values('aaa')

set @i=@i+1

end

declare @i int

set @i=0

while @i<100

begin

insert into test(name) values('bbb')

set @i=@i+1

end

declare @i int

set @i=0

while @i<100

begin

insert into test(name) values('ccc')

set @i=@i+1

end

declare @i int

set @i=0

while @i<100

begin

insert into test(name) values('ddd')

set @i=@i+1

end

go-- =============================================

-- 查看相关信息

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

SELECT COUNT(*) FROM test

select * from sys.partitions where [object_id]=object_id('test')

select * from sys.objects where name='test' and type_desc='USER_TABLE'

select * from sys.indexes where [object_id]=2105058535

EXEC sp_spaceused 'test'

结果如下：

测试步骤

1、我们需要找到数据存放于哪些数据页上面。

打开一个session 1 执行以下代码：

begin tran

update test set name='fff' where id =1

waitfor delay '00:00:05'

select * from test where id=2

同时打开session 2 执行以下代码

begin tran

update test set name='fff' where id =2

select * from test where id=1

同时打开session 3 执行以下代码：

select * from sys.dm_os_waiting_tasks where session_id>50

结果如下：

然后执行以下代码：

dbcc traceon(3604)

dbcc page('test',1,21,3)

结果如下：

从截图的红框部分可以发现，page(1:21)上存储了385条记录(具体说明见之后的页头信息说明)，但是我们之前保存了400条记录，那剩下的15条记录存放在哪一个数据页？

回滚session 1 、 session 2的事务。

在session 1 中执行以下代码：

begin tran

update test set name='fff' where id =386

waitfor delay '00:00:05'

select * from test where id=387

同时在session 2中执行以下代码：

begin tran

update test set name='fff' where id =387

select * from test where id=386

同时打开session 3 执行以下代码：

select * from sys.dm_os_waiting_tasks where session_id>50

结果如下：

然后执行以下代码：

dbcc traceon(3604)

dbcc page('test',1,55,3)

结果如下：

因为没有索引，所以页面无法知道下一页是那个页面。

m_prevPage = (0:0) ：前一页的页码 m_nextPage = (0:0) ：后一页页码

2、创建聚集索引

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

-- 创建聚集索引

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

create clustered index ix_test on test(id)

drop index ix_test on test

获取创建索引之后的数据页，同样需要把之前的方法再操作一遍，此时，我获取到的第一个数据页为page（1:79）

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

-- 查看数据页存储

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

dbcc traceon(3604)

dbcc page('test',1,79,3)

结果：

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:79)

BUFFER:

BUF @0x04C83CE0

bpage = 0x1DD2A000 bhash = 0x00000000 bpageno = (1:79)

bdbid = 7 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 27502 bstat = 0xc0010b

blog = 0x1212121b bnext = 0x00000000

bdbid=7 ：数据库ID，可以使用DB_NAME(7)得到数据库名为测试库test。

Bpageno=(1:79) ：数据页编号，1指文件ID，79指数据页ID

PAGE HEADER: (页头信息):

Page @0x1DD2A000

m_pageId = (1:79) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 30 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039894016

Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:89)

pminlen = 8 m_slotCnt = 368 m_freeCnt = 0

m_freeData = 7456 m_reservedCnt = 0 m_lsn = (30:248:60)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 0

m_pageId = (1:79) ：数据页编号

m_prevPage = (0:0) ：上一页编号

m_nextPage = (1:89) ：下一页编号，可以执行dbcc page(‘test’,1,89,3)来查看m_prevPage值必为(1:79)

Metadata: PartitionId = 72057594038910976 ：所属分区ID

Metadata: AllocUnitId = 72057594039894016 ：包含该页的分区单元ID

Metadata: ObjectId = 2105058535 ：该页所属的对象ID

m_level = 0 ：该页面在索引中的级别（数据页为0）

m_slotCnt = 368 ：该页存储的数据行数，可以执行dbcc page(‘test’,1,89,3)来查看m_slotCnt必为32,368+32=400，与该表插入的行数完全一致。

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 20

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 20

Memory Dump @0x0B2FC060

00000000: 30000800 01000000 03000002 00110014 †0...............

00000010: 00616161 ††††††††††††††††††††††††††††.aaa

Slot 0 Column 67108865 Offset 0x0 Length 0 Length (physical) 0

DROPPED = NULL

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 ：第1行数据

id = 1 ：字段id的值，实际长度为 Length 4 Length (physical) 4

Slot 0 Column 2 Offset 0x11 Length 3 Length (physical) 3

name = aaa ：字段name的值，实际长度为Length 3 Length (physical) 3

注意：

执行语句：update test set name='fffff' where id=1

再重新执行语句dbcc page(‘test’,1,79,3) 可以发现，slot 0 这行没有值。Page(1:79)有值的首行是从slot 1开始。这个数据仅仅只是更新而不是删除，原有slot 0的值去哪里里了？

执行dbcc page(‘test’,1,89,3) ，你会发现slot 32行变成了page(1:79)行的值。

从这里可以看出：update操作的原理，删除原有得数据行，并将数据重新插入到这个对象所属页面的最后一行。

Slot 1 Offset 0x74 Length 20

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 20

Memory Dump @0x0B2FC074

00000000: 30000800 02000000 03000002 00110014 †0...............

00000010: 00616161 ††††††††††††††††††††††††††††.aaa

Slot 1 Column 67108865 Offset 0x0 Length 0 Length (physical) 0

DROPPED = NULL

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2

Slot 1 Column 2 Offset 0x11 Length 3 Length (physical) 3

name = aaa

Slot 2 Offset 0x88 Length 20

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 20

Memory Dump @0x0B2FC088

00000000: 30000800 03000000 03000002 00110014 †0...............

00000010: 00616161 ††††††††††††††††††††††††††††.aaa

Slot 2 Column 67108865 Offset 0x0 Length 0 Length (physical) 0

DROPPED = NULL

Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 3

Slot 2 Column 2 Offset 0x11 Length 3 Length (physical) 3

name = aaa

Slot 366 Offset 0x1cf8 Length 20

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 20

Memory Dump @0x0B2FDCF8

00000000: 30000800 6f010000 03000002 00110014 †0...o...........

00000010: 00646464 ††††††††††††††††††††††††††††.ddd

Slot 366 Column 67108865 Offset 0x0 Length 0 Length (physical) 0

DROPPED = NULL

Slot 366 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 367

Slot 366 Column 2 Offset 0x11 Length 3 Length (physical) 3

name = ddd

Slot 367 Offset 0x1d0c Length 20

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 20

Memory Dump @0x0B2FDD0C

00000000: 30000800 70010000 03000002 00110014 †0...p...........

00000010: 00646464 ††††††††††††††††††††††††††††.ddd

Slot 367 Column 67108865 Offset 0x0 Length 0 Length (physical) 0

DROPPED = NULL

Slot 367 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 368

Slot 367 Column 2 Offset 0x11 Length 3 Length (physical) 3

name = ddd

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

结论

1、在没有聚集索引(非聚集索引我也测试了，但dbcc page的结果与没有索引时保持一致)的情况下，数据页的m_pageId 、m_prevPage属性值都为(0:0)，也就是说，无法指向下一页。

但是，使用dbcc ind('test',2105058535，-1)可以看到页面的前后顺序（这里特别感谢肖磊），注意，由于我重新删除数据库并按照文章上面的步骤执行后，pageid有变化，已经对不上文章中的pageid。

2、当出现了聚集索引，数据会迁移到别的存储页面中。这就说明：重建聚集索引时，数据可能会从一个页面移动到另外的页面，从而产生大量的IO。

3、数据更新时，数据库先删除原有数据，同时再往改对象所存储的最后一页插入更新后的数据。这是武断的结论，感谢高文佳和肖磊，

高文佳的意见是：

对于update操作在数据库上到底做UPDATE 还是做DELETE+INSERT，取决当前的空间是否能存放更新后的数据

肖磊的意见是：

m_freeCnt这个是指页面剩余空间，如果它是0，你再将其中一条记录update成更长的value，当然会引起页拆分。对于varchar类型，还有overflow的问题

我通过测试，在更新第二个数据页上面的id为380的数据发现，完全符合肖磊的意见，当数据页剩余存储空间足够时，即m_freeCnt值不为0时，数据的位置不会发生改变。

posted @ 2015-06-01 17:16 菜鸟就是我阅读(1045) 评论(0) 编辑收藏举报

刷新页面返回顶部

实践是检验理论的唯一方式

sqlserver 数据存储

准备工作

测试步骤

1、我们需要找到数据存放于哪些数据页上面。

打开一个session 1 执行以下代码：

同时打开session 2 执行以下代码

同时打开session 3 执行以下代码：

然后执行以下代码：

在session 1 中执行以下代码：

同时在session 2中执行以下代码：

同时打开session 3 执行以下代码：

然后执行以下代码：

2、创建聚集索引

结论

公告