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);
CREATE TABLE t2 (c1 CHAR (10));
INSERT INTO t1 VALUES (‘PAUL’);
INSERT INTO t1 VALUES (‘KIMBERLY’);
INSERT INTO t2 VALUES (‘PAUL’);
INSERT INTO t2 VALUES (‘KIMBERLY’);
— prevent random background transactions
ALTER DATABASE GhostTest SET AUTO_CREATE_STATISTICS OFF;
BEGIN TRAN DelFromClust;
DELETE FROM t1 WHERE c1=’KIMBERLY’;
BEGIN TRAN DelFromHeap;
DELETE FROM t2 WHERE c1=’KIMBERLY’;
SELECT * FROM ::fn_dblog (null, null);
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.