Ghost Record Cleanup Process

When rows are deleted from an index leaf page, the space is not released immediately, instead SQL Server marks those records as "ghost records" (records pending for deletion).  Later the Ghost Record Cleanup Process will remove those records physically in an asynchronous fashion, improving the performance of the delete operations.

The Ghost Record Cleanup Process has also an impact on the insert operation.  SQL Server allocates new pages when there is no room for data in the existing pages. They incur in pages split, moving 50% of the rows form the filled up pages to the newly allocated pages.  SQL Server mark the records moved from the filled pages as "ghost records", which are later removed by the Ghost Record Cleanup Process. 

References

http://support.microsoft.com/kb/929440

http://support.microsoft.com/kb/815594

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.