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.
-- 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');
GOThe 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.)
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:
-- 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):
-- now, delete the first 12 records
DELETE FROM dbo.Test WHERE RowID <= 12;
GO
-- force the Ghost Cleanup process
DBCC ForceGhostCleanup;
GOOnce again, check to see what pages are used by this table:
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:
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.
/*
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:
/*
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:
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:
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.
