{"id":4778,"date":"2017-08-03T12:06:34","date_gmt":"2017-08-03T19:06:34","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4778"},"modified":"2017-11-28T10:13:46","modified_gmt":"2017-11-28T18:13:46","slug":"sqlskills-sql101-rebuild-vs-reorganize","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/","title":{"rendered":"SQLskills SQL101: REBUILD vs. REORGANIZE"},"content":{"rendered":"

As\u00a0Kimberly blogged about earlier this year<\/a>, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

Often when discussing index fragmentation I find that people aren’t aware of all the differences between rebuilding an index (using ALTER INDEX … REBUILD<\/em>) and reorganizing an index (using ALTER INDEX … REORGANIZE<\/em>), so that’s the topic for this post. Everything I say below applies to row-based indexes only (i.e. not columnstore indexes), and equally to a clustered index or a nonclustered index.<\/p>\n

Space Required<\/strong><\/p>\n

Rebuilding an index requires building a new index before dropping the old index, regardless of\u00a0the amount of fragmentation present in the old index. This means you need to have enough free space to accommodate the new index.<\/p>\n

Reorganizing an index first squishes the index rows together to try to deallocate some index pages, and then shuffles the remaining pages in place to make their physical (allocation) order the same as\u00a0the logical (key) order. This only requires a single 8-KB page, as a temporary storage for pages being moved around. So an index reorganize is extremely space efficient, and is one of the reasons I wrote the original DBCC INDEXDEFRAG<\/em> for SQL Server 2000 (the predecessor of ALTER INDEX … REORGANIZE<\/em>).<\/p>\n

If you have space constraints, and can\u2019t make use of single-partition rebuild, reorganizing is the way to go.<\/p>\n

Algorithm Speed<\/strong><\/p>\n

An index rebuild will always build a new index, even if there\u2019s no fragmentation. The length of\u00a0time the rebuild takes is related to the size of the index, not the amount of fragmentation in it.<\/p>\n

Reorganizing an index only deals with the fragmentation that exists, and the more fragmentation\u00a0there is, the longer a reorganize will take.<\/p>\n

This means that for a lightly fragmented index (e.g. less than 30% fragmentation), it\u2019s generally\u00a0faster to reorganize the index, but for a more heavily fragmented index, it\u2019s generally faster to\u00a0just rebuild the index. This is why you may have seen thresholds of 0 to 5-10% do nothing, 5-10% to 30% reorganize, 30%+ rebuild. I created this guidance while I was at Microsoft – see here<\/a>.<\/p>\n

Transaction Log Generated<\/strong><\/p>\n

In the FULL recovery mode, an index rebuild is fully logged, so the transaction log will have to\u00a0accommodate the full size of the index in a single transaction. This also means the entire\u00a0generated transaction log may need to be mirrored, sent to your AG replicas, scanned by\u00a0replication, backed up, and so on.<\/p>\n

In the SIMPLE and BULK_LOGGED recovery modes, the amount of transaction log generated\u00a0by an offline index rebuild will be minimal (online index rebuild is always fully logged) \u2013 just the allocations of pages and extents. However, the\u00a0next log backup performed (either in BULK_LOGGED or after switching to FULL) will also contain\u00a0all the extents changed by the rebuild, and so the log backup will be roughly the same size as if the rebuild was\u00a0done in the FULL recovery mode. The benefits are in time and the fact that the transaction log\u00a0itself does not have to accommodate the full size of the index during the rebuild in a single transaction.<\/p>\n

In all recovery modes, reorganizing an index is fully logged, but is performed as a series of small\u00a0transactions so should not cause the transaction log to grow inordinately. And of course,\u00a0transaction log is only generated for the operations performed, which may be less\u00a0for a reorganize as it only deals with fragmentation that exists.<\/p>\n

Locks Required<\/strong><\/p>\n

An offline index rebuild of any index holds a\u00a0schema-modification (i.e. super-exclusive) table lock \u2013 no updates or reads of the entire table.<\/p>\n

An\u00a0online index rebuild of any index acquires a short-term shared table lock at the start of the\u00a0operation, holds an intent-shared table lock throughout the operation (which will only block\u00a0exclusive and schema-modification table locks), and then acquires a short-term schema-modification \u00a0table lock at the end of the operation. \u2018Online\u2019 is a bit of a misnomer. From SQL Server 2014, you can use the WAIT_AT_LOW_PRIORITY<\/em> option to delay the potential for blocking – see this blog post<\/a>.<\/p>\n

An index reorganize holds an intent-exclusive table lock throughout the operation, which will\u00a0only block shared, exclusive, and schema-modification table locks. One of the major reasons I wrote DBCC\u00a0INDEXDEFRAG<\/em> for SQL Server 2000 was as an online alternative to DBCC DBREINDEX<\/em>.<\/p>\n

Interruptible or Not<\/strong><\/p>\n

An index rebuild operation cannot be interrupted without it rolling back everything it’s done so far \u2013 it\u2019s atomic – all or nothing. In SQL Server 2017, however, there is a resumable-online index rebuild feature.<\/p>\n

An index reorganize can be interrupted and the worst that will happen is that a single page move\u00a0operation is rolled back.<\/p>\n

Progress Reporting or Not<\/strong><\/p>\n

Index rebuilds do not have proper progress reporting. You can hack it for online index operations\u00a0by looking at the bigintdata1<\/em> column in the Progress Report: Online Index<\/em> Profiler event, which\u00a0happens to show how many rows of the old index have been scanned. You can also hack it for index operations by looking at the number of page reads the SPID has done in sys.dm_exec_requests<\/em>.<\/p>\n

Index reorganize operations populate the percent_complete<\/em> column of sys.dm_exec_requests<\/em> so\u00a0you can easily gauge how much work remains. In fact DBCC INDEXDEFRAG<\/em> also used to do\u00a0progress reporting, but less elegantly, by printing a progress message to your connection every 30 seconds.<\/p>\n

Statistics<\/strong><\/p>\n

An index rebuild will always rebuild the index column statistics with the equivalent of a full\u00a0scan (or sampled, for an index partition or if the index is partitioned).<\/p>\n

An index reorganize does not see a total view of the index and so cannot update statistics,\u00a0meaning that manual index statistics maintenance is required.<\/p>\n

Summary<\/strong><\/p>\n

As you can see, there are quite a few major differences between rebuilding and reorganizing, but\u00a0there\u2019s no right answer as to which one you should use \u2013 that\u2019s your choice.<\/p>\n

If you have an index maintenance routine that always rebuilds and never considers reorganizing, you should\u00a0reconsider. It\u2019s usually better to reorganize a lightly\u00a0fragmented index and rebuild a more heavily fragmented index \u2013 to save time and resources. You\u2019ll find that most index maintenance products and freely-available scripts allow you to make\u00a0that choice.<\/p>\n

And as always, rather than writing your own index maintenance solution, I recommend Ola Hallengren’s free code<\/a> (yes, other people have done similar, but I think Ola’s is by far the best and most widely used).<\/p>\n","protected":false},"excerpt":{"rendered":"

As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Often […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,108],"tags":[],"class_list":["post-4778","post","type-post","status-publish","format-standard","hentry","category-fragmentation","category-sql101"],"yoast_head":"\nSQLskills SQL101: REBUILD vs. REORGANIZE - 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\/sqlskills-sql101-rebuild-vs-reorganize\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: REBUILD vs. REORGANIZE - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Often […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-03T19:06:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-11-28T18:13:46+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=\"6 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\/sqlskills-sql101-rebuild-vs-reorganize\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/\",\"name\":\"SQLskills SQL101: REBUILD vs. REORGANIZE - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-08-03T19:06:34+00:00\",\"dateModified\":\"2017-11-28T18:13:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: REBUILD vs. REORGANIZE\"}]},{\"@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":"SQLskills SQL101: REBUILD vs. REORGANIZE - 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\/sqlskills-sql101-rebuild-vs-reorganize\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: REBUILD vs. REORGANIZE - Paul S. Randal","og_description":"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Often […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/","og_site_name":"Paul S. Randal","article_published_time":"2017-08-03T19:06:34+00:00","article_modified_time":"2017-11-28T18:13:46+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/","name":"SQLskills SQL101: REBUILD vs. REORGANIZE - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-08-03T19:06:34+00:00","dateModified":"2017-11-28T18:13:46+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-rebuild-vs-reorganize\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: REBUILD vs. REORGANIZE"}]},{"@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\/4778","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=4778"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4778\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4778"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4778"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4778"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}