Learn more about ghost history in sqlserver

Learn more about ghost history in sqlserver

Ghost recording is also called phantom record, and ghost recording is also called Ghost record.

Whether the truncate table uses ghosting records

There are two possible ghost and shadow records.(1) clustered index table (2) Use the Snapshot isolation level heap table

RelatedArticle:

Http://support.microsoft.com/kb/2622823/zh-cn
Http://www.sqlskills.com/blogs/paul/ghost-cleanup-redux/
Http://www.cnblogs.com/Amaranthus/archive/2013/04/22/3036619.html#2664812
Http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/

Why is there a Ghost Shadow record in the clustered index table? We know that there is a clustered index table, and the data pages in it will be connected with a two-way linked list. If you delete it immediately,

The index query data is affected. For example, a transaction is using the clustered index to search for data. If a page is deleted at this time,

The search results are not accurate, so when you are idle, You can slowly Delete the data. What do I think?

Ghost record cleanup
Q: In the process information window of the SQL Server Enterprise Manager, I found a background process named "ghost record cleanup, this command is triggered by the user system.

A: When deleting rows, pages, or extended disk areas from a database, SQL Server marks these objects as "phantom" (indicating that the delete operation is pending ), and later, use the background task to clear these objects. The process is ghost record cleanup. Ghost record cleanup improves the performance of the DELETE command because SQL server does not need to perform physical cleanup immediately.

 

The following content is summarized based on the link provided in the article.

(The ghost cleanup task is executed every five seconds in SQL Server.) You can use the trace flag 661 to disable running of the ghost cleanup tool.

This will reduce the physical Io, because the page needs to be saved in the buffer pool for cleanup, logs will be generated, resulting in physical Io.

If the trail mark 661 can be enabled for a database with a large Delete volume, the ghost cleanup task will not run.

1 DBCCTraceoff (661,-1)--Disable the ghost cleanup tool globally2 DBCCTracestatus (661)--Check whether the ghost cleanup tool is in the running status Column
 

Check whether a table has a ghost record.

For a table scan, the smaller the number of ghosting records, the better. As for why, the execution engine must determine whether there are any ghosting records during the query, if it is a ghost recording, skip this record and continue searching for the next record.

 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]  ' ), --  Objectid of the table  4                                              Null , Null , '  Detailed  ' )

 

If you really want to know about ghost and shadow records, you can take a look at the two articles I translated.

Ghost recording TRANSLATION 1

Ghost recording Translation 2

If there is something wrong with this article, you are welcome to make a brick O (else _ else) O