Yesterday I got a call from a user about Database not being connected or getting timed out, well actually there are many different solutions for resolving performance issues.
But the issues what I had was not actually realated to performance there is something else I noticed behind the scene when I ran sp_who2 I had no clue.
Then I ran select * from sysprocesses where blocked<>0
to give me the list of blocked processed.
Then I noticed GHOST CLEANUP process was constantly running and blocking the other SIPD and using most of the resources.
First lets understand what is Ghost Process or what are Ghost records.
Ghost records are the records which are logically deleted but still they exist physically deleted in a page. Just to give a brief overview, having ghost records simplfies key-range locking and
transaction rollback.
The Ghost record is marked with a bit that indicates it's a ghost record
and cannot be physically deleted until the transaction that caused it to
be ghosted commits or until the transaction is committed. As soon as it is committed, records are deleted by an asynchronous
background process (called the ghost-cleanup task) or it is converted
back to a real record by an insert of a record with the exact same set
of keys.
To Resolve this issue quickly, I
disabled the Auto Create Statistics and Auto Update Statistics options
on the database. As soon as I hit the apply button the GHOST CLEANUP
process stopped.
To me it looks like when SQL tries to
create stats or update them, it will run the Ghost Cleanup process first
to cleanup the indexes and tables, before getting the stats.
But don't forget to create a job when you are disabling these tasks on the database. Create a job during non-business hours or when the load on the database is minimum.
Posts
