Understanding about the ghost records in SQLSERVER

Understanding about the ghost records in SQLSERVER

The phantom of the opera records also called phantom records, ghost records English Name: Ghost record

Truncate table there is no discussion about using the ghost records

Two situations will appear ghost records: (1)Clustered index on the table (2)Using snapshot isolation level heap table

Related articles:




Why is there the phantom of the opera is recorded in table clustered index, we all know there are clustered index table data pages, which will connect with two-way linked list, if immediately delete,

It will affect the index lookup data, such as a transaction is using the clustered index lookup data, if this time to delete a page,

Then find out the result is not accurate, so when I free, then slowly delete data, I think so.

Ghost clean record
Q: in the process of information window SQL Server enterprise manager, I found a “ Ghost Record Cleanup” (phantom erase) background processes, and the command is triggered by the user system.

Answer: to delete rows from the database, or extend the panel, SQL Server will be the object is marked as &ldquo ” phantom; (delete operation to be performed), and later in the use of a background task remove these objects, the process is the Ghost Record Cleanup. Ghost Record Cleanup can improve the performance of the Delete command, because the SQL Server without immediate physical cleaning operation.


The following contents according to the given article summed up

(a SQL Server ghost cleanup task every 5 seconds) can use the trace flag 661 to shut down the ghost cleaning tool.

This will reduce the physical IO, because the clear need to page is saved in the buffer pool, will produce a log, cause physical IO.

If the delete quantity is big database can enable trace flag 661, so the ghost cleanup task will not run.

1 DBCC TRACEOFF(661,-1)--Close the ghost Cleanup tool in the global scope
2 DBCC TRACESTATUS(661) --Check whether ghost cleaning tool is running the status column

To view a table if there are ghost records

For the table scan, the ghost record number as small as possible, as for why, because in the query, the execution engine to judge whether there is ghost records, if a ghost records will skip this record, continue to the next record search

1 SELECT  [ghost_record_count], [version_ghost_record_count]
2 FROM    [sys].[dm_db_index_physical_stats](DB_ID('dlgpos'),   --Database ID
3                                            OBJECT_ID('[DLGPOS].[dbo].[Accounts]'), --Table objectid
4                                            NULL, NULL,'detailed')

If you want to truly understand the ghost records, you can look at my two article translation

A ghost records Chinese Translation

The phantom of the opera records Chinese translation two

If there is not in place, welcome everybody clap brick o(∩_∩)o

Posted by Boyce at November 24, 2013 - 7:36 PM