求知 文章 文库 Lib 视频 iPerson 课程 认证 咨询 工具 讲座 Modeler   Code  
会员   
 
  
 
 
     
   
分享到
聚集索引表插入数据和删除数据的方式是怎样的
 

发布于2013-8-12

 

先创建一个聚集索引表

USE [pratice]
GO
--DROP TABLE ClusteredTable
CREATE TABLE ClusteredTable
( id INT,
col2 CHAR(999),
col3 VARCHAR(10)
)

这个表每个行由int(4字节),char(999字节)和varchar(0字节组成),所以每行为1003个字节,

则8行占用空间1003*8=8024字节加上一些内部开销,可以容纳在一个页面

1 --创建聚集索引
2 CREATE CLUSTERED INDEX CIX ON ClusteredTable(id ASC)

插入数据

--由于聚集索引需要有2个数据页才会出现一个索引页,所以这里插入16条记录,16条记录就需要2个数据页
DECLARE @i INT
SET @i=1
WHILE(@i<32)
BEGIN
INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] )
VALUES(@i,'xxx','')
SET @i=@i+2
END

看一下所用的数据页数量

SELECT  [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth],
[index_level], [avg_fragmentation_in_percent], [fragment_count],
[page_count]
FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'),
OBJECT_ID('pratice.dbo.ClusteredTable'),
NULL, NULL, NULL)

再创建一个表,用来保存DBCC IND的结果

CREATE TABLE DBCCResult (
PageFID NVARCHAR(200),
PagePID NVARCHAR(200),
IAMFID NVARCHAR(200),
IAMPID NVARCHAR(200),
ObjectID NVARCHAR(200),
IndexID NVARCHAR(200),
PartitionNumber NVARCHAR(200),
PartitionID NVARCHAR(200),
iam_chain_type NVARCHAR(200),
PageType NVARCHAR(200),
IndexLevel NVARCHAR(200),
NextPageFID NVARCHAR(200),
NextPagePID NVARCHAR(200),
PrevPageFID NVARCHAR(200),
PrevPagePID NVARCHAR(200)
)

执行下面语句,看一下DBCC IND的结果

先说明一下:

PageType 分页类型:1:数据页面;2:索引页面;3:Lob_mixed_page;4:Lob_tree_page;10:IAM页面

IndexID 索引ID,0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 大于250就是text或image字段

INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ')
SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

看一下页面内容

DBCC TRACEON(3604,-1)
GO
DBCC PAGE([pratice],1,14623,3) --索引页
GO
DBCC PAGE([pratice],1,14544,3)--数据页
GO
DBCC PAGE([pratice],1,37036,3)--数据页
GO

聚集索引页面

数据页 14544 可以看到14544页里面保存的是1~15的记录 其中字段m_slotCnt = 8 表示本页面存储了8条记录

PAGE: (1:14544)


BUFFER:


BUF @0x03F577E0

bpage = 0x19108000 bhash = 0x00000000 bpageno = (1:14544)
bdbid = 5 breferences = 0 bUse1 = 12315
bstat = 0x2c0000b blog = 0x32159bb bnext = 0x00000000

PAGE HEADER:


Page @0x19108000

m_pageId = (1:14544) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (0:0) m_nextPage = (1:37036)
pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0
m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2773:397:15)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 1

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 3

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 5

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 7

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 9

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A

UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 11

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD80C


UNIQUIFIER = [NULL]

Slot 6 Column 1 Offset 0x4 Length 4

id = 13

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]

Slot 7 Offset 0x1bfe Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CDBFE

UNIQUIFIER = [NULL]

Slot 7 Column 1 Offset 0x4 Length 4

id = 15

Slot 7 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

数据页 37036可以看到14544页里面保存的是17~31的记录 其中字段m_slotCnt = 8 表示本页面存储了8条记录

PAGE: (1:37036)


BUFFER:


BUF @0x03F6F5F4

bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036)
bdbid = 5 breferences = 0 bUse1 = 12635
bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000

PAGE HEADER:


Page @0x19CD0000

m_pageId = (1:37036) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (1:14544) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0
m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2773:421:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 17

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 19

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 21

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33CC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 23

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 25

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D41A


UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 27

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D80C


UNIQUIFIER = [NULL]

Slot 6 Column 1 Offset 0x4 Length 4

id = 29

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]

Slot 7 Offset 0x1bfe Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33DBFE


UNIQUIFIER = [NULL]

Slot 7 Column 1 Offset 0x4 Length 4

id = 31

Slot 7 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

我们创建一个堆表以作为对比

USE [pratice]
GO
--DROP TABLE HeapTable
CREATE TABLE HeapTable
( id INT,
col2 CHAR(999),
col3 VARCHAR(10)
)

DECLARE @i INT
SET @i=1
WHILE(@i<32)
BEGIN
INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] )
VALUES(@i,'xxx','')
SET @i=@i+2
END

看一下所用的数据页数量

SELECT  [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth],
[index_level], [avg_fragmentation_in_percent], [fragment_count],
[page_count]
FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'),
OBJECT_ID('pratice.dbo.HeapTable'),
NULL, NULL, NULL)

再看一下DBCC IND的结果

--先清空[DBCCResult]的数据
TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,HeapTable,-1) ')
SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

看一下数据页14529里的数据 id从1~13 其中字段m_slotCnt = 7 表示本页面存储了7条记录

PAGE: (1:14529)


BUFFER:


BUF @0x03F83188

bpage = 0x1A516000 bhash = 0x00000000 bpageno = (1:14529)
bdbid = 5 breferences = 0 bUse1 = 13252
bstat = 0x2c0000b blog = 0x2159bbbb bnext = 0x00000000

PAGE HEADER:


Page @0x1A516000

m_pageId = (1:14529) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012
m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2773:486:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060

Slot 0 Column 0 Offset 0x4 Length 4

id = 1

Slot 0 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452

Slot 1 Column 0 Offset 0x4 Length 4

id = 3

Slot 1 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


Slot 2 Column 0 Offset 0x4 Length 4

id = 5

Slot 2 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36

Slot 3 Column 0 Offset 0x4 Length 4

id = 7

Slot 3 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028

Slot 4 Column 0 Offset 0x4 Length 4

id = 9

Slot 4 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A

Slot 5 Column 0 Offset 0x4 Length 4

id = 11

Slot 5 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD80C

Slot 6 Column 0 Offset 0x4 Length 4

id = 13

Slot 6 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

看一下数据页14545里的数据 id从15~27 其中字段m_slotCnt = 7 表示本页面存储了7条记录

PAGE: (1:14545)


BUFFER:


BUF @0x03F5782C

bpage = 0x1910A000 bhash = 0x00000000 bpageno = (1:14545)
bdbid = 5 breferences = 0 bUse1 = 13392
bstat = 0x2c0000b blog = 0x32159bb bnext = 0x00000000

PAGE HEADER:


Page @0x1910A000

m_pageId = (1:14545) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012
m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2774:28:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060

Slot 0 Column 0 Offset 0x4 Length 4

id = 15

Slot 0 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452

Slot 1 Column 0 Offset 0x4 Length 4

id = 17

Slot 1 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C844

Slot 2 Column 0 Offset 0x4 Length 4

id = 19

Slot 2 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33CC36

Slot 3 Column 0 Offset 0x4 Length 4

id = 21

Slot 3 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D028

Slot 4 Column 0 Offset 0x4 Length 4

id = 23

Slot 4 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D41A

Slot 5 Column 0 Offset 0x4 Length 4

id = 25

Slot 5 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33D80C

Slot 6 Column 0 Offset 0x4 Length 4

id = 27

Slot 6 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

看一下数据页37037里的数据 只有id为29 和31这两条记录 其中字段m_slotCnt = 2 表示本页面存储了2条记录

PAGE: (1:37037)


BUFFER:


BUF @0x03F83ABC

bpage = 0x1A4E8000 bhash = 0x00000000 bpageno = (1:37037)
bdbid = 5 breferences = 0 bUse1 = 13476
bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000

PAGE HEADER:


Page @0x1A4E8000

m_pageId = (1:37037) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 2 m_freeCnt = 6072
m_freeData = 2116 m_reservedCnt = 0 m_lsn = (2774:35:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060

Slot 0 Column 0 Offset 0x4 Length 4

id = 29

Slot 0 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452

Slot 1 Column 0 Offset 0x4 Length 4

id = 31

Slot 1 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

现在向聚集索引表ClusteredTable插入一条记录,插入id为30的记录

1 INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] )
2 SELECT 30,'xxx',''

<!DOCTYPE HTML>

可以看到新增加了一页37042

我们看一下聚集索引页14623

可以看到聚集索引页增加了一行

我们看一下37036页 ,可以看到该页本身存放8条记录,现在存放6条记录 m_slotCnt = 6

PAGE: (1:37036)


BUFFER:


BUF @0x03F6F5F4

bpage = 0x19CD0000 bhash = 0x00000000 bpageno = (1:37036)
bdbid = 5 breferences = 0 bUse1 = 14314
bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000

PAGE HEADER:


Page @0x19CD0000

m_pageId = (1:37036) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (1:14544) m_nextPage = (1:37042)
pminlen = 1007 m_slotCnt = 6 m_freeCnt = 2024
m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2774:96:9)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 17

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 19

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 21

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 23

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 25

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A

UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 27

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

我们看一下37042页,可以看到m_slotCnt = 3 ,该页面只有3条记录

PAGE: (1:37042)


BUFFER:


BUF @0x03F68738

bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042)
bdbid = 5 breferences = 0 bUse1 = 14543
bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000

PAGE HEADER:


Page @0x1997A000

m_pageId = (1:37042) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (1:37036) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 3 m_freeCnt = 5060
m_freeData = 3126 m_reservedCnt = 0 m_lsn = (2774:96:12)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 29

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C844


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 30

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 31

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

原本一页可以容纳8条记录,现在37036页面只能容纳6条记录,造成还有2条记录的剩余空间没有利用,造成浪费

而且聚集索引页的主键列的id字段的范围值也打乱了,需要增加一行来保持索引键列的有序

其实这也属于页拆分的一种,详细可以看一下宋大侠的文章里面关于内部碎片和外部碎片的说法,这里就不讨论了

T-SQL查询高级—SQL Server索引中的碎片和填充因子

其实这里插入到聚集索引表里的记录是会维护数据页和索引页使得id字段保持有序

id为30这条记录还是插在了id为31的这条记录前面

这样的话,插入一条记录的同时需要维护索引页和数据页所以开销是比较大的

那么堆表是怎样处理数据的插入的呢?

现在向堆表[HeapTable]插入一条记录,插入id为26的记录

INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] )
SELECT 26,'xxx',''

--先清空[DBCCResult]的数据
TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,HeapTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

可以看到什么页面都没有增加

我们看一下数据页37037里的内容 可以看到m_slotCnt = 3 ,说明这个数据页比没有插入数据之前多了一条记录

PAGE: (1:37037)
BUFFER:
BUF @0x03F83ABC

bpage = 0x1A4E8000 bhash = 0x00000000 bpageno = (1:37037)
bdbid = 5 breferences = 0 bUse1 = 15670
bstat = 0x2c0000b blog = 0x212121bb bnext = 0x00000000

PAGE HEADER:
Page @0x1A4E8000

m_pageId = (1:37037) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 3 m_freeCnt = 5060
m_freeData = 3126 m_reservedCnt = 0 m_lsn = (2774:121:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060

Slot 0 Column 0 Offset 0x4 Length 4

id = 29

Slot 0 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452

Slot 1 Column 0 Offset 0x4 Length 4

id = 31

Slot 1 Column 1 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C844

Slot 2 Column 0 Offset 0x4 Length 4

id = 26

Slot 2 Column 1 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]


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

按道理,如果按照id字段的顺序插入记录的话,id为26的这条记录应该插入到数据页14545 里id为25的那条记录的后面,但是堆表的结果并非这样

他插入到了数据页37037的id为31的那条记录的后面

这说明堆表的记录插入只会插入到堆中的最后一个数据页里的最后,所以插入数据基本上没有开销

而园子里有人针对聚集索引表作出下面讨论:

插入是会分配新的page,而不是在原来的有碎片的page里面插入数据

只有说经常更新行打小不固定的表的时候,碎片空隙保持一定的冗余,防止页拆分Shiite有一定好处的

不过我不认同您对数据插入一定会分配新的页的说法,当页内空间足够是不用分配新的页的。如果每插入一条数据分配一个新的页。

那岂不是一个100W行的表会有100W页?那SQL Server可以退出历史舞台了.....

可以看到刚才我在聚集索引表里插入id为30的那条记录,结果造成分配了一个新的page,为什麽??因为原本只能容纳8条记录的页面

不能再容纳新的记录了,只能够分配一个新的page,分配的时候也造成了页拆分

而插入是不是一定会分配新的page呢?

证明:证明的方法很简单,只需要在数据页37042里再插入一条记录,看会不会分配新的page就可以了

数据页37042现在有3条记录,id分别为29,30,31

现在插入id为32这条记录

INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] )
2 SELECT 32,'xxx',''

SELECT  [index_id], [index_type_desc], [alloc_unit_type_desc], [index_depth],
[index_level], [avg_fragmentation_in_percent], [fragment_count],
[page_count]
FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'),
OBJECT_ID('pratice.dbo.ClusteredTable'),
NULL, NULL, NULL)
--先清空[DBCCResult]表的数据
TRUNCATE TABLE [dbo].[DBCCResult]
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

看一下数据页37042这个页面 m_slotCnt = 4 说明37042这个页面有4条记录

PAGE: (1:37042)


BUFFER:


BUF @0x03F68738

bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042)
bdbid = 5 breferences = 0 bUse1 = 16744
bstat = 0x2c0000b blog = 0x21bbbbbb bnext = 0x00000000

PAGE HEADER:


Page @0x1997A000

m_pageId = (1:37042) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (1:37036) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 4 m_freeCnt = 4048
m_freeData = 4136 m_reservedCnt = 0 m_lsn = (2774:138:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 29

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C844


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 30

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx


col3 = [NULL]

Slot 2 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33C452


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 31

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x0A33CC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 32

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

现在我们插入6条记录,让数据超过数据页面本身的承载能力,让他分配新的数据页

INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] )
SELECT 33,'xxx','' UNION ALL
SELECT 34,'xxx','' UNION ALL
SELECT 35,'xxx','' UNION ALL
SELECT 36,'xxx','' UNION ALL
SELECT 37,'xxx',''

聚集索引页面

从上面两张图可以看到,分配了一个新的数据页14537,因为数据页37042已经超过8条记录了,所以必须要分配一个新的数据页,同时聚集索引页也会增加一条记录

数据页14537的内容,m_slotCnt = 1 只有一条记录

PAGE: (1:14537)


BUFFER:


BUF @0x03F79EE0

bpage = 0x1A16E000 bhash = 0x00000000 bpageno = (1:14537)
bdbid = 5 breferences = 0 bUse1 = 17088
bstat = 0x2c0000b blog = 0x159bbbbb bnext = 0x00000000

PAGE HEADER:


Page @0x1A16E000

m_pageId = (1:14537) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 390 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063486976
Metadata: PartitionId = 72057594054246400 Metadata: IndexId = 1
Metadata: ObjectId = 427148567 m_prevPage = (1:37042) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 1 m_freeCnt = 7084
m_freeData = 1106 m_reservedCnt = 0 m_lsn = (2774:212:10)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 37

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

堆表跟聚集索引表一样,当数据页面不能承载页面本身的数据量的时候就会分配一个新的数据页,由于篇幅关系,这里就不测试了

那么重组索引/重建索引对于上面解决那个页拆分有没有帮助呢? 对于数据页37036,页面数据还没有填满的情况

我们先使用重组索引

其实对于数据页37036的情况,我们也可以理解为在数据页37036里删除了两条记录,使得数据页37036里的记录数不足8条

很多书本上说,如果表空间还剩下很多的话,最好在表上建立一个聚集索引,然后重组索引,重组索引之后一些数据页面剩余的页面空间

就可以利用起来

先重组一下索引

USE [pratice]
2 GO
3 ALTER INDEX [CIX] ON ClusteredTable REORGANIZE

我们看一下重组索引之后,数据页和聚集索引页面变成什么样子了???

--先清空[DBCCResult]表的数据
2 TRUNCATE TABLE [dbo].[DBCCResult]
3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,ClusteredTable,-1) ')
4
5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

聚集索引页面

可以看到数据页14537这个页面不见了,聚集索引页的索引键列(id)的范围值重新排序了

因为刚才在数据页37036里遭遇了页拆分,所以这里只需要查看数据页37036和数据页37042里的内容就可以了

数据页37036,可以看到 m_slotCnt = 8 ,数据页37036里的记录又填充满了,id的范围从17~30

PAGE: (1:37036)


BUFFER:


BUF @0x03F6F5F4

bpage = 0x19CD0000                   bhash = 0x00000000                   bpageno = (1:37036)
bdbid = 5                            breferences = 0                      bUse1 = 18572
bstat = 0x2c0000b                    blog = 0x212121bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x19CD0000

m_pageId = (1:37036)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 390    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594063486976                                 
Metadata: PartitionId = 72057594054246400                                 Metadata: IndexId = 1
Metadata: ObjectId = 427148567       m_prevPage = (1:14544)               m_nextPage = (1:37042)
pminlen = 1007                       m_slotCnt = 8                        m_freeCnt = 0
m_freeData = 8176                    m_reservedCnt = 0                    m_lsn = (2774:232:3)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                     DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC060

                
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 17                              

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                             
col3 = [NULL]                        

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC452

                
UNIQUIFIER = [NULL]                  

Slot 1 Column 1 Offset 0x4 Length 4

id = 19                              

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC844

                    
UNIQUIFIER = [NULL]                  

Slot 2 Column 1 Offset 0x4 Length 4

id = 21                              

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CCC36

                    
UNIQUIFIER = [NULL]                  

Slot 3 Column 1 Offset 0x4 Length 4

id = 23                              

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                    
col3 = [NULL]                        

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD028

                     
UNIQUIFIER = [NULL]                  

Slot 4 Column 1 Offset 0x4 Length 4

id = 25                              

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD41A

                   
UNIQUIFIER = [NULL]                  

Slot 5 Column 1 Offset 0x4 Length 4

id = 27                              

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                               
col3 = [NULL]                        

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD80C

                    
UNIQUIFIER = [NULL]                  

Slot 6 Column 1 Offset 0x4 Length 4

id = 29                              

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                   
col3 = [NULL]                        

Slot 7 Offset 0x1bfe Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CDBFE

                    
UNIQUIFIER = [NULL]                  

Slot 7 Column 1 Offset 0x4 Length 4

id = 30                              

Slot 7 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        


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

数据页37042,可以看到 m_slotCnt = 7,id的范围从31~37

PAGE: (1:37042)


BUFFER:


BUF @0x03F68738

bpage = 0x1997A000                   bhash = 0x00000000                   bpageno = (1:37042)
bdbid = 5                            breferences = 0                      bUse1 = 18756
bstat = 0x2c0000b                    blog = 0x21bbbbbb                    bnext = 0x00000000

PAGE HEADER:


Page @0x1997A000

m_pageId = (1:37042)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 390    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594063486976                                 
Metadata: PartitionId = 72057594054246400                                 Metadata: IndexId = 1
Metadata: ObjectId = 427148567       m_prevPage = (1:37036)               m_nextPage = (0:0)
pminlen = 1007                       m_slotCnt = 7                        m_freeCnt = 1012
m_freeData = 7166                    m_reservedCnt = 0                    m_lsn = (2774:232:12)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                     DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC060

                     
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 31                              

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                    
col3 = [NULL]                        

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC452

                    
UNIQUIFIER = [NULL]                  

Slot 1 Column 1 Offset 0x4 Length 4

id = 32                              

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                               
col3 = [NULL]                        

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC844

                    
UNIQUIFIER = [NULL]                  

Slot 2 Column 1 Offset 0x4 Length 4

id = 33                              

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CCC36

                     
UNIQUIFIER = [NULL]                  

Slot 3 Column 1 Offset 0x4 Length 4

id = 34                              

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                 
col3 = [NULL]                        

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD028

                     
UNIQUIFIER = [NULL]                  

Slot 4 Column 1 Offset 0x4 Length 4

id = 35                              

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                   
col3 = [NULL]                        

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD41A

                    
UNIQUIFIER = [NULL]                  

Slot 5 Column 1 Offset 0x4 Length 4

id = 36                              

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                     
col3 = [NULL]                        

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CD80C

                   
UNIQUIFIER = [NULL]                  

Slot 6 Column 1 Offset 0x4 Length 4

id = 37                              

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                   
col3 = [NULL]                        


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

重组索引之后无论是数据页还是索引页,里面的数据都按照id列的顺序填充满并排好序了那么“重组索引之后一些数据页面剩余的页面空间就可以利用起来”是真的

那么重建索引呢??跟重组索引有什么区别??

先drop掉ClusteredTable表,然后又创建ClusteredTable表,插入数据,建表和建索引的步骤跟文章开头一样,这里就不写了

按照前面的做法,令第二个数据页面只剩下两条记录没有填满,这里由于篇幅关系就不写了,文章开头都有步骤

下面这幅图是我插入了id为30的记录之后,ClusteredTable表中存在的索引页和数据页,可以看到增加了一个数据页37036

可以看到数据页37036里的内容, m_slotCnt = 3 记录有3条,id分别为:29,30,31

PAGE: (1:37036)


BUFFER:


BUF @0x03F6F5F4

bpage = 0x19CD0000                   bhash = 0x00000000                   bpageno = (1:37036)
bdbid = 5                            breferences = 0                      bUse1 = 19590
bstat = 0x2c0000b                    blog = 0x21bbbbbb                    bnext = 0x00000000

PAGE HEADER:


Page @0x19CD0000

m_pageId = (1:37036)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 395    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594063814656                                 
Metadata: PartitionId = 72057594054574080                                 Metadata: IndexId = 1
Metadata: ObjectId = 491148795       m_prevPage = (1:14544)               m_nextPage = (0:0)
pminlen = 1007                       m_slotCnt = 3                        m_freeCnt = 5060
m_freeData = 3126                    m_reservedCnt = 0                    m_lsn = (2775:67:32)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                     DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC060

                    
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 29                              

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                              
col3 = [NULL]                        

Slot 1 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC844

                      
UNIQUIFIER = [NULL]                  

Slot 1 Column 1 Offset 0x4 Length 4

id = 30                              

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        

Slot 2 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC452

                    
UNIQUIFIER = [NULL]                  

Slot 2 Column 1 Offset 0x4 Length 4

id = 31                              

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                 
col3 = [NULL]                        


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

再插入6条记录

INSERT INTO [dbo].[ClusteredTable] ( [id], [col2], [col3] )
SELECT 33,'xxx','' UNION ALL
SELECT 34,'xxx','' UNION ALL
SELECT 35,'xxx','' UNION ALL
SELECT 36,'xxx','' UNION ALL
SELECT 37,'xxx','' UNION ALL
SELECT 38,'xxx',''

可以看到多了一个数据页面14539

数据页面14539只有一条记录,m_slotCnt = 1

PAGE: (1:14539)


BUFFER:


BUF @0x03F79CCC

bpage = 0x1A17C000                   bhash = 0x00000000                   bpageno = (1:14539)
bdbid = 5                            breferences = 0                      bUse1 = 20082
bstat = 0x2c0000b                    blog = 0x212159bb                    bnext = 0x00000000

PAGE HEADER:


Page @0x1A17C000

m_pageId = (1:14539)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 395    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594063814656                                 
Metadata: PartitionId = 72057594054574080                                 Metadata: IndexId = 1
Metadata: ObjectId = 491148795       m_prevPage = (1:37036)               m_nextPage = (0:0)
pminlen = 1007                       m_slotCnt = 1                        m_freeCnt = 7084
m_freeData = 1106                    m_reservedCnt = 0                    m_lsn = (2775:120:10)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                      DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x095CC060

                  
UNIQUIFIER = [NULL]                  

Slot 0 Column 1 Offset 0x4 Length 4

id = 38                              

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx                                                                                                       
                                                                                                  
col3 = [NULL]                        


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

现在来重建一下索引

USE [pratice]
2 GO
3 ALTER INDEX [CIX] ON ClusteredTable REBUILD

我们看一下重组索引之后,数据页和聚集索引页面变成什么样子了???

数据页14539这个页面不见了,更重要的是原本4个数据页面现在只有3个,还有大家留意一下索引页和数据页的pageid,跟之前完全不一样

我们看一下聚集索引页面和数据页面有什么变化

聚集索引页面

数据页14623, m_slotCnt = 8

PAGE: (1:14623)


BUFFER:


BUF @0x03F692CC

bpage = 0x19A0E000 bhash = 0x00000000 bpageno = (1:14623)
bdbid = 5 breferences = 0 bUse1 = 20925
bstat = 0x2c0000b blog = 0x21bbbbcb bnext = 0x00000000

PAGE HEADER:


Page @0x19A0E000

m_pageId = (1:14623) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063880192
Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1
Metadata: ObjectId = 491148795 m_prevPage = (0:0) m_nextPage = (1:37042)
pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0
m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2775:140:33)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 1

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 3

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 5

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 7

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 9

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A


UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 11

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD80C


UNIQUIFIER = [NULL]

Slot 6 Column 1 Offset 0x4 Length 4

id = 13

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 7 Offset 0x1bfe Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CDBFE


UNIQUIFIER = [NULL]

Slot 7 Column 1 Offset 0x4 Length 4

id = 15

Slot 7 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

数据页37042, m_slotCnt = 8

PAGE: (1:37042)


BUFFER:


BUF @0x03F68738

bpage = 0x1997A000 bhash = 0x00000000 bpageno = (1:37042)
bdbid = 5 breferences = 0 bUse1 = 21031
bstat = 0x2c0000b blog = 0xbbbbbbcb bnext = 0x00000000

PAGE HEADER:


Page @0x1997A000

m_pageId = (1:37042) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063880192
Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1
Metadata: ObjectId = 491148795 m_prevPage = (1:14623) m_nextPage = (1:13572)
pminlen = 1007 m_slotCnt = 8 m_freeCnt = 0
m_freeData = 8176 m_reservedCnt = 0 m_lsn = (2775:140:42)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 17

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 19

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 21

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 23

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 25

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A


UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 27

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD80C


UNIQUIFIER = [NULL]

Slot 6 Column 1 Offset 0x4 Length 4

id = 29

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 7 Offset 0x1bfe Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CDBFE


UNIQUIFIER = [NULL]

Slot 7 Column 1 Offset 0x4 Length 4

id = 30

Slot 7 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

 

数据页14623, m_slotCnt = 7

PAGE: (1:13572)


BUFFER:


BUF @0x03F369D0

bpage = 0x1845C000 bhash = 0x00000000 bpageno = (1:13572)
bdbid = 5 breferences = 0 bUse1 = 21131
bstat = 0x3c0000b blog = 0x159bbbcb bnext = 0x00000000

PAGE HEADER:


Page @0x1845C000

m_pageId = (1:13572) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 396 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063880192
Metadata: PartitionId = 72057594054639616 Metadata: IndexId = 1
Metadata: ObjectId = 491148795 m_prevPage = (1:37042) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 7 m_freeCnt = 1012
m_freeData = 7166 m_reservedCnt = 0 m_lsn = (2775:140:44)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:8088) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC060


UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

id = 31

Slot 0 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x452 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC452


UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

id = 33

Slot 1 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 2 Offset 0x844 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CC844


UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

id = 34

Slot 2 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 3 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CCC36


UNIQUIFIER = [NULL]

Slot 3 Column 1 Offset 0x4 Length 4

id = 35

Slot 3 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 4 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD028


UNIQUIFIER = [NULL]

Slot 4 Column 1 Offset 0x4 Length 4

id = 36

Slot 4 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 5 Offset 0x141a Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD41A


UNIQUIFIER = [NULL]

Slot 5 Column 1 Offset 0x4 Length 4

id = 37

Slot 5 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 6 Offset 0x180c Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x095CD80C


UNIQUIFIER = [NULL]

Slot 6 Column 1 Offset 0x4 Length 4

id = 38

Slot 6 Column 2 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

从上面数据页面和聚集索引页面的结果看,重建索引和重组索引基本上是一样的效果,只是,重建索引会把索引页和数据页删除,然后分配新的索引页和数据页

所以重建索引开销还是比较大的

但是重建索引是不是一定比重组索引的开销大呢?

本人觉得:相比重组索引,重建索引是分配新的数据页和索引页,把旧的数据页里的数据按索引字段 排好序,再放进去新的数据页里

而重组索引是把旧的数据页里的数据有页拆分的页面填充满,这样会造成数据页之间的数据移动,效率可能会比重建索引差一点

大家可以使用SET STATISTICS TIME ON来统计一下重组索引和重建索引所用的时间,由于篇幅问题我这里就不测试了o(∩_∩)o

SET STATISTICS TIME ON
USE [pratice]
GO
ALTER INDEX [CIX] ON ClusteredTable REBUILD
---------------------------------------------------
SET STATISTICS TIME ON
USE [pratice]
GO
ALTER INDEX [CIX] ON ClusteredTable REORGANIZE

这里宋大侠有一个建议:

当[dm_db_index_physical_stats]DMV里的[avg_fragmentation_in_percent]字段大于30%的时候使用索引重建

当[avg_fragmentation_in_percent]等于小于30%的时候使用索引重组

[avg_fragmentation_in_percent]>30%就重建索引

[avg_fragmentation_in_percent]<=30%就重组索引

SELECT  [avg_fragmentation_in_percent]
2 FROM [sys].[dm_db_index_physical_stats](DB_ID('pratice'),
3 OBJECT_ID('pratice.dbo.ClusteredTable'),
4 NULL, NULL, NULL)

文章写完了,本人知道还有不足的地方

还是那一句:

如有不对的地方,欢迎强烈拍砖哦o(∩_∩)o

2013-7-25 补充

我把堆表HeapTable里的数据页37037里的3条记录全部删除,现在数据页37037里一条记录都没有但是数据页37037并没有释放掉

1 DELETE FROM [dbo].[HeapTable] WHERE [id] IN(29,31,26)

PAGE: (1:37037)


BUFFER:


BUF @0x03E5E684

bpage = 0x19B4C000 bhash = 0x00000000 bpageno = (1:37037)
bdbid = 5 breferences = 0 bUse1 = 4284
bstat = 0x1c0000b blog = 0x3212159 bnext = 0x00000000

PAGE HEADER:


Page @0x19B4C000

m_pageId = (1:37037) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 1 m_freeCnt = 8094
m_freeData = 3126 m_reservedCnt = 1010 m_lsn = (2776:34:2)
m_xactReserved = 1010 m_xdesId = (0:6906025) m_ghostRecCnt = 0
m_tornBits = -1598612873

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED


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

在徐海蔚老师的《SQLSERVER企业级平台管理》第26页里说到:

我delete了数据,SQLSERVER却没有完全释放空间,这不是空间泄漏吗?久而久之,我的数据库里会不会充斥着这些“没用”的页面,

把我的空间都浪费掉了?这倒不必担心,虽然这些页面没有被释放掉,但当表格里插入新的数据时,这些页面是会被重新使用的。

所以这些页面并没有被“泄漏”掉,会留给SQLSERVER重用的

如果真的看着这些页面碍事,而表格又不能整个删除掉,处理起来有时候倒有点费事。如果表格有聚集索引,重建/重组一下索引就能

释放这些页面,还是挺简单的。但是如果没有,可能就要重建一张新表,把数据从旧表里倒过去,然后再删除旧表,释放空间;或者

在这张表上建立一个聚集索引。所以,如果表没有用了,就直接删除它;如果表还有用,那这些页面将来再有新数据插入时,还会被利用上的。

一般没有必要一定要逼着SQLSERVER把他们释放出来

现在我再向HeapTable表插入2条记录,看数据页37037能不能被重新利用

INSERT INTO [dbo].[HeapTable] ( [id], [col2], [col3] )
2 SELECT 40,'xxx','' UNION ALL
3 SELECT 41,'xxx',''

可以看到id为40和id为41这两条记录被插入到数据页37037中里,数据页37037又被重新利用了o(∩_∩)o

PAGE: (1:37037)


BUFFER:


BUF @0x03E5E684

bpage = 0x19B4C000 bhash = 0x00000000 bpageno = (1:37037)
bdbid = 5 breferences = 0 bUse1 = 5362
bstat = 0x1c0000b blog = 0x3212159 bnext = 0x00000000

PAGE HEADER:


Page @0x19B4C000

m_pageId = (1:37037) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 391 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594063552512
Metadata: PartitionId = 72057594054311936 Metadata: IndexId = 0
Metadata: ObjectId = 443148624 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 1007 m_slotCnt = 2 m_freeCnt = 6072
m_freeData = 5146 m_reservedCnt = 0 m_lsn = (2776:51:4)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -1598612873

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:32352) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0xc36 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x09DACC36

Slot 0 Column 0 Offset 0x4 Length 4

id = 40

Slot 0 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]

Slot 1 Offset 0x1028 Length 1010

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x09DAD028

Slot 1 Column 0 Offset 0x4 Length 4

id = 41

Slot 1 Column 1 Offset 0x8 Length 999

col2 = xxx

col3 = [NULL]


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

相关文章

基于EA的数据库建模
数据流建模(EA指南)
“数据湖”:概念、特征、架构与案例
在线商城数据库系统设计 思路+效果
 
相关文档

Greenplum数据库基础培训
MySQL5.1性能优化方案
某电商数据中台架构实践
MySQL高扩展架构设计
相关课程

数据治理、数据架构及数据标准
MongoDB实战课程
并发、大容量、高性能数据库设计与优化
PostgreSQL数据库实战培训
 
分享到
 
 


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...