{"id":1133,"date":"2007-11-06T23:17:02","date_gmt":"2007-11-06T23:17:02","guid":{"rendered":"\/blogs\/paul\/post\/Conference-Questions-Pot-Pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations.aspx"},"modified":"2013-01-01T21:07:10","modified_gmt":"2013-01-02T05:07:10","slug":"conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/","title":{"rendered":"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations"},"content":{"rendered":"<p><FONT face=Verdana><FONT size=2><br \/>\n<P>A couple more questions from the last couple of classes.<\/P><br \/>\n<P><STRONG>Q1) Why doesn&#8217;t performing an index rebuild alter the fragmentation?<\/STRONG><\/P><br \/>\n<P><STRONG>A1) <\/STRONG>Here are the possibilities &#8211; all of which I&#8217;ve seen happen:<\/P><br \/>\n<UL><br \/>\n<LI>There isn&#8217;t an index &#8211; either <FONT face=\"Courier New\">DBCC DBREINDEX<\/FONT>&nbsp;or <FONT face=\"Courier New\">ALTER INDEX &#8230; REBUILD<\/FONT> are being run on a table that only has a heap, and so the (extent) fragmentation of the heap isn&#8217;t changing because there&#8217;s no way to rebuild a heap (except by the heavily NOT recommended method of creating and then dropping a clustered index).<br \/>\n<LI>The index is too small. An index with only a handful of pages may not show any changes in fragmentation because all the pages are single, mixed pages (see my <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-anatomy-of-an-extent\/\">previous post<\/a> on extent types for more info) and so rebuilding the index does nothing.<br \/>\n<LI>The workload and schema are such that by the time the rebuild has finished and the fragmentation calculation has been done again, the index is already getting fragmented again.<br \/>\n<LI>The Extent Scan Fragmentation result from <FONT face=\"Courier New\">DBCC SHOWCONTIG<\/FONT> is being used to gauge fragmentation for an index stored in a filegroup with multiple files. The Extent Scan Fragmentation in <FONT face=\"Courier New\">DBCC SHOWCONTIG<\/FONT> does not cope with multiple files (as is documented in Books Online) and so the value may even go UP in some cases!<\/LI><\/UL><br \/>\n<P><STRONG>Q2) What operations take advantage of minimal-logging when the recovery mode is BULK_LOGGED?<\/STRONG><\/P><br \/>\n<P><STRONG>A2)<\/STRONG> The list is very small &#8211; 4 four classes of operations:<\/P><br \/>\n<UL><br \/>\n<LI>Index builds, rebuilds, or drop of a clustered index&nbsp;(NOT index defrags with <FONT face=\"Courier New\">DBCC INDEXDEFRAG<\/FONT> or <FONT face=\"Courier New\">ALTER INDEX &#8230; REORGANIZE<\/FONT> &#8211; this is a common misconception).<br \/>\n<LI>Bulk load operations (i.e. <FONT face=\"Courier New\">BCP<\/FONT>, <FONT face=\"Courier New\">INSERT &#8230; SELECT * FROM OPENROWSET (BULK&#8230;)<\/FONT>, and <FONT face=\"Courier New\">BULK INSERT<\/FONT>).<br \/>\n<LI>Insert or appends of LOB data (either using <FONT face=\"Courier New\">WRITETEXT<\/FONT>\/<FONT face=\"Courier New\">UPDATETEXT<\/FONT> for <FONT face=\"Courier New\">TEXT<\/FONT>\/<FONT face=\"Courier New\">NTEXT<\/FONT>\/<FONT face=\"Courier New\">IMAGE<\/FONT> data types, or <FONT face=\"Courier New\">UPDATE<\/FONT> with a <FONT face=\"Courier New\">.WRITE<\/FONT> clause).<br \/>\n<LI>SELECT INTO operations on permanent tables.<\/LI><\/UL><br \/>\n<P>For these operations, only the allocations are logged in the transaction log. Any extents that are allocated and changed through a minimally-logged operation are marked in the ML bitmaps (one for every 4GB of each file) and then the next transaction log backup will also read all those extents and include them in the backup.<\/P><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A couple more questions from the last couple of classes. Q1) Why doesn&#8217;t performing an index rebuild alter the fragmentation? A1) Here are the possibilities &#8211; all of which I&#8217;ve seen happen: There isn&#8217;t an index &#8211; either DBCC DBREINDEX&nbsp;or ALTER INDEX &#8230; REBUILD are being run on a table that only has a heap, [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,47,98],"tags":[],"class_list":["post-1133","post","type-post","status-publish","format-standard","hentry","category-conference-questions-pot-pourri","category-indexes-from-every-angle","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - 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\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A couple more questions from the last couple of classes. Q1) Why doesn&#8217;t performing an index rebuild alter the fragmentation? A1) Here are the possibilities &#8211; all of which I&#8217;ve seen happen: There isn&#8217;t an index &#8211; either DBCC DBREINDEX&nbsp;or ALTER INDEX &#8230; REBUILD are being run on a table that only has a heap, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-11-06T23:17:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-02T05:07:10+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\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/\",\"name\":\"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-11-06T23:17:02+00:00\",\"dateModified\":\"2013-01-02T05:07:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations\"}]},{\"@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":"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - 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\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/","og_locale":"en_US","og_type":"article","og_title":"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - Paul S. Randal","og_description":"A couple more questions from the last couple of classes. Q1) Why doesn&#8217;t performing an index rebuild alter the fragmentation? A1) Here are the possibilities &#8211; all of which I&#8217;ve seen happen: There isn&#8217;t an index &#8211; either DBCC DBREINDEX&nbsp;or ALTER INDEX &#8230; REBUILD are being run on a table that only has a heap, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/","og_site_name":"Paul S. Randal","article_published_time":"2007-11-06T23:17:02+00:00","article_modified_time":"2013-01-02T05:07:10+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\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/","name":"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-11-06T23:17:02+00:00","dateModified":"2013-01-02T05:07:10+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-3-unexpected-index-rebuild-results-and-bulk-logged-operations\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations"}]},{"@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\/1133","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=1133"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1133\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}