Delphix users with SQL Server environments sometimes encounter the scenario where new SQL Server virtual databases (VDBs) created from a dSource are growing without any user connections. This VDB growth is caused by SQL Server feature called Ghost Cleanup. This feature is part of SQL Server storage engine to optimize performance during index maintenance operations.
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.
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.
There are several options available to change this behavior:
- SQL Server does allows you to enable trace flag 621 which will prevent ghost process from running. (This is an instance level change and applies to all databases on the target. The hit you take on database size by disabling this process is very minimal since it’s a VDB. Also disabling ghost cleanup reduces I/O load on the server, which could be beneficial with busy target servers starved for I/O and memory).
- You can try applying the patch mentioned in KB article: KB2622823
- Leave ghost cleanup as is. VDB growth will stop once all the ghost records are purged. Users can quantify the initial size increase and then decide on further actions. Additionally, Delphix compression will reduce storage consumption.
- Let the ghost cleanup process complete on the VDB and then create a snapshot from Delphix to use as a golden copy for VDB provisioning. This new golden copy VDB won’t have the same issue.