test 1, clustered table
a,create the following clustered table and insert 5 records into it:
a,create the following clustered table and insert 5 records into it:
create table smallrows
(
a int identity primary key,
b char(10)
)
(
a int identity primary key,
b char(10)
)
insert into smallrows values('row 1')
insert into smallrows values('row 2')
insert into smallrows values('row 3')
insert into smallrows values('row 4')
insert into smallrows values('row 5')
b,get the root page of clustered index:
dbcc ind(test,smallrows,1)
insert into smallrows values('row 2')
insert into smallrows values('row 3')
insert into smallrows values('row 4')
insert into smallrows values('row 5')
b,get the root page of clustered index:
dbcc ind(test,smallrows,1)
get the root page of smallrows,the root page is 1:78
c,see what's in the root page of the clustered index:
notice:
there are 5 records(m_slotCnt = 5) in the page and no ghost record at all(m_ghostRecCnt = 0), and record Type are PRIMARY_RECORD:
notice:
there are 5 records(m_slotCnt = 5) in the page and no ghost record at all(m_ghostRecCnt = 0), and record Type are PRIMARY_RECORD:
dbcc traceon(3604)
go
go
dbcc page(test,1,78,1)
result:
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
result:
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:78)
BUFFER:
BUF @0x0000000082FD9100
bpage = 0x0000000082AE2000 bhash = 0x0000000000000000 bpageno = (1:78)
bdbid = 5 breferences = 0 bUse1 = 9671
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
bdbid = 5 breferences = 0 bUse1 = 9671
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000082AE2000
m_pageId = (1:78) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:189:3)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:189: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:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C060
0000000000000000: 10001200 01000000 726f7720 31202020 †.......row 1
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C075
0000000000000000: 10001200 02000000 726f7720 32202020 †.......row 2
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 2, Offset 0x8a, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C08A
0000000000000000: 10001200 03000000 726f7720 33202020 †.......row 3
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 3, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C09F
0000000000000000: 10001200 04000000 726f7720 34202020 †.......row 4
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 4, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C0B4
0000000000000000: 10001200 05000000 726f7720 35202020 †.......row 5
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
OFFSET TABLE:
Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
d, now,I'm going to delete one row from this table and see the contents of the root page again:
notice:
now,in slot 2 ,the record type is GHOST_DATA_RECORD,and m_slotCnt is still
5. This means no record is physically removed from the page,and the record type of slot 2 is GHOST_DATA_RECORD,means this record now is a ghost record.
now,in slot 2 ,the record type is GHOST_DATA_RECORD,and m_slotCnt is still
5. This means no record is physically removed from the page,and the record type of slot 2 is GHOST_DATA_RECORD,means this record now is a ghost record.
delete from smallrows where a=3
dbcc traceon(3604)
go
dbcc page(test,1,78,1)
result:
(1 行受影响)
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
(1 行受影响)
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:78)
BUFFER:
BUF @0x0000000082FD9100
bpage = 0x0000000082AE2000 bhash = 0x0000000000000000 bpageno = (1:78)
bdbid = 5 breferences = 1 bUse1 = 9914
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
bdbid = 5 breferences = 1 bUse1 = 9914
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000082AE2000
m_pageId = (1:78) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:190:2)
m_xactReserved = 0 m_xdesId = (0:559) m_ghostRecCnt = 1
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:190:2)
m_xactReserved = 0 m_xdesId = (0:559) m_ghostRecCnt = 1
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000DE0A060
0000000000000000: 10001200 01000000 726f7720 31202020 †.......row 1
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000DE0A075
0000000000000000: 10001200 02000000 726f7720 32202020 †.......row 2
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 2, Offset 0x8a, Length 21, DumpStyle. BYTE
Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000DE0A08A
0000000000000000: 1c001200 03000000 726f7720 33202020 †.......row 3
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 3, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000DE0A09F
0000000000000000: 10001200 04000000 726f7720 34202020 †.......row 4
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 4, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000DE0A0B4
0000000000000000: 10001200 05000000 726f7720 35202020 †.......row 5
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
OFFSET TABLE:
Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
e,see it again,pls notice m_slotCnt and m_ghostRecCnt,they all equal to 0,why? because the background thread called ghost-cleanup cleaned up the ghost record in the root page,so now,there are only 4 records in the page and ghost record count is 0 :
Information:
we can manually call sp_clean_db_free_space or sp_clean_db_file_free_space
to clean up ghost records. in a particular database or a file of a database.
more detail information can see here:
http://msdn.microsoft.com/en-us/library/dd408731.aspx
we can manually call sp_clean_db_free_space or sp_clean_db_file_free_space
to clean up ghost records. in a particular database or a file of a database.
more detail information can see here:
http://msdn.microsoft.com/en-us/library/dd408731.aspx
dbcc traceon(3604)
go
go
dbcc page(test,1,78,1)
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
PAGE: (1:78)
BUFFER:
BUF @0x0000000082FD9100
bpage = 0x0000000082AE2000 bhash = 0x0000000000000000 bpageno = (1:78)
bdbid = 5 breferences = 0 bUse1 = 10026
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
bdbid = 5 breferences = 0 bUse1 = 10026
bstat = 0x1c0010b blog = 0x59bbbbbb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000082AE2000
m_pageId = (1:78) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 4 m_freeCnt = 8004
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:191:1)
m_xactReserved = 0 m_xdesId = (0:559) m_ghostRecCnt = 0
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 4 m_freeCnt = 8004
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:191:1)
m_xactReserved = 0 m_xdesId = (0:559) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C060
0000000000000000: 10001200 01000000 726f7720 31202020 †.......row 1
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C075
0000000000000000: 10001200 02000000 726f7720 32202020 †.......row 2
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 2, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C09F
0000000000000000: 10001200 04000000 726f7720 34202020 †.......row 4
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 3, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000D58C0B4
0000000000000000: 10001200 05000000 726f7720 35202020 †.......row 5
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
OFFSET TABLE:
Row - Offset
3 (0x3) - 180 (0xb4)
2 (0x2) - 159 (0x9f)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
the ghost record disappeared,because ghost-cleanedup thead reclaimed the space.
3 (0x3) - 180 (0xb4)
2 (0x2) - 159 (0x9f)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
the ghost record disappeared,because ghost-cleanedup thead reclaimed the space.
test 2,delete all rows in the root page of the clusetered index:
notice:
after deleted,all rows in the page are now ghost record,and their offset do not change, m_ghostRecCnt = 4
, m_slotCnt = 4,that means when the background thread ghost-cleanup runs next time ,all these ghost records may will be reclaimed by SQL Server.
delete from smallrows
dbcc traceon(3604)
go
dbcc page(test,1,78,1)
dbcc traceon(3604)
go
dbcc page(test,1,78,1)
(4 行受影响)
PAGE: (1:78)
BUFFER:
BUF @0x0000000084FB0400
bpage = 0x00000000845C8000 bhash = 0x0000000000000000 bpageno = (1:78)
bdbid = 5 breferences = 3 bUse1 = 7185
bstat = 0x1c0000b blog = 0x3212159 bnext = 0x0000000000000000
bdbid = 5 breferences = 3 bUse1 = 7185
bstat = 0x1c0000b blog = 0x3212159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000000845C8000
m_pageId = (1:78) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 4 m_freeCnt = 8004
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:298:7)
m_xactReserved = 0 m_xdesId = (0:569) m_ghostRecCnt = 4
m_tornBits = 362163492
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: bjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 4 m_freeCnt = 8004
m_freeData = 201 m_reservedCnt = 0 m_lsn = (20:298:7)
m_xactReserved = 0 m_xdesId = (0:569) m_ghostRecCnt = 4
m_tornBits = 362163492
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000E23A060
0000000000000000: 1c001200 01000000 726f7720 31202020 †.......row 1
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000E23A075
0000000000000000: 1c001200 02000000 726f7720 32202020 †.......row 2
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 2, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000E23A09F
0000000000000000: 1c001200 04000000 726f7720 34202020 †.......row 4
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
Slot 3, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000E23A0B4
0000000000000000: 1c001200 05000000 726f7720 35202020 †.......row 5
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
0000000000000010: 20200200 00† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ? ...
OFFSET TABLE:
Row - Offset
3 (0x3) - 180 (0xb4)
2 (0x2) - 159 (0x9f)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
3 (0x3) - 180 (0xb4)
2 (0x2) - 159 (0x9f)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
Test 3,delete one row from a heap
--create a heap table
create table smallrows
(
a int identity ,
b char(10)
)
go
--create a heap table
create table smallrows
(
a int identity ,
b char(10)
)
go
ALTER TABLE dbo.smallrows ADD CONSTRAINT PK_smallrows PRIMARY KEY nonCLUSTERED (a)
go
insert into smallrows values('row 1')
insert into smallrows values('row 2')
insert into smallrows values('row 3')
insert into smallrows values('row 4')
insert into smallrows values('row 5')
insert into smallrows values('row 2')
insert into smallrows values('row 3')
insert into smallrows values('row 4')
insert into smallrows values('row 5')
dbcc ind(test,smallrows,1)
root page:1:22627
Dbcc traceon(3604)
Go
Dbcc page(test,1,22627,3)
Go
Go
Dbcc page(test,1,22627,3)
Go
Result:
PAGE: (1:22627)
BUFFER:
BUF @0x0000000088FED880
bpage = 0x0000000088CEC000 bhash = 0x0000000000000000 bpageno = (1:22627)
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 44256 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 44256 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000088CEC000
m_pageId = (1:22627) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0 --heap
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (294:259:3)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0 --heap
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 7981
m_freeData = 201 m_reservedCnt = 0 m_lsn = (294:259: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:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000BAFA060
0000000000000000: 10001200 01000000 726f7720 31202020 †........row 1
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000BAFA075
0000000000000000: 10001200 02000000 726f7720 32202020 †........row 2
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 2, Offset 0x8a, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000BAFA08A
0000000000000000: 10001200 03000000 726f7720 33202020 †........row 3
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 3, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000BAFA09F
0000000000000000: 10001200 04000000 726f7720 34202020 †........row 4
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 4, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000000BAFA0B4
0000000000000000: 10001200 05000000 726f7720 35202020 †........row 5
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
OFFSET TABLE:
Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
delete from smallrows where a=3
Dbcc traceon(3604)
Go
Dbcc page(test,1,22627,1)
Go
(1 row(s) affected)
Result:
PAGE: (1:22627)
PAGE: (1:22627)
BUFFER:
BUF @0x0000000088FED880
bpage = 0x0000000088CEC000 bhash = 0x0000000000000000 bpageno = (1:22627)
bdbid = 7 breferences = 1 bcputicks = 978
bsampleCount = 1 bUse1 = 44422 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
bdbid = 7 breferences = 1 bcputicks = 978
bsampleCount = 1 bUse1 = 44422 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000088CEC000
m_pageId = (1:22627) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8002
m_freeData = 201 m_reservedCnt = 21 m_lsn = (294:310:5)
m_xactReserved = 21 m_xdesId = (0:2444) m_ghostRecCnt = 0
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8002
m_freeData = 201 m_reservedCnt = 21 m_lsn = (294:310:5)
m_xactReserved = 21 m_xdesId = (0:2444) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:16176) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000001010A060
0000000000000000: 10001200 01000000 726f7720 31202020 †........row 1
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 1, Offset 0x75, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000001010A075
0000000000000000: 10001200 02000000 726f7720 32202020 †........row 2
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
--notice here，slot 2 disappeared，but m_slotCnt still equals to 5 and m_ghostRecCnt equals to 0.This means SQL server still thinks there are five records in the page,and there are no ghost record in the page at all,so ,sql server would not reclaim the space on the page.
--
Slot 3, Offset 0x9f, Length 21, DumpStyle. BYTE
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
--notice here，slot 2 disappeared，but m_slotCnt still equals to 5 and m_ghostRecCnt equals to 0.This means SQL server still thinks there are five records in the page,and there are no ghost record in the page at all,so ,sql server would not reclaim the space on the page.
--
Slot 3, Offset 0x9f, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000001010A09F
0000000000000000: 10001200 04000000 726f7720 34202020 †........row 4
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 4, Offset 0xb4, Length 21, DumpStyle. BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x000000001010A0B4
0000000000000000: 10001200 05000000 726f7720 35202020 †........row 5
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
0000000000000010: 20200200 00†††††††††††††††††††††††††† ...
OFFSET TABLE:
Row - Offset
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 0 (0x0) --offset=0,that means this slot doesn’t be taken by a row.
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
4 (0x4) - 180 (0xb4)
3 (0x3) - 159 (0x9f)
2 (0x2) - 0 (0x0) --offset=0,that means this slot doesn’t be taken by a row.
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Test 4,delete all rows in a page of a heap
Delete from smallrows
Dbcc traceon(3604)
Go
Dbcc page(test,1,22627,1)
go
result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:22627)
BUFFER:
BUF @0x0000000088FED880
bpage = 0x0000000088CEC000 bhash = 0x0000000000000000 bpageno = (1:22627)
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 45721 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
bdbid = 7 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 45721 bstat = 0xc0000b
blog = 0x32121bb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000088CEC000
m_pageId = (1:22627) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8086
m_freeData = 201 m_reservedCnt = 84 m_lsn = (294:312:14)
m_xactReserved = 84 m_xdesId = (0:2445) m_ghostRecCnt = 0
m_tornBits = 0
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
Metadata: bjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 5 m_freeCnt = 8086
m_freeData = 201 m_reservedCnt = 84 m_lsn = (294:312:14)
m_xactReserved = 84 m_xdesId = (0:2445) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
OFFSET TABLE:
Row - Offset
4 (0x4) - 0 (0x0)
3 (0x3) - 0 (0x0)
2 (0x2) - 0 (0x0)
1 (0x1) - 0 (0x0)
0 (0x0) - 0 (0x0)
4 (0x4) - 0 (0x0)
3 (0x3) - 0 (0x0)
2 (0x2) - 0 (0x0)
1 (0x1) - 0 (0x0)
0 (0x0) - 0 (0x0)
all offsets are 0,and m_ghostRecCnt is 0,so sql server
will not reclaim this page,and table smallrows still takes this page.
will not reclaim this page,and table smallrows still takes this page.
summary:
1,delete a row/rows in a clustered index,the deleted rows will be marked as ghost records by sql server,so the space taken by these records can be reclaimed by the background thread
ghost-cleanedup.
2,delete a row/rows in a heap table,the deleted rows will not be marked as ghost records by sql server,so sql server can not reclaim these space taken by thoes deleted rcords.
1,delete a row/rows in a clustered index,the deleted rows will be marked as ghost records by sql server,so the space taken by these records can be reclaimed by the background thread
ghost-cleanedup.
2,delete a row/rows in a heap table,the deleted rows will not be marked as ghost records by sql server,so sql server can not reclaim these space taken by thoes deleted rcords.