http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/12/17/ghost-cleanup-process/

Printed 2014/10/21 06:18AM

Ghost Cleanup process

By Wayne Sheffield, 2012/12/17

I’ve been digging deeper into the Ghost Cleanup process recently, and quite naturally my quest lead to Paul Randal’s blog. He has a post about the Ghost Cleanup process in depth, and in this post is this line:

The ghost cleanup task will leave a single record on the page to avoid having to deallocate empty data or index pages.

Hmmm… interesting. And if you know me, you know that I just have to investigate this a little bit deeper.

Let’s start off by creating a table in tempdb. I’ll size it so that it will hold 10 records per page, and then insert 15 rows into this table.

Source code   
-- use a database that everyone has
USE tempdb;
GO
 
-- Create a table that holds 10 records per page.
-- 11 records of 736 bytes will just fit into one page,
-- 10 records of 809.6 bytes will just fit into one page,
-- so make each record size somewhere between these values.
CREATE TABLE Test (
    RowID INT CONSTRAINT PK_Test PRIMARY KEY CLUSTERED, 
    Col1 CHAR(750)
);
 
GO
 
-- Insert 15 records into the table.
INSERT INTO Test (RowID, Col1)
VALUES (1, 'One'),
       (2, 'Two'),
       (3, 'Three'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six'),
       (7, 'Seven'),
       (8, 'Eight'),
       (9, 'Nine'),
       (10, 'Ten'),
       (11, 'Eleven'),
       (12, 'Twelve'),
       (13, 'Thirteen'),
       (14, 'Fourteen'),
       (15, 'Fifteen');
GO

The next step is to see what pages are used by this table. To do this, we’ll use the undocumented command DBCC IND. (Paul Randal blogged about this (and DBCC PAGE) while he was still working at Microsoft.)

Source code   
DBCC IND (tempdb, 'Test', 1);

What we’re interested in are the rows where PageType = 1 (data pages). On my system, the first page is 374, and the last page is 361. We can see the contents of these pages by running the following statements:

Source code   
-- first, set trace flag 3604 on so that the output will go to the console
DBCC TRACEON (3604);
DBCC PAGE (tempdb, 1, 374, 3);
DBCC PAGE (tempdb, 1, 361, 3);

All fifteen records are seen, so now we can test what happens when we delete all of the records in a page (and we’ll force the Ghost Cleanup process to run by using another undocumented DBCC command):

Source code   
-- now, delete the first 12 records
DELETE FROM dbo.Test WHERE RowID <= 12;
GO
-- force the Ghost Cleanup process
DBCC ForceGhostCleanup;
GO

Once again, check to see what pages are used by this table:

Source code   
DBCC IND (tempdb, 'Test', 1);

And here we can see that both pages are still there. Let’s look at what is actually on these pages:

Source code   
DBCC TRACEON (3604);
DBCC PAGE (tempdb, 1, 374, 3);
DBCC PAGE (tempdb, 1, 361, 3);

And here we can see that on page 374, that record 10 is still there, but it’s type is GHOST_DATA_RECORD. It has also been moved up in the slot array from position 9 to position 0.

Source code   
/*
Slot 0 Offset 0x1b21 Length 761
 
Record Type = GHOST_DATA_RECORD     Record Attributes =  NULL_BITMAP    Record Size = 761
 
Memory Dump @0x000000000EF39B21
 
0000000000000000:   1c00f602 0a000000 54656e20 20202020 20202020  ..ö.....Ten         
*/

And at the top of the page, we can see that the page is tracking 1 ghost record:

Source code   
/*
PAGE HEADER:
 
Page @0x000000033B58C000
 
m_pageId = (1:374)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 3743834                                    m_indexId (AllocUnitId.idInd) = 26368
Metadata: AllocUnitId = 7421932431262482432                              
Metadata: PartitionId = 5260204593162813440                              Metadata: IndexId = 1
Metadata: ObjectId = 309576141      m_prevPage = (0:0)                  m_nextPage = (1:361)
pminlen = 758                       m_slotCnt = 1                       m_freeCnt = 7333
m_freeData = 7706                   m_reservedCnt = 0                   m_lsn = (0:1028339:0)
m_xactReserved = 0                  m_xdesId = (0:899786)               m_ghostRecCnt = 1m_tornBits = 0                      DB Frag ID = 1                      
*/

So, how do we go about getting rid of these pages with only ghost records in them, and the ghost records themselves? You need to rebuilt the index:

Source code   
ALTER INDEX PK_Test ON dbo.Test REBUILD;

Now when you run DBCC IND, you get just one page with data – and since the remaining data fits onto one page, you also get rid of the index page:

Source code   
DBCC IND (tempdb, 'Test', 1);

And if you look at that page (for me, page 371), you will see that in the page header the ghost record count is zero, and the only rows on that page are for records 13-15.

So, while I didn’t expect to find anything wrong in what Paul wrote, it sure is nice seeing it actually play out with a simple test.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.