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)