my understanding of the ghost cleanup process is that every five seconds it will look to removed ghosted records in an index. and so it will not overload the system, it will only "clean" roughly ten pages at a time.

so, that means it only cleans roughly 80k worth of records every five seconds? seems like my indexes would always be filled with ghosted records and the cleanup would never finish.

so, let's say i run a delete, perhaps a million rows, and the index records for those millions rows are roughly 8Gb in size. so roughly 100,000 times the difference of 80k. does that mean the ghost cleanup process will take 500,000s, or almost six days to complete?

clearly i am missing something here, because it does not make sense that it would take that long to clear out the ghosted records. and what about the other activity that hits that same index? does the ghost cleanup process cause waits, or have to wait for other processes?

[this question brought about by performance issues we have been seeing in the OpsMgrDW and we wanted to know more about this process]


I wrote two comprehensive blog posts that explain ghost cleanup (it's the only place it's explained in depth anywhere, either in print or online).

The first one is Inside the Storage Engine: Ghost cleanup in depth and the second one is Ghost cleanup redux. Yes, 10 pages every time, and it's possible for the ghost cleanup task to never catch-up with a high volume of continuous deletes.

Ghost cleanup apart from the 10-pages-every-5 seconds can be triggered aggressively by making sure the Storage Engine 'sees' the ghost records. Force a scan of the affected table or index using something like

select * from [problem-table] with (index = problem-index)

That will queue up a request to clean out the ghost records aggressively. Beware, though, that it will generate a lot of transaction log while it does it. They should also get cleared out by index rebuilds or reorganizes as part of regular index maintenance.

Hope this helps.

| improve this answer | |

I've always thought that index rebuilds would "fix" any remaining issues when the structures are shifted around.

| improve this answer | |

A couple of years ago I ran into this problem and was not able to fix it through configuration, triggering ghost cleanup, or anything of the sort. I was working with a database that had inserts and deletes continuously and SQLServer would effectively lock up periodically due to ghost cleanup triggering.

My eventual solution was to partition the problem tables by time, and instead of deleting old rows I could merely drop the old tables. That helped immensely. This may not be feasible for your situation but it applies to many, with some variation.

| improve this answer | |
  • that's interesting. OpsMgr tries to drop tables as well, but we do see some contention periodically with the ghost cleanup. – SQLRockstar Jun 11 '09 at 0:11
  • Yup - partitioning is always a better solution (theoretically) than massive deletes, but commonly there's some reason it can't be implemented. Most 3rd-party tools won't support it because it can change the querying characteristics of the workload and can lead to blocking issues. – Paul Randal Jun 11 '09 at 3:38

we have similar situation. One of our table has lot of ghost records and these records are not cleaned by Ghost cleanup process. DB Shrinking, DBCC Cleantable, Rebuilding the index and Reorganizing the index has no use. To resolve this we trasfer data to temporary table then trucate the table and trafer data back to truncated table. This table contains LOB Data (2 nvarchar(max) columns). Is there any other way to fix this?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.