Have you ever wondered how SQL Server handles the cleanup of deleted records? In this blog post, we will explore the Ghost Cleanup process and understand how it works.

The Ghost Cleanup process is responsible for removing deleted records from data and index pages in SQL Server. When a record is deleted, SQL Server marks it as deleted but does not immediately deallocate the space it occupies. Instead, it leaves a single record on the page to avoid having to deallocate empty data or index pages.

To demonstrate this process, let’s create a table in the tempdb database and insert some records into it:

-- Create a table in tempdb
USE tempdb;
GO

CREATE TABLE Test (
    RowID INT CONSTRAINT PK_Test PRIMARY KEY CLUSTERED,
    Col1 CHAR(750)
);

-- Insert records into the table
INSERT INTO Test (RowID, Col1)
VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five'), (6, 'Six'), (7, 'Seven'), (8, 'Eight'), (9, 'Nine'), (10, 'Ten'), (11, 'Eleven'), (12, 'Twelve'), (13, 'Thirteen'), (14, 'Fourteen'), (15, 'Fifteen');

Now, let’s see what pages are used by this table using the undocumented command DBCC IND:

-- Check the pages used by the table
DBCC IND (tempdb, 'Test', 1);

We are interested in the rows where PageType = 1, which represents data pages. We can view the contents of these pages using the DBCC PAGE command:

-- View the contents of the pages
DBCC PAGE (tempdb, 1, 374, 3);
DBCC PAGE (tempdb, 1, 361, 3);

Now, let’s delete some records from the table and force the Ghost Cleanup process to run:

-- Delete records and force Ghost Cleanup
DELETE FROM dbo.Test WHERE RowID <= 12;
DBCC ForceGhostCleanup;

After deleting the records, let’s check the pages used by the table again:

-- Check the pages used by the table
DBCC IND (tempdb, 'Test', 1);

Surprisingly, both pages are still there. However, if we examine the contents of these pages, we can see that the deleted records are marked as GHOST_DATA_RECORD:

-- View the contents of the pages
DBCC PAGE (tempdb, 1, 374, 3);
DBCC PAGE (tempdb, 1, 361, 3);

To remove these pages with only ghost records and the ghost records themselves, we need to rebuild the index:

-- Rebuild the index
ALTER INDEX PK_Test ON dbo.Test REBUILD;

After rebuilding the index, if we check the pages used by the table again, we will find only one page with data, and the ghost record count will be zero:

-- Check the pages used by the table
DBCC IND (tempdb, 'Test', 1);

By understanding the Ghost Cleanup process, we can ensure efficient space utilization in our SQL Server databases. It’s important to regularly monitor and maintain indexes to keep the database performance optimal.

Thank you for reading this blog post. Stay tuned for more SQL Server tips and tricks!