sqlskills-logo-2015-white.png

Ghost cleanup redux

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I’m teaching this week that’s worth answering in a blog post – do ghost records occur in heaps? The answer is no, not during normal processing.

When snapshot isolation is enabled, deletes from a heap are ghosted, as part of the overall versioning process, which can lead to some interesting side-effects. A versioned record has an extra 14-bytes tagged on the end, so a heap record that suddenly becomes versioned is 14-bytes longer – which may mean it doesn’t fit on the page any longer. This could lead to it being moved, resulting in a forwarding/forwarded record pair – just because the record was deleted! Now, the page has to be full for this time happen, and the Storage Engine will take steps to avoid this happening for rows less than 32 bytes long – but that’s getting a little too deep. Paul White talks this and an even deeper case in this post.

Anyway, I digress. I want to show you the difference between deleting from a clustered index and from a heap. I’m going to create two such tables, then delete row from each and roll it back.

 

CREATE TABLE t1 (c1 CHAR (10));
CREATE CLUSTERED INDEX t1c1 on t1 (c1);
GO

CREATE TABLE t2 (c1 CHAR (10));
GO

INSERT INTO t1 VALUES (‘PAUL’);
INSERT INTO t1 VALUES (‘KIMBERLY’);

INSERT INTO t2 VALUES (‘PAUL’);
INSERT INTO t2 VALUES (‘KIMBERLY’);
GO

— prevent random background transactions
ALTER DATABASE GhostTest SET AUTO_CREATE_STATISTICS OFF;
GO

BEGIN TRAN DelFromClust;
DELETE FROM t1 WHERE c1=’KIMBERLY’;
ROLLBACK TRAN;
GO

BEGIN TRAN DelFromHeap;
DELETE FROM t2 WHERE c1=’KIMBERLY’;
ROLLBACK TRAN;
GO

SELECT * FROM ::fn_dblog (null, null);
GO

 

Here’s a portion of the results from looking in the transaction log. The line of code where I turn off auto-update stats is just to prevent the auto-create transactions from cluttering up my view of the transaction log.

The first (highlighted) transaction is for the delete/rollback in the clustered index. You can clearly see that the third column shows a log context of ghosting for the LOP_DELETE_ROWS log record, plus the setting of the ‘this page has at least one ghost record’ in the PFS byte for that page.

The second (unhighlighted) transaction is for the delete/rollback in the heap. Here you can see that it just does a straight delete.

If you look at the data page contents before the rollback in both cases, for the clustered index you’ll still be able to see the deleted (ghosted) record, and for the heap you’ll see the deleted record really is deleted.

Hope this helps.

9 thoughts on “Ghost cleanup redux”

  1. Very interesting Paul. When I’m discussing the scenarios where heaps might cause row forwarding due to widening updates, I usually point out that this is only possible with heaps that have variable width columns in the first place (& then only when updates actually cause rows to spill off the page). I wasn’t aware that deletes could potentially cause forwarding to occur under snapshot isolation, so thanks for the great info.

    Reply

  2. Hi Paul,

    In index case + ghost cleanup task, it can also introduce page split when delete occurs + insert also happens for the same key. so this in 1 hand introduces optimization in case of rollback and in another hand this causes page split.

    regards,
    Michel

    Reply

  3. Hi Paul,

    In availability group, there is documented affect on secondary. is the same effect in case of mirroring..

    Reply

  4. Hi Paul,
    thank you for explanation, but what about rollback in heap in case of INSERT?
    Have there been any changes in how rollback acts on heaps since SQL Server 2012 was released?
    On my 2012 and 2014 instances I note that no deallocations occur in following scenario:

    create table dbo.t(col char(8000));

    begin tran

    insert into dbo.t(col)
    select top 10000 ‘a’
    from sys.all_columns c1 cross join sys.all_columns c2;

    rollback;

    In the transaction log after rollback I only found 10000 rows with LOP_SET_FREE_SPACE (LCX_PFS )
    and 10000 rows with LOP_DELETE_ROWS (LCX_HEAP),
    that’s different from what I’ve seen in SQL Server 2008 R2 where besides these 20000 rows I clearly saw heap page deallocations
    (log context of LCX_PFS for LOP_MODIFY_ROW in transaction log) and extent deallocations (LOP_SET_BITS in context LCX_GAM).
    I checked out the sp_spaceused result for my heap and it showed no changes as if it was no rollback at all.
    DBCC PAGE (‘db1’, 1, 1, 3) still shows me (1:296) – (1:8087) = ***ALLOCATED*** 0_PCT_FULL
    whereas the same command after the same test on SQL Server 2008 R2 instance returned (1:3292) – (1:8087) = ***NOT ALLOCATED*** 0_PCT_FULL.
    So did they change the implementation of rollback for heaps and is this fact documented regularly?

    Reply

  5. Paul sir, PFS maintains free page space in a page and have learned from you that it maintains it thru 4 bytes like 95% full so and so. We have come across where in heaps we had sufficient space available but it didn’t allow insert due to 96% full. Does same happen to indexes. Can this be the reason of never find page density be 100%. Thank you

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course