Ghost records are something that are a bit of an enigma for most folks working with SQL Server, and not just because of the name. Today, I’ll seek to explain the concept, as well as identify some troubleshooting techniques.

The main reason behind introducing the concept of Ghost records was to enhance performance. In the leaf level of an index, when rows are deleted, they're marked as ghost records. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost. The page header also reflects the number of ghost records on a page. What this means, in effect, is that the DML operation which fired the delete will return to the user much faster, because it does not have to wait for the records to be deleted physically. Rather, they’re just marked as “ghosted”.

Ghost records are present only in the index leaf nodes. If ghost records weren't used, the entire range surrounding a deleted key would have to be locked. Here’s an example i picked up from somewhere:
Suppose you have a unique index on an integer and the index contains the values 1, 30, and 100. If you delete 30, SQL Server will need to lock (and prevent inserts into) the entire range between 1 and 100. With ghosted records, the 30 is still visible to be used as an endpoint of a key-range lock so that during the delete transaction, SQL Server can allow inserts for any value other than 30 to proceed.

SQL Server provides a special housekeeping thread that periodically checks B-trees for ghosted records and asynchronously removes them from the leaf level of the index. This same thread carries out the automatic shrinking of databases if you have that option set.The ghost record(s) presence is registered in:

• The record itself
• The Page on which the record has been ghosted
• The PFS for that page (for details on PFS, see Paul Randal’s blog here)
• The DBTABLE structure for the corresponding database. You can view the DBTABLE structure by using the DBCC DBTABLE command (make sure you have TF 3604 turned on).

The ghost records can be cleaned up in 3 ways:

• If a record of the same key value as the deleted record is inserted
• If the page needs to be split, the ghost records will be handled
• The Ghost cleanup task (scheduled to run once every 5 seconds)

The Ghost cleanup process divides the “ghost pages” into 2 categories:

• Hot Pages (frequently visited by scanning processes)
• Cold Pages

The Ghost cleanup thread is able to retrieve the list of Cold pages from the DBTABLE for that database, or the PFS Page for that interval. The cleanup task cleans up a maximum of 10 ghost pages at a time. Also, while searching for the ghost pages, if it covers 10 PFS Pages, it yields.

As far as hot ghost pages are concerned, the ghost cleanup strives to keep the number of such pages below a specified limit. Also, if the thread cleans up 10 hot ghost pages, it yields. However, if the number of hot ghost pages is above the specified (hard-coded) limit, the task runs non-stop till the count comes down below the threshold value.

If there is no CPU usage on the system, the Ghost cleanup task runs till there are no more ghost pages to clean up.

Troubleshooting

So now we get to the interesting part. If your system has some huge delete operations, and you feel the space is not being freed up at all or even not at the rate it should be, you might want to check if there are ghost records in that database. I’ll try to break down the troubleshooting into some logical steps here:

1. Run the following command:
   Select * from sys.dm_db_index_physical_stats(db_id(<dbname>),<ObjectID>,NULL,NULL,’DETAILED’)
   P.S. The object ID can be looked up from sys.objects by filtering on the name column.
   
   
2. Check the Ghost_Record_Count and Version_Ghost_Record_Count columns (version ghost record count will be populated when you’re using snapshot isolation on the database). If this is high (several million in some cases), then you’ve most probably got a ghost record cleanup issue. If this is SQL Server 2008/2008 R2, then make sure you have applied the patch mentioned in the kb http://support.microsoft.com/kb/2622823
   
   
3. Try running the following command:
   EXEC sp_clean_db_free_space @dbname=N’<dbname>’
   
   
4. If the ghost record count from step 1 is the same (or similar) after running this command, then we might need to dig in a bit deeper.
   Warning: Some of the troubleshooting steps mentioned from hereon are unpublished and might be unsupported by Microsoft. Proceed at your own risk.
   
   
5. Enable Trace Flag 662 (prints detailed information about the work done by the ghost cleanup task when it runs next), and 3605 (directs the output of TF 662 to the SQL errorlog). Please do this during off hours.
   
   
6. Wait for a few minutes, then examine the errorlog. First, you need to check if the database is being touched at all. If so, it’s very much possible that the Ghost Cleanup task is doing it’s job, and will probably catch up in a bit. Another thing to watch out for is, do you see one page being cleaned up multiple times? If so, note the page number and file id. Please ensure you disable the TF 662 after this step (it creates a lot of noise in the errorlog, so please use it for as little time as possible)
   
   
7. Next, run the following command on the page to view its contents
   DBCC PAGE(‘<DBName>’,<file id>,<Page no.>,3)
   
   
8. This will give you the contents of the page. see if you can spot a field called m_ghostRecCnt in the output. If it has a non-zero value, than means the page has ghost records. Also, look for the PFS page for that page. It will look something like PFS (1:1). You can also try dumping the PFS page to see if this page has a ‘Has Ghost’ against it. For more details on the DBCC Page, check out Paul Randal’s post here

Another thing that deserves mention is the special role of the PAGLOCK hint w.r.t ghost records:

• Running a select statement with the PAGLOCK hint against a table will ensure that all the ghost records in that table are queued for cleanup by the ghost cleanup task.
• Accommodating the PAGLOCK hint in your delete statement will ensure that the records are deleted there and then, and are not left behind for the Ghost Cleanup task to take care of later. By default, all indexes have the PAGLOCK option turned on (you can check by scripting out a create index task), but they might not be able to get it all the time. This is where the PAGLOCK query hint comes in. It makes your query wait for the Page Lock, so it can clean up the records physically before returning. However, it’s not advisable to use the PAGLOCK hint in your delete statements all the time, as the performance trade-off also needs to be taken into consideration (this is the same purpose for which the Ghost Cleanup task was introduced, remember?). This should be resorted to only under situations where you are facing a definite issue with Ghost Record cleanup, and have a dire need to prevent further ghost records from getting created.

These steps might or might not solve your problem, but what they will do is give you an insight into how the SQL Server Database Engine works w.r.t Ghost records and their cleanup. One of the most common (and quickest) resolutions for a ghost records issue is to restart SQL Server.

Once again, this post does not come with any guarantees, and the contents are in no way endorsed by Microsoft or any other corporation or individual.

Hope this helps you understand the concept of Ghost Records somewhat. You’re more than welcome to share your experiences/opinions/knowledge in the comments section, and I shall be delighted to include them in the contents of the post if suitable.