An in-depth look at Ghost Records in SQL Server


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.

Comments (16)

  1. ERIC says:

    May i know why heap table doesn't support ghost records?

  2. Hi Eric,

    Thanks for showing interest in the blog. A few reasons:

    1. We do not use keyrange locks in heaps

    2. There is no way a new insert can conflict with a delete operation that is under rollback, since we anyway use heap level locks when performing updates/inserts/deletes on heaps

    The only case where heaps have ghost records is when we use versioning (snapshot isolation).

    Hope this helps.

    Regards,

    Harsh

  3. ERIC says:

    THANK YOU Harsh.

    "insert can conflict with a delete operation "? do you refer to the unique constraint ?can you kind of helping to explain it more details?

  4. Hi Eric,

    Let's take the example I specified in the blog. If you delete the 30 record from a heap table, the records will be deleted physically (or rolled back completely), and only then will the delete operation return. So there is no possibility of an insert of record value 30 conflicting with the delete operation down the line. Also, the delete operation against the heap uses heap level locks, since we do not have a clustered index to allow us to take key range locks. Hope this helps.

    Please feel free to drop me an email if you would like to discuss this further.

  5. samson says:

    thanks for the information

  6. HarshDeep_Singh says:

    You're very welcome Samson….thanks for appreciating…!!!

  7. Ray says:

    Hi Harsh,

    Is this still active/monitored?  Hmm.  This is good background and foundational information but I need to go bit further.  First, how do I tell/prove that Ghost Record cleanup is a cause of, or contributor to performance issues.  Second what can I do about it.

    I have several transactional tables that receive 1M+ inserts daily.  After a holding period (30 to 90 days) the same 1M+ are deleted.  Most of the tables have on the order of 100M rows and may be 100GB.  Running the physical status management function is not really practical.

  8. HarshDeep_Singh says:

    Hi Ray,

    Thanks for showing interest in the blog.

    The Ghost record feature was introduced as a performance improvement, so let me assure you that in most situations it will not be a cause of performance degradation. The only adverse impact it may have is reclamation of space when large deletes are performed on the table.

    For investigating the space usage by the table, the steps identified in the troubleshooting section above should work.

    Please let me know if you have any other questions.

    Regards,

    Harsh

  9. Ray says:

    Ray.Herring@Hotmail.com

    Thanks for the reply Harsh.

    I have exactly the "only adverse impact" situation you mention.  I am evaluating this problem using SQL 2012 RTM Developer on Windows 2008R2 EE with 64GB RAM and 4 vCPUs.

     I have been working using your guidance above as well as some information from Jason Strate (http://www.jasonstrate.com/…/index-black-ops-part-4-index-overhead-and-maintenance)  I used his scripts as a model to create and modify a table.

    I have also refreshed my recollection of Paul Randall's Ghost Record posts on his SQL SKILLS blog.

    Several things I have demonstrated are

      1 – DELETE <myTable> with (PAGLOCK) Where <condition> DOES NOT immediately clean up ghost records.  When I execute the delete the ghost record count for all indexes increase (according to Index Physical Stats).

      2 – Leaf_Ghost_Count in Index Operational Stats is an accumulating value that a) does not give any information about current ghost record count and b) is only reset when the index is rebuilt or reorged.

      3 – sp_clean_db_free_space takes so long to run that it is impractical as a solution.  As I complete this reply the process has been running for >35 minutes on a 4.5GB DB with 10 tables.

      4 – When I enable TF 662 (log Ghost Record Cleanup Process) my SQL Errorlog is flooded with entries.  The log has about 160,000 Ghost Process entries in barely 5 minutes.   [ enable at 10:57:26 disable at 11:02:48]

    BTW, rebooting a 24 x 7 x 52 server every couple of days is not a good solution to any problem.  Also, running the Physical Stats DMV with MODE = 'Detailed' is not really practical when the target table/index runs to 100+M rows and 100+GB.  There needs to be/should be and easier way to determine the current number of ghost records.

    I really appreciate your original article. It pointed me in the direction to get started.  However, I think I still have a long way to go on this one.

    I'll be glad to share test scripts/data etc. if you desire.

  10. Ray says:

    Hi Harsh,

    Just a quick follow up.  sp_clean_db_free_space finally completed (13 hours 40 minutes)

    but the Ghost Record counts (according to Index Physical Stats) did not change. 🙁

  11. HarshDeep_Singh says:

    Hi Ray,

    Can you please send me the output of the errorlog with TF662, as well as the output from sys.dm_db_index_physical_stats?

    My email is harshdeep_singh@hotmail.com

  12. Shanky_621 says:

    HI Harsh,

    You wrote 'Ghost records are present only in the index leaf nodes'. I guess you should also include about Non clustered index leaf page when record is deleted from here we have ghost records as well. You should also mention about ghost records in Versioned database although you mentioned this in comment.

  13. HarshDeep_Singh says:

    Hi Shanky…thanks for showing interest in the blog. When I mentioned index leaf nodes in the blog, I did not specify clustered or non-clustered indexes, because I meant both. When we generalize and say "index", it can include both clustered and non-clustered indexes, as I'm sure you're aware.

    As for versioned ghost records, please refer to point 2 in the troubleshooting section in the blog.

    While I appreciate constructive feedback, it would be great if you could read the blog article fully before pointing out areas of improvement. Thanks.

  14. Shanky_621 says:

    Thanks Hrash for replying I have replied to many Blogs but only you are the person to reply back so thank for that. I read your complete blog and reffered to forum users as well its nice. Ya I missed somehow my bad 🙂

    One question I have Do ghost records happen when we have forwarded records  ?

  15. HarshDeep_Singh says:

    Hi Shanky…thanks for your question. I think you meant to ask if ghost records are created when a forwarded record is deleted. A Ghost version record is created when versioned forwarded heap rows are recalled back to their originating page. The records must be ghosted rather than deleted so the versioning chain can be maintained. The ghost version records cannot be cleaned up until all transactions that have references to the original data record have finished (either committed or rolled back).

  16. Raman Bedi says:

    Hi Harsh,

    Would this also work for slack space issues? I have a SQL 2005 database , is there a cleanup script available for this version?