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.

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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.)

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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:[codesyntax lang=”tsql” strict=”yes”]

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):

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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.
[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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

[codesyntax lang=”tsql” strict=”yes” highlight_lines=”14″]

[/codesyntax]

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:

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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:

[codesyntax lang=”tsql” strict=”yes”]

[/codesyntax]

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.