When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records.
SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically reduce the operation's performance.
SQL Server delete the records physically from storage by a thread called Ghost Cleanup Thread (GCT). Ghost Cleanup Thread physically remove the deleted/updated records.
Few facts about Ghost Cleanup Thread
1. SQL OS invokes Ghost Cleanup Thread every 10 seconds interval.
2. Ghost Cleanup Thread run at SQL instance level and sweep one bye one each DB starting from master.
3. Ghost Cleanup Thread scans "PFS" (Page Free Space) pages of the DB to get the pages which has ghost records.
4. Ghost Cleanup Thread remove records which are marks as deleted.
5. Skip the database if it is not able to take a shared lock for the database or database is not Open in read/write mode.
--By Abhishek....
Stay In Touch:
No comments:
Post a Comment
If you have any doubt or question, please contact us.