Sql-server – Why would a single ghost record remain in a few random indexes

sql serversql server 2014

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?

Best Answer

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

Related Question