다음을 통해 공유

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 https://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

  • Anonymous
    April 22, 2013
    May i know why heap table doesn't support ghost records?

  • Anonymous
    May 08, 2013
    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

  • Anonymous
    May 09, 2013
    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?

  • Anonymous
    May 12, 2013
    The comment has been removed

  • Anonymous
    October 31, 2013
    thanks for the information

  • Anonymous
    November 01, 2013
    You're very welcome Samson....thanks for appreciating...!!!

  • Anonymous
    April 01, 2014
    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.

  • Anonymous
    April 01, 2014
    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

  • Anonymous
    April 02, 2014
    The comment has been removed

  • Anonymous
    April 03, 2014
    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. :(

  • Anonymous
    April 04, 2014
    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

  • Anonymous
    June 17, 2014
    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.

  • Anonymous
    June 18, 2014
    The comment has been removed

  • Anonymous
    June 18, 2014
    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  ?

  • Anonymous
    June 22, 2014
    The comment has been removed

  • Anonymous
    October 15, 2015
    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?