okbangas (1/15/2013)
As far as I understand it, there is something missing from the explanation. If we assume that only what's written in the scenario has happened, the scenario is as follows:* Table is created
* Records are inserted
* Records are deleted -> Marked as ghost
* Ghost Cleanup Task is running (every 5 seconds).
But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.
The ghost cleanup task doesn't just start up when it's told to – it starts up in the background every 5 seconds and looks for ghost records to cleanup. Remember that it won't be told to go cleanup a specific page by a delete operation – it's a subsequent scan that does it, if a scan happens.
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/[/url]
You're absolutely correct. However, the question states:
If you delete the first 12 records, how many data pages will the table have after the Ghost Cleanup process has been run against this table?
It's not going to run against this table until something scans this table, but once it does, it will leave that page with only one record in it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes