Translation of ghosting records 1

Original article on Ghost history Translation: many years ago, I saw a post on Ghost history cleanup tasks in various forums when I was still in the storage engine development team. There were some bugs in the ghost removal task before. The KB articles were KB932115 and KB815594. However, the two articles only introduced the ghost mechanism for some reason.

Original article on Ghost history Translation: many years ago, I saw a post on Ghost history cleanup tasks in various forums when I was still in the storage engine development team. There were some bugs in the ghost removal task before. The KB articles were KB932115 and KB815594. However, the two articles only introduced the ghost mechanism for some reason.

Translation of ghosting records 1

Original article:

Many years ago, I saw a post on Ghost cleaning tasks in various forums when I was still in the storage engine development team.
There were some bugs in the previous ghost removal task. KB articles were KB932115, KB815594, and server space. However, there are still few articles about ghost mechanism in these two articles.
For some reason, I did not mention ghost shadows in my old blog. Today I plan to write an article about ghost cleanup tasks.

So what is Ghost Image cleanup? This is a background process used to clear ghost records. It is usually mentioned together with the ghost cleaning task.
What is a ghost recording? Last week, I briefly described the topic "anatomy OF a record post ".
A deleted record or a record deleted from a table without any indexes in a database at the Snapshot isolation level is a ghost image record.

In terms of clustered index tables, the ghost image record will be better. The ghost image is a delete operation, but it is not physically deleted from the data page.
Mark the record "deleted" or "shadow" on the data page ". This is a performance optimization for the quick execution of delete operations.
This also makes the rollback of the delete operation faster, because you only need to remark the "deleted" or "ghosting" record as "not deleted", so you don't need
Re-insert records that have been deleted. Deleted records will be physically deleted by a background ghost cleaning process/task later (the location of the deleted records,
Will not be overwritten by other records ). A ghost recording inserts a record on an empty data page or an empty index page to avoid the empty data page or an empty index page.
Released. when the transaction is rolled back, the data page or index page can still exist.

The ghost recording cleanup task cannot physically delete records marked as Ghost records. Unless you commit your transaction, make sure that the delete operation has been committed because the deleted records
A lock exists and will not be unlocked until the transaction is committed. By the way, if a record is marked as "deleted" on a data page, the nolock option is added.
Or the read uncommitted isolation level cannot be used to READ the record marked as a ghost.

When a record has been deleted and marked as a ghost recording, the data page of the ghost recording will also be marked as a ghost page, which will be reflected on the PFS page.
The information is recorded in the header of the data page. You can also remind the database that there are some ghost records in the current database when you record the data page of the current ghost history on the PFS page.
Not cleared yet. The ghost recording cleanup task does not know which data pages have ghost records, even if there is a delete operation. Only when the scan operation reads
Data Page.

The ghost recording cleanup task searches for ghost records every five seconds. The scan task uses the PFS page of each database to check whether a ghost image record page exists,
If so, notify the ghost cleanup task to clear the ghost records on the ghost history page. If the scan task does not exist, scan the next database.
The ghost cleanup task cleans up the ghost records of up to 10 pages each time. In my impression, it looks like 10 pages to ensure that the system will not have any impact.
Therefore, ghost records will eventually be deleted and cleared through ghost records. If the ghost cleanup task scans the current database for no ghost history and the Hong Kong Space, it will
Skip this database to scan the next Database

Can you tell the ghost recording cleanup task how it works? In SQLSERVER2005, you can use the following code in
View ghost cleaning tasks in sys. dm_exec_requests View

# Myexecrequests 5 FROM sys. dm_exec_requests; nocount on;) # myexecrequests sys. dm_exec_requests (*) 19 FROM ## myexecrequests20END; ## myexecrequests;, U.S. space