SQL Server – GHOST_CLEANUP Lots of IO and CPU Usage

ghost-cleanupsql serversql-server-2012

I have been trying to troubleshoot an issue I have detected on a UAT SQL Server, there is some resource usage even if the server is not being used…

On Activity monitor I have an average of 5% CPU usage (Real SQL Server Usage double checked with DMVs) and also a 20mb/s disk IO, this has been the same for 2 days now…

I have found that the only process that is doing IO Operation is the ghost cleanup…

After reviewing what data file is doing the IO Operation I found that I have 1500Reads/sec (NO WRITE OPERATIONS) on one Database where some users were doing load tests days ago…

What can I do with this, will this process finish? Is there anything I can do?

EDIT

After taking some time on troubleshooting this I finally found that after disconnecting the Database from the Availability Group GHOST_CLEANUP started to really free up space on my Database, took 4 hours for him to free 80GB of Storage. After connecting the Database to the Availability Group GHOST_CLEANUP starts to not free space again… Weird.

Best Answer

What can I do with this, will this process finish? Is there anything I can do?

This is how Ghost Cleanup works it kicks up after every 10 seconds and does the cleanup. The ghost cleanup does not filters out specific pages to cleanup, when ghost cleanup operation starts it takes up 10 pages which are marked as ghosted and cleans them up. This process happens every 10 sec unless scan which run and finds out ghost records come and says that there is no ghost records.

Yep ghost cleanup is single threaded, I/O intensive and also fills up transaction log and you cannot avoid this unless you disable it. To disable this process you can enabled trace flag TF 661 documented in This support article. As a good advise it is NOT recommended but since you have testing system you can of course try it.

So I believe the testers are doing some big delete operation may be periodically forcing lot of ghost records and this forcing ghost cleanup.

I would suggest you wait till the process completes. Like I said it will pick not more than 10 pages and so depending on amount of pages deleted it may take some time.

Some very good readings on Ghost Cleanup process.

  1. Inside Storage Engine Ghost Cleanup in Depth
  2. Turning Off Ghost Cleanup Tasks for Performance Gains

EDIT:

My main concern however is that if I disable the Ghost Cleanup task, how then I will clean the rows marked to be deleted?

I would like to quote from the post Turning Off Ghost Cleanup

If you disable the ghost cleanup task, the space taken up by deleted records will NOT be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.

One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.

Related Question