Ghost records in SQL Server

When records are deleted from a table, those records are logically removed by marking them as deleted but not physically removed from the page immediately. It has introduced as a performance optimization that makes the delete operations faster.

What exactly happens when delete command is fired?

The marked record by the delete statement in the data page is marked as ghost (stored in log file .ldf file) and is not released until the Ghost Cleanup Process takes over after the delete operation has done successfully.

Why this records needs to be there?

While performing delete operation on table, if the transaction is cancelled or need to roll back the table data.

Who will remove the Ghost record?

Ghost Cleanup Process or Shrink the database runs to deletes the records physically and frees the database space. Sometimes we can Rebuild indexes to this table also.

SQL Query for ghost records count

SELECT ghost_record_count,* FROM  

sys.dm_db_index_physical_stats(DB_ID('Database Name’),OBJECT_ID('Table name'), NULL, NULL, 'DETAILED')

To run this, we have require 5 parameters or objects mentioned below.

Will see the Output: