1

I used the ghost_record_count column to find indexes/partitions that have ghost records. I found a few that have one such record. I then tried to queue them up for processing by scanning all pages:

SELECT COUNT_BIG(*)
FROM T WITH (READUNCOMMITTED, INDEX(idx))
OPTION (MAXDOP 1)

I then waited at least 10 seconds to let the ghost cleanup task run. But the ghost records do not disappear. I also tried restarting the server. No ghost related trace flags are in use.

This is not an actual problem that I'm having. I'm trying to understand ghost cleanup in general. Why do the counters not drop to zero in tables that have no writes?

0

You can find the answer in this article Inside the Storage Engine: Ghost cleanup in depth

When a record is deleted, apart from it being marked as a ghost record, the page that the record is on is also marked as having ghost records in one of the allocation maps – the PFS page (post coming soon!) – and in its page header. Marking a page as having ghost records in a PFS page also changes the database state to indicate that there are some ghost records to cleanup – somewhere. Nothing tells the ghost cleanup task to clean the specific page that the delete happened on – yet. That only happens when the next scan operation reads the page and notices that the page has ghost records.

So it's not cleaned up until the page is read again

  • That's why the OP is using a COUNT_BIG with index hint. To force all pages in the index to be scanned. – Martin Smith Jun 2 '17 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.