Understanding VDB Size Under Microsoft SQL Server Ghost Cleanup Process (KBA1033)

Ghost Cleanup:  

It is an asynchronous, independent background process designed to remove ghost records from index pages with minimal impact to database system. It requires very little system resources, thus increasing the performance on very busy database servers.

Ghost Record:  

A ghost record is a record that has just been deleted from an index leaf page. To improve performance, SQL Server never physically removes this record from index pages, instead, it marks them as deleted or "ghosted" (soft delete). It is an efficient way of handling deletes and rollbacks, since physical delete operation takes considerable CPU, Memory and I/O.

Ghost Cleanup Process:  

Apart from marking the ghost records, SQL Server also chronicles in index page headers the presence of ghost records. The ghost cleanup background process starts every 5 seconds and scans pages for ghost records. If any are detected, the process physically removes those ghost records and updates the header (the process only scans 10 pages at a time). Once the process runs through 10 pages it stops itself, this limit is in place so that the ghost cleanup process will not overwhelm system resources. All ghost records will eventually be removed by this background process and depending on the change rate and size it may take several hours.

From a SQL Server perspective the whole operation is transparent and therefore there won't be any change in database size. After removing ghost records from a page, the page size still remains the same, except now it has some free space.

However Delphix works on a physical data block level which means that Delphix does not recognize SQL Server logical structures. When a ghost record is removed from a page, it translates into block change to Delphix. Since Delphix tracks all changes to source blocks, it makes a copy of the changed block resulting in the size increase to the VDB or dSource.

In cases where a dSource was created from a full database backup taken right after index maintenance, all ghost records are carried over from production to the dSource. When a VDB is created from this dSource, the ghost records are exposed and the target SQL Server ghost cleanup process starts deleting them. This in turn appears as changes to VDB data block and Delphix captures them as a change delta.