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.
I've always thought that index rebuilds would "fix" any remaining issues when the structures are shifted around.
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.
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?