{"id":698,"date":"2010-04-29T08:22:00","date_gmt":"2010-04-29T08:22:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx"},"modified":"2017-07-30T13:37:57","modified_gmt":"2017-07-30T20:37:57","slug":"a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/","title":{"rendered":"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\">Another quickie but goodie before the finale tomorrow!<\/p>\n<p style=\"text-align: justify;\"><strong>Myth #29:<\/strong> <em>fix heap fragmentation by creating and dropping a clustered index. <\/em><\/p>\n<p style=\"text-align: justify;\"><em><strong><u>Nooooooooooooo!!!<\/u><\/strong><\/em><\/p>\n<p style=\"text-align: justify;\">This is just about one of the worst things you could do outside of shrinking a database.<\/p>\n<p style=\"text-align: justify;\">If you run <em>sys.dm_db_index_physical_stats<\/em> (or my old <em>DBCC SHOWCONTIG<\/em>) on a heap (a table without a clustered index) and it shows some fragmentation, don&#8217;t <strong><u><em>EVER<\/em><\/u><\/strong> create and drop a clustered index to build a nice, contiguous heap. Do yourself a favor and just create the well-chosen clustered index and leave it there &#8211; there&#8217;s a ton of info out there on choosing a good clustering key &#8211; narrow+static+unique+ever-increasing is what you need. Kimberly has a blog post from 2005(!) that sums things up: <em><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/ever-increasing-clustering-key-the-clustered-index-debate-again\/\">Ever-increasing clustering key &#8211; the Clustered Index Debate&#8230;&#8230;&#8230;.again!<\/a><\/em>\u00a0and I&#8217;ve got <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/an-example-of-a-nasty-cluster-key\/\">An example of a nasty cluster key<\/a><\/em>.<\/p>\n<p style=\"text-align: justify;\">Yes, you can use <em>ALTER TABLE &#8230; REBUILD<\/em> in SQL Server 2008+ to remove heap fragmentation, but that is almost as bad as creating and dropping a clustered index!<\/p>\n<p style=\"text-align: justify;\">Why am I having a <a href=\"https:\/\/en.wiktionary.org\/wiki\/conniption_fit\">conniption fit<\/a> about this? Well, every record in a nonclustered index has to link back to the matching row in the table (either a heap or clustered index &#8211; you can&#8217;t have both &#8211; see Kimberly&#8217;s recent SQL Server Magazine blog post <em><a href=\"http:\/\/sqlmag.com\/blogs\/sql-server-questions-answered\/sql-server-questions-answered\/tabid\/1977\/entryid\/12748\/Default\">What Happens if I Drop a Clustered Index?<\/a><\/em> for an explanation). The link takes the form of:<\/p>\n<ul style=\"text-align: justify;\">\n<li>If the table is a heap, the actual physical location of the table record (data file:page number:record number)<\/li>\n<li>If the table has a clustered index, the clustering key(s)<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">The blog post link at the bottom of this post explains in a lot more detail.<\/p>\n<p style=\"text-align: justify;\">If you create a clustered index, all the linkages to the heap records are no longer valid and so all the nonclustered indexes must be rebuilt automatically to pick up the new clustering key links. If you drop the clustered index again, all the clustering key links are now invalid so all the nonclustered indexes must be rebuilt automatically to pick up the new heap physical location links.<\/p>\n<p style=\"text-align: justify;\">In other words, if you create and then drop a clustered index, all the nonclustered indexes are rebuilt twice. Nasty.<\/p>\n<p style=\"text-align: justify;\">If you think you can use <em>ALTER TABLE &#8230; REBUILD<\/em> to fix heap fragmentation, you can, but it causes all the nonclustered indexes to be rebuilt as the heap record locations obviously change.<\/p>\n<p style=\"text-align: justify;\">Now, what about if you *rebuild* a clustered index? Well, that depends on what version you&#8217;re on and whether you&#8217;re doing a simple rebuild or changing the definition of the index. One major point of misconception is that moving a clustered index or partitioning it changes the cluster keys &#8211; it doesn&#8217;t. For a full list of when the nonclustered indexes need to be rebuilt, see <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-what-happens-to-non-clustered-indexes-when-the-table-structure-is-changed\/\">Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,47,48,61],"tags":[],"class_list":["post-698","post","type-post","status-publish","format-standard","hentry","category-fragmentation","category-indexes-from-every-angle","category-inside-the-storage-engine","category-misconceptions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - 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\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-29T08:22:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-30T20:37:57+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=\"3 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\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/\",\"name\":\"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-29T08:22:00+00:00\",\"dateModified\":\"2017-07-30T20:37:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation\"}]},{\"@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":"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - 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\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-29T08:22:00+00:00","article_modified_time":"2017-07-30T20:37:57+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/","name":"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-29T08:22:00+00:00","dateModified":"2017-07-30T20:37:57+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (29\/30) fixing heap fragmentation"}]},{"@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\/698","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=698"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/698\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=698"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}