How does ghost cleanup process work internally?

Question

I wanted to understand when a record is updated/deleted in 8-kb page, what happens to old record? I know its gets cleaned up by ghost cleanup process - but what method does it use for this?

Some possible ways I can think of -

All clean data from dirty page gets copied to new page and then it gets written to disk, old page is deallocated?
Data in page is adjusted after ghost process physically clears up old record then and there?
That internal cavity remains and only adjusted at the time of index maintenance?

One more relevant question comes in mind is - When maintaining versions of that record, does it create whole page for every single version of record or only append new record in same page? If so, what would happen if the page is full and needed to split, would it link both pages with pointers?

How it actually works?

Answer 1

Hi @Asif Ali ,

If checkpoint exist, checkpoint flushes only the dirty pages, and if SQL Server use the lazy write, it can flush the dirty and clean pages for free space, and can remove the clean pages

Ghost cleanup process deletes records off of pages that have been marked for deletion, the ghost cleanup task cannot physically delete the ghost records until after the delete transaction commits, because the deleted records are locked and the locks are not released until the transaction commits.

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
https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you are interested in the way the SQL Server engine works, I suggest you read the book "SQL Server Internals".