My company has recently started experiencing user processes being blocked by the internal Ghost Cleanup process at times for up to 10 minutes. How can we eliminate or significantly reduce the amount of time Ghost Cleanup holds exclusive locks on our db objects?
This is on our production transactional database server. 8 CPU, 128 GB RAM, SQL 2005 Enterprise, Windows 2003 Server. Database size is currently 900GB on a dedicated RAID5 array on SAN made up of 300GB 15k fiber disks (5 or 6 in the array, I don't recall). Log file is on a separate disk. Database is not set to Auto Shrink, but is set to Auto Update Statistics (I'd rather not disable this), and is set to Auto Create Statistics. It's in Full recovery mode with logs being backed up hourly. I don't have good data regarding performance of the disk (working on that with our SAN vendor), but if it's possible that disk performance affects the ghost cleanup process I'd like to know that. I'm having trouble finding any useful documentation on Ghost Cleanup.
This happens only occasionally, not quite daily and not usually more than once per day. I'm looking into whether there are any processes that delete a ton of data which could cause ghost cleanup to go nuts but don't have data yet. Is there any way at all to schedule the ghost cleanup process to run at a certain time, or to force it not to lock the entire table while it is working? I don't want to disable it as I don't want the database to grow uncontrollably. I haven't found much information about this online.
Start Free Trial
