{"id":918,"date":"2009-03-21T15:50:00","date_gmt":"2009-03-21T15:50:00","guid":{"rendered":"\/blogs\/paul\/post\/Importance-of-index-maintenance.aspx"},"modified":"2017-11-16T13:05:08","modified_gmt":"2017-11-16T21:05:08","slug":"importance-of-index-maintenance","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/","title":{"rendered":"Importance of index maintenance"},"content":{"rendered":"<p style=\"text-align: justify;\">Last week\u2019s survey was on what kind of regular index maintenance you perform (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/weekly-survey-index-maintenance-plan\/\">here<\/a> for the survey) as a way of kicking off a new series I\u2019m writing around index maintenance. Here are the results as of 3\/21\/09 \u2013 I find them very encouraging:<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg\" alt=\"\" width=\"578\" height=\"336\" \/><\/p>\n<p style=\"text-align: justify;\">As you can see, about\u00a02\/5 of respondents\u00a0are performing some form of analysis-based fragmentation removal (answers 5+6), which I consider the best way to perform index maintenance, if you\u2019re willing to invest the time involved to set it up. It allows the least amount of work to be performed, for the most targeted performance gains \u2013 and so is especially appropriate for 24&#215;7 systems where there\u2019s a minimal or non-existent maintenance window.<\/p>\n<p style=\"text-align: justify;\">The next best option is to do all rebuilds or all defrags based on a fragmentation threshold (answers 3+4), which about\u00a01\/5 of respondents do. This also allows work to be limited, but by choosing only a single method of removing fragmentation, there are pros and cons. Now, the survey was limited to a number of questions so I couldn\u2019t explore what the threshold is that people are using (e.g. logical fragmentation, page density, extent fragmentation, or something else). Some measures are good to use and some not so good, and I\u2019ll be exploring the various counters and ways of determining fragmentation as the series progresses.<\/p>\n<p style=\"text-align: justify;\">Either of the options to operate on all indexes regardless of fragmentation (options 1+2) can lead to lots of wasted resources (disk space, transaction log space, I\/Os, CPU) by operating on indexes that are not fragmented in the first place, or for which fragmentation removal has no benefit for workload performance. About 1\/3 of respondents do this. This isn\u2019t surprising to me as rebuild-all-the-indexes-every-night\/week is a very common index maintenance plan for \u201cinvoluntary DBAs\u201d who know that index maintenance is important, but don\u2019t have the knowledge or training to implement a more sophisticated maintenance plan. This growing size of this group of people is one of the main reasons I\u2019m going to write this series.<\/p>\n<p style=\"text-align: justify;\">Doing absolutely nothing for index maintenance, which about 1\/10 do, is usually not a good idea, as indexes in a database that\u2019s not read-only commonly become fragmented over time. However, these people may know they don\u2019t suffer from fragmentation issues, or that removing fragmentation has no effect on workload performance. However, my suspicion (based on what I see in the field) is that some of these respondents don\u2019t realize the benefits of performing index maintenance.<\/p>\n<p style=\"text-align: justify;\">You may be surprised to hear that I don\u2019t consider doing nothing to be the worst choice. That dubious honor goes to answer 7 \u2013 doing any kind of index maintenance followed by a database shrink operation \u2013 as\u00a03 respondents do. A post-maintenance shrink operation may well undo some of the benefits of the maintenance by introducing massive amounts of index fragmentation \u2013 see my blog post <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/auto-shrink-turn-it-off\/\">Auto-shrink &#8211; turn it OFF!<\/a>\u00a0for details of how bad this can get.<\/p>\n<p style=\"text-align: justify;\">I\u2019m actually pretty encouraged by these results though. Compared to how things were around 1999 when I wrote <em>DBCC INDEXDEFRAG<\/em> and <em>DBCC SHOWCONTIG<\/em> for SQL Server 2000,\u00a0these results show that knowledge in the field (or at least in the group that reads my blog and responded) has vastly improved. My aim for the forthcoming series about index fragmentation and maintenance is to increase knowledge a lot more broadly.<\/p>\n<p>Next up &#8211; this week&#8217;s survey. Thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week\u2019s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I\u2019m writing around index maintenance. Here are the results as of 3\/21\/09 \u2013 I find them very encouraging: As you can see, about\u00a02\/5 of respondents\u00a0are performing some form [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,42,47,52,86,91],"tags":[],"class_list":["post-918","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-fragmentation","category-indexes-from-every-angle","category-involuntary-dba","category-sql-server-2008","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Importance of index maintenance - 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\/importance-of-index-maintenance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Importance of index maintenance - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Last week\u2019s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I\u2019m writing around index maintenance. Here are the results as of 3\/21\/09 \u2013 I find them very encouraging: As you can see, about\u00a02\/5 of respondents\u00a0are performing some form [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-21T15:50:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-11-16T21:05:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg\" \/>\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\/importance-of-index-maintenance\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/\",\"name\":\"Importance of index maintenance - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg\",\"datePublished\":\"2009-03-21T15:50:00+00:00\",\"dateModified\":\"2017-11-16T21:05:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Importance of index maintenance\"}]},{\"@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":"Importance of index maintenance - 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\/importance-of-index-maintenance\/","og_locale":"en_US","og_type":"article","og_title":"Importance of index maintenance - Paul S. Randal","og_description":"Last week\u2019s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I\u2019m writing around index maintenance. Here are the results as of 3\/21\/09 \u2013 I find them very encouraging: As you can see, about\u00a02\/5 of respondents\u00a0are performing some form [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-21T15:50:00+00:00","article_modified_time":"2017-11-16T21:05:08+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg","type":"","width":"","height":""}],"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\/importance-of-index-maintenance\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/","name":"Importance of index maintenance - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg","datePublished":"2009-03-21T15:50:00+00:00","dateModified":"2017-11-16T21:05:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/indexmaintresults.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-index-maintenance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Importance of index maintenance"}]},{"@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\/918","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=918"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/918\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}