sqlserver 数据存储

/*

    测试数据存储

*/

准备工作

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

-- 创建测试数据库

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

create database test

go

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

-- 创建测试表

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

use test

go

 

create table test(

id int identity(1,1) not null,

name varchar(10) not null

)

go

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

-- 导入数据

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

declare @i int

set @i=0

while @i<100

begin

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

    set @i=@i+1

end

go

declare @i int

set @i=0

while @i<100

begin

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

    set @i=@i+1

end

go

declare @i int

set @i=0

while @i<100

begin

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

    set @i=@i+1

end

go

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‘

GO

结果如下:

 技术分享                

 

测试步骤

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)

go

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

go

结果如下:

技术分享

从截图的红框部分可以发现,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)

go

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

go

结果如下:

技术分享

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

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)

go

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

go

结果:

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时,数据的位置不会发生改变。

 

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。