Translation of ghosting records 2

Translation of ghosting records 2

Link: http://www.sqlskills.com/blogs/paul/ghost-cleanup-redux/

I wrote an article earlierArticleInside the storage engine: Ghost cleanup in depth.
Today, one of the students in the class I taught was asked about ghost recording.
Record a blog post
Q: Will the ghost image record occur in the heap table? The answer is: no unless it is abnormal.

When the Snapshot isolation level is enabled, the records in the heap table will be deleted as Ghost records and used as version management
This will have some interesting effects. A version record adds a 14-byte tag at the end,
Therefore, a record in the heap table is 14 bytes longer than the previous one, which means that the record is stored in
A data page is no longer suitable. This will cause the record to move and roll forward, only because the record is deleted.
Now, the page is full of data, and the storage engine will take some measures to avoid recording length less than 32 bytes
However, it may be too deep.

In any case, leave the topic aside. I want to show you the differences between deleting records in the clustered index table and deleting records in the heap table.
I want to create two tables, delete all the records, and roll back.

Execute the following SQL statement first

 1   Use   [  Gposdb  ] 2   Go  3   Create   Table T1 (C1 Char ( 10  ));  4   Create   Clustered   Index T1c1 On  T1 (C1 );  5   Go 6   7   Create   Table T2 (C1 Char ( 10  ));  8   Go  9   10   Insert   Into T1 Values ( '  Paul '  );  11   Insert   Into T1 Values ( '  Kimberly  '  );  12   13   Insert   Into T2 Values ( '  Paul '  );  14   Insert   Into T2 Values ( '  Kimberly  '  );  15   Go 

 1   -Prevent random background transactions  2   Alter   Database  [  Gposdb  ]   Set Auto_create_statistics Off  ;  3   Go  4   5   Begin   Tran  Delfromclust;  6   Delete   From T1 Where C1 =  '  Kimberly  '  ;  7   Rollback   Tran  ;  8   Go  9   10   Begin   Tran  Delfromheap;  11  Delete   From T2 Where C1 =  '  Kimberly  '  ;  12   Rollback   Tran  ;  13   Go  14   15   Select  *   From : Fn_dblog ( Null , Null  );  16   Go 

This is part of the transaction log. In the red box in the figure, I disabled auto update statistics.
To prevent other unrelated transactions from being generated, you can better observe the results.

The first transaction in the red box above is to roll back the delete record operation of the clustered index. You can see clearly
The third column shows that a ghost image record is used for deleting a record. In addition, you can see the bitmap in the settings PFS page.
To mark the existence of a ghost image on this page.
The part in the second red box above is used to roll back the delete record operation of the heap table. Here you can see what the database does
Is an action to directly delete records.
If you have viewed the data page before performing the rollback operation, you will see the records in the clustered index table marked as Ghost shadows.
The record in the heap table is deleted directly.

Hope this article will help you

 

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