9

I'm running a high transactional database (~175k transactions / Minute on average, almost 9M records per hour added and removed)

Up till recently this hasn't been too much of an issue as we've been at ~7.5M records added and removed but with the latest influxes of data the ghost cleanup doesn't appear to be able to keep up with cleaning up the unused space on tables / indexes.

A few days ago we reached 53 GB of 'Unused Space' across 16 tables (mostly 2 of them) so as a result started looking into the ghost cleanup process to find it runs once every 5 seconds and runs over 10 pages.

My current solution is that early morning I am running three threads of the following command:

DECLARE @2hours datetime = dateadd(hour,2,getutcdate())

WHILE getutcdate() < @2hours
BEGIN
    DBCC FORCEGHOSTCLEANUP ('DBNAME') WITH  NO_INFOMSGS
END

to catch up with the backlog from the night before (when most of our deletes take place)

I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages, is there any way of doing that or should I continue just spinning up multiple cleanup procs to clear out the data, or if there's any other actions that can assist with this

Re-indexing runs on most effected indexs atleast once a week (most are every other day)

SQL Server 2012 Enterprise SP3_CU8 (upgrading to CU9 tomorrow) on AlwaysOn High Availabilty Cluster also with replication (distribution on a separate server)

4

I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages

No there is none. Atleast I dont know as of now :-)

if there's any other actions that can assist with this

From Paul Randal's blog - 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).

select * from [your_problem_table] with (index = Index_that_has_large_Deletes)

Is there a possibility for you to partition the table and purge the old partition instead of doing deletes ? FYI .. SQL Server 2016 and up allows you to truncate individual partitions as well.

Also, you can (TEST and implement) - disable ghost clean-up (trace flag 661) and then rebuild index WITH ONLINE = ON option since you are using Enterprise edition.

If you are using AlwaysON (with async) with replication, make sure to enable Trace flag 1448 - allows the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change.

Make sure to read Deletes that Split Pages and Forwarded Ghosts from Paul White to see if your tables have triggers or LOB columns that would slow down.

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.