ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ghost record of sql server

ghost record of sql server

原创 Linux操作系统 作者:hegenhua 时间:2012-02-25 00:09:50 0 删除 编辑

 

test 1, clustered table
a,create the following clustered table and insert 5 records into it:
create table smallrows
(
 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)
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:
dbcc traceon(3604)
go
dbcc page(test,1,78,1)
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
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                      
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           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
OFFSET TABLE:
Row - Offset                        
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.

delete from smallrows where a=3
dbcc traceon(3604)
go
dbcc page(test,1,78,1)
result:
(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
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                      
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           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
OFFSET TABLE:
Row - Offset                        
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
 
 dbcc traceon(3604)
go
dbcc page(test,1,78,1)
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
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                      
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           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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.

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)
(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
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              
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           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
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† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?† ?  ...           
OFFSET TABLE:
Row - Offset                        
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
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')
 
dbcc ind(test,smallrows,1)
root page:1:22627
 
Dbcc traceon(3604)
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          
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                      
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           
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††††††††††††††††††††††††††  ...           
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††††††††††††††††††††††††††  ...           
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††††††††††††††††††††††††††  ...           
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††††††††††††††††††††††††††  ...           
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††††††††††††††††††††††††††  ...           
OFFSET TABLE:
Row - Offset                        
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)

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          
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                      
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           
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††††††††††††††††††††††††††  ...           
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
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 21
Memory Dump @0x000000001010A09F
0000000000000000:   10001200 04000000 726f7720 34202020 †........row 4   
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††††††††††††††††††††††††††  ...           
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)                 

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.
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          
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                      
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           
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)                   
all offsets are 0,and  m_ghostRecCnt is 0,so sql server     
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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/101629/viewspace-717130/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-17

  • 博文量
    24
  • 访问量
    50780