Posts
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.
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.
No comments:
Post a Comment