{"id":756,"date":"2010-01-08T21:32:00","date_gmt":"2010-01-08T21:32:00","guid":{"rendered":"\/blogs\/paul\/post\/Turning-off-the-ghost-cleanup-task-for-a-performance-gain.aspx"},"modified":"2017-04-13T09:50:51","modified_gmt":"2017-04-13T16:50:51","slug":"turning-off-the-ghost-cleanup-task-for-a-performance-gain","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/","title":{"rendered":"Turning off the ghost cleanup task for a performance gain"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">I&#39;ve blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn&#39;t find the trace flag to turn it off. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">My previous blog posts about this are: <\/font>\n<\/p>\n<ul>\n<li>\n<div>\n\t<a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-ghost-cleanup-in-depth\/\"><font face=\"verdana,geneva\" size=\"2\">Inside the Storage Engine: Ghost cleanup in depth<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> <\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/ghost-cleanup-redux\/\"><font face=\"verdana,geneva\" size=\"2\">Ghost cleanup redux<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> <\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">These explain what ghost records are and how the ghost cleanup process works. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">On big systems it&#39;s possible for the ghost cleanup process to fall behind the rest of the system, with no hope of it catching up. It&#39;s a single-threaded task &#8211; so imagine a 16-way box with lots of deletes occuring, and a single CPU spending a few seconds every 5 seconds trying to remove all the ghosted records resulting from the deletes of all the other CPUs. It&#39;s pretty obvious that the ghost cleanup process is going to lag behind. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The problem with this scenario is that the ghost cleanup process will still pop up every 5 seconds (every 10 on 2008) and start removing ghost records, potentially causing performance issues by keeping pages in the buffer pool, generating log records, and causing physical IOs. The ghost cleanup task is also one of the background processes that can cause IOs to occur on what looks like a totally quiescent system. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">There is a way to turn off the ghost cleanup task, using trace flag 661, as documented in <\/font><a href=\"https:\/\/support.microsoft.com\/kb\/920093\"><font face=\"verdana,geneva\" size=\"2\">KB 920093<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">. Be careful though!!! If you disable the ghost cleanup task, the space taken up by deleted records will *NOT* be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.<\/font>\n<\/p>\n<p>\n<font size=\"2\">One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Turning on this trace flag can be useful as a performance gain on systems with very heavy delete workloads, but only as long as you&#39;re careful about it. It&#39;s not something that&#39;s generally recommended but it may be useful to you.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Enjoy! <\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#39;ve blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn&#39;t find the trace flag to turn it off. My previous blog [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,66,96],"tags":[],"class_list":["post-756","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-performance-tuning","category-trace-flags"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Turning off the ghost cleanup task for a performance gain - 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\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Turning off the ghost cleanup task for a performance gain - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I&#039;ve blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn&#039;t find the trace flag to turn it off. My previous blog [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-01-08T21:32:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:50:51+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\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\",\"name\":\"Turning off the ghost cleanup task for a performance gain - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-01-08T21:32:00+00:00\",\"dateModified\":\"2017-04-13T16:50:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Turning off the ghost cleanup task for a performance gain\"}]},{\"@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":"Turning off the ghost cleanup task for a performance gain - 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\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/","og_locale":"en_US","og_type":"article","og_title":"Turning off the ghost cleanup task for a performance gain - Paul S. Randal","og_description":"I&#39;ve blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn&#39;t find the trace flag to turn it off. My previous blog [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/","og_site_name":"Paul S. Randal","article_published_time":"2010-01-08T21:32:00+00:00","article_modified_time":"2017-04-13T16:50:51+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\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/","name":"Turning off the ghost cleanup task for a performance gain - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-01-08T21:32:00+00:00","dateModified":"2017-04-13T16:50:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Turning off the ghost cleanup task for a performance gain"}]},{"@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\/756","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=756"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/756\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=756"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=756"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=756"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}