{"id":919,"date":"2009-03-19T10:44:00","date_gmt":"2009-03-19T10:44:00","guid":{"rendered":"\/blogs\/paul\/post\/Ghost-cleanup-redux.aspx"},"modified":"2016-06-06T08:13:39","modified_gmt":"2016-06-06T15:13:39","slug":"ghost-cleanup-redux","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/","title":{"rendered":"Ghost cleanup redux"},"content":{"rendered":"

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see <\/span>Inside the Storage Engine: Ghost cleanup in depth<\/span><\/a>. 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?<\/em> The answer is no, not during normal processing. <\/span><\/p>\n

When snapshot isolation is enabled, deletes from a heap are<\/em> 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\u00a0even deeper case in this post<\/a>.<\/span><\/p>\n

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. <\/span><\/p>\n

 <\/p>\n

CREATE TABLE t1 (c1 CHAR (10));
\nCREATE CLUSTERED INDEX t1c1 on t1 (c1);
\nGO <\/span><\/p>\n

CREATE TABLE t2 (c1 CHAR (10));
\nGO <\/span><\/p>\n

INSERT INTO t1 VALUES (‘PAUL’);
\nINSERT INTO t1 VALUES (‘KIMBERLY’); <\/span><\/p>\n

INSERT INTO t2 VALUES (‘PAUL’);
\nINSERT INTO t2 VALUES (‘KIMBERLY’);
\nGO <\/span><\/p>\n

— prevent random background transactions
\nALTER DATABASE GhostTest SET AUTO_CREATE_STATISTICS OFF;
\nGO <\/span><\/p>\n

BEGIN TRAN DelFromClust;
\nDELETE FROM t1 WHERE c1=’KIMBERLY’;
\nROLLBACK TRAN;
\nGO <\/span><\/p>\n

BEGIN TRAN DelFromHeap;
\nDELETE FROM t2 WHERE c1=’KIMBERLY’;
\nROLLBACK TRAN;
\nGO <\/span><\/p>\n

SELECT * FROM ::fn_dblog (null, null);
\nGO<\/span> <\/span><\/p><\/blockquote>\n

 <\/p>\n

Here’s a\u00a0portion of the results from looking in the transaction log. The line of code where I turn off auto-update stats is just to prevent\u00a0the\u00a0auto-create transactions from cluttering up my view of the transaction log. <\/span><\/p>\n

\"\"<\/span><\/p>\n

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<\/span> log record, plus the setting of the ‘this page has at least one ghost record’ in the PFS byte for that page.<\/span><\/p>\n

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

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.<\/span><\/p>\n

Hope this helps.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,48,62,86,98,100],"tags":[],"class_list":["post-919","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-inside-the-storage-engine","category-on-disk-structures","category-sql-server-2008","category-transaction-log","category-undocumented-commands"],"yoast_head":"\nGhost cleanup redux - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Ghost cleanup redux - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-19T10:44:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-06-06T15:13:39+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\",\"name\":\"Ghost cleanup redux - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-03-19T10:44:00+00:00\",\"dateModified\":\"2016-06-06T15:13:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Ghost cleanup redux\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Ghost cleanup redux - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/","og_locale":"en_US","og_type":"article","og_title":"Ghost cleanup redux - Paul S. Randal","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-19T10:44:00+00:00","article_modified_time":"2016-06-06T15:13:39+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/","name":"Ghost cleanup redux - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-03-19T10:44:00+00:00","dateModified":"2016-06-06T15:13:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Ghost cleanup redux"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/919","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=919"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/919\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=919"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}