In SQL Server, when a record is deleted or when an update causes a row to move pages, SQL Server doesn’t always immediately remove data from pages. Instead, there is an internal mechanism to mark the row for deletion and remove the data later. These leftover rows are called “ghost records”, i.e. they physically exist but aren’t visible to queries.
This series of posts will cover the basics of why the process exists, how it works, and an interesting corruption issue we found on an instance of SQL 2016 SP2.
Given the depth and technical nature of this topic, it has been broken down into a series of posts. This is post one of the series:
- Post 1 – Ghosts! – Ghost Records and the Ghost Cleanup Process
- Post 2 – Seeing the ghost cleanup process in action
- Post 3 – Let the ghosts linger
- Post 4 – Ghosting in the transaction log
- Post 5 – The ghostly case of index corruption
- Post 6 – Questions answered
How and Why Ghost Records?
When a delete operation is executed, or when an update causes a page split, a marker is set in the page header, and each row is marked to show that the record is to be deleted; this is the process of creating ghost records. The concept is to improve performance as the data is not immediately removed, and, with row-level locking, it helps minimise blocking and supports efficient concurrency under typical isolation levels such as Read Committed and above. If a transaction is later rolled back, the cost of effectively restoring the row is considerably reduced as the data doesn’t need to be reinserted, only the marker removed. Later demos will show this in action.
Snapshot isolation relies on row versioning to maintain older versions of data; while related, this mechanism is separate from standard ghost record handling and therefore beyond the scope of this post.
Ghost Cleanup Process
As records are deleted or if an update causes them to move pages, a marker is applied at the row level, reflected in the page header, and recorded in the PFS (page free space) page. These markers will be explored in more depth within a future post.
Once records have been marked as ghosted, a background process is responsible for removing them, known as the Ghost Cleanup Task:
- The Ghost Cleanup Task periodically scans for databases flagged as containing ghost records.
- The PFS (Page Free Space) pages are used to identify data/index pages that may contain ghost records.
- Pages identified as containing ghost records are examined, and rows marked for deletion are processed.
- Records are physically removed once all open transactions have committed.
- As records are removed, the data page is updated and, if necessary, the corresponding PFS entries are modified to reflect the change in free space and ghost status.
From SQL Server 2012, the process occurs every 5 seconds, clearing up to 10 pages at a time; this is to ensure the process doesn’t monopolise resources.
The ghost cleanup worker uses cooperative scheduling, so it voluntarily yields as necessary, minimising lock escalation, as page modifications require an exclusive latch while ghost records are physically removed. Consequentially, any database that cannot provide a shared lock will be skipped and its processing attempted on the next run.
Summary
Ghost records are rows that have been logically deleted but not yet physically removed from data pages. SQL Server marks these rows using metadata at the row, page, and database levels, allowing deletes to complete quickly and efficiently. A background task, the ghost cleanup process, later removes these ‘ghosted’ rows once they are no longer needed.
Ghosting is an invisible process for performance optimisation that allows SQL Server to defer expensive physical deletes.
Further Reading
Keep an eye out for post 2 coming next week with the demo code, which shows the ghost cleanup process in action and how to monitor it.