Introduction

SQL Server databases consist of various background processes and user processes. One of the important background processes is the Ghost Cleanup task. While many DBAs are familiar with processes like Checkpoint and Lazy Writer, the Ghost Cleanup task often goes unnoticed. In this article, we will explore the internals of the Ghost Cleanup task and its significance in SQL Server databases.

What is the Ghost Cleanup Task?

The Ghost Cleanup task is a background process that runs periodically to clean up ghost records in SQL Server databases. But what exactly are ghost records? When a user deletes records from a clustered index data page or a nonclustered index leaf page, SQL Server does not physically remove them. Instead, it marks these records as ghost records. The deleted rows remain on the page, but the row header indicates that they are ghost rows. This optimization technique allows for quick logical deletion during the delete operation. During a rollback, SQL Server unmarks the record as a ghost record, eliminating the need to reinsert the deleted records. Once the delete transaction commits and the ghost cleanup task runs, it physically removes the ghost records from the database.

How Does the Ghost Cleanup Task Work?

The Ghost Cleanup task performs the following steps:

1. The task runs every 5 seconds (SQL Server 2012+).
2. It checks databases marked as having ghost entries and scans the PFS (Page Free Space) pages of the ghost database.
3. It physically removes the ghost record rows from the database.
4. Once all ghost records are removed from a database, it marks the database as having no ghost entries, and SQL Server skips it during the next run of the ghost cleanup task.

Important Notes:

• A single run of the Ghost Cleanup task cleans up a maximum of 10 pages in each execution to avoid impacting system performance.
• If there are more than 10 pages with ghost records, they are processed during subsequent executions of the task.
• The Ghost Cleanup task is usually not visible when querying sp_who2 or DMVs because it is a quick process. However, in databases with frequent deletes, you may observe it in the session’s output.

Practical Demonstration

To better understand the Ghost Cleanup task, let’s create a sample table and insert records into it:

CREATE DATABASE TestDatabase;
USE TestDatabase;
CREATE TABLE dbo.ViewGhostRecords (
    ID INT PRIMARY KEY CLUSTERED,
    Name VARCHAR(100) NULL,
    Amount INT NULL
);
INSERT INTO dbo.ViewGhostRecords (ID, Name, Amount)
VALUES (1, 'Record 1', 100), (2, 'Record 2', 200), (3, 'Record 3', 300);

Next, we can enable trace flags to view DBCC results and disable the Ghost Cleanup task:

DBCC TRACEON (3604, -1);
DBCC TRACEON (661, -1);

We can then delete records from the table and view the page data using DBCC PAGE:

BEGIN TRAN;
DELETE FROM dbo.ViewGhostRecords;
DBCC PAGE (TestDatabase, 1, 1, 3) WITH TABLERESULTS;

By querying the index DMV, we can check the number of ghost records:

SELECT SUM(ghost_record_count) AS total_ghost_records, DB_NAME(database_id) AS DB
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')
GROUP BY database_id
ORDER BY total_ghost_records DESC;

Finally, we can disable the Ghost Cleanup task using trace flag 661, but it is not recommended unless necessary:

DBCC TRACEOFF (661, -1);

Conclusion

The Ghost Cleanup task plays a crucial role in maintaining the performance and integrity of SQL Server databases by removing ghost records. While it is possible to disable the task, it is generally not recommended unless there is a specific requirement. If necessary, manual deletion of ghost records can be performed using stored procedures like sp_clean_db_free_space and sp_clean_db_file_free_space. Understanding the Ghost Cleanup task is essential for database administrators to optimize database performance and ensure data consistency.