Yes, it’s a cleanup process available as a part of SQL Server Storage Engine. It runs in background to cleanup ghost records. If you can remember … “When we create a clustered index then it stores actual data/records at the leaf level of the index.” And SQL Server marks/declares a record as a ghost records when they are deleted from leaf level of index.

Why just mark and not to delete at once even though SQL Server knows that user does not need that record anymore ?? Well, to achieve better performance SQL Server runs “Ghost Cleanup Task” asynchronously to permanently delete marked records.

Though the ghost cleanup task is one of the internal processes of SQL Server, it does not override the locking framework laid-out in SQL Server. That means … the ghost cleanup task can’t delete the marked records for deletion until and unless the lock has released which is acquired by user’s delete transaction. Once the delete transaction is committed, ghost cleanup task permanently erase these records.

To accommodate this functionality, SQL Server ignores those records while parsing which are marked as ghost records even though you use NOLOCK or READ UNCOMMITTED hints.

It’s not easy to find a ghost cleanup task’s activity as it runs in the background every 5 seconds and scans ghost records for deletion. But you can monitor the currently running processes time by time and spot it. You can use sys.dm_exec_requests DMV to see different running processes and their types in “command” column.

SELECT * FROM sys.dm_exec_requests WHERE command LIKE ‘%ghost%’
Go

Please find the documentation of sys.dm_exec_requests on MSDN.

You can also check some awesome articles on this subject:
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
http://sqlserverpedia.com/blog/sql-server-service-internals-and-architecture/164/

I hope you have enjoyed this information.
Thanks, Khilit