{"id":1148,"date":"2007-10-19T00:37:43","date_gmt":"2007-10-19T00:37:43","guid":{"rendered":"\/blogs\/paul\/post\/More-on-Database-Mirroring-performance-and-index-maintenance.aspx"},"modified":"2017-04-13T09:54:18","modified_gmt":"2017-04-13T16:54:18","slug":"more-on-database-mirroring-performance-and-index-maintenance","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/","title":{"rendered":"More on Database Mirroring performance and index maintenance"},"content":{"rendered":"<p><FONT face=Verdana><FONT size=2><br \/>\n<P>There&#8217;s been some discussion over on the <A href=\"http:\/\/www.sqlservercentral.com\/Forums\/\">SQL Server Central forums<\/A> about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs.<\/P><br \/>\n<P>Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are <EM>fully logged<\/EM>. It all comes down to the amount of transaction log generated and whether this causes a problem.<\/P><br \/>\n<UL><br \/>\n<LI>In synchronous mirroring, the additional log being generated could overload the network link between the principal and mirror. This will&nbsp;slow down the hardening of regular transactions in the mirror&#8217;s transaction log, and thus led to a decrease in transaction throughput on the principal.<br \/>\n<LI>In asynchronous mirroring, the additional log being generated could again overload the network link &#8211; but this time there&#8217;s no requirement for transactions to harden in the mirror before they can commit on the principal, so instead the SEND queue on the principal gets really large. Now, in asynchronous mirroring the SEND queue represents the amount of work that will be lost if a failover occurs, as its all the transaction log that hasn&#8217;t yet been sent to the mirror. So, in asynchronous mode,&nbsp;a large index rebuild operation could lead to increase exposure to data loss in the event of a failover.<\/LI><\/UL><br \/>\n<P>In SS2008, the log stream compression I blogged about <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-performance-boost-for-database-mirroring\/\">here<\/a> should go a long way to alleviating this problem. There are three ways I can think of to reduce the amount of transaction log generated by and index maintenance plan in SS2005:<\/P><br \/>\n<OL><br \/>\n<LI>Use a <EM>potentially<\/EM> less expensive (in terms of logging) solution for removing index fragmentation. Doing an index rebuild is guaranteed to generate an equivalent amount of transaction log to the size of the index being rebuilt, no matter how much fragmentation there is &#8211; because an index rebuild <EM>always<\/EM> rebuilds the entire index. The alternative is to do an index <EM>reorganize<\/EM>, either using my old <FONT face=\"Courier New\">DBCC INDEXDEFRAG<\/FONT> or the new <FONT face=\"Courier New\">ALTER INDEX &#8230; REORGANIZE<\/FONT>. These will only generate transaction log when index pages are compacted and reorganized &#8211; so for less heavily fragmented indexes. There&#8217;s no hard and fast rule here but I generally say where <FONT face=\"Courier New\">Logical Scan Fragmentation<\/FONT>\/<FONT face=\"Courier New\">Average Fragmentation in Percent<\/FONT> from <FONT face=\"Courier New\">DBCC SHOWCONTIG<\/FONT>\/<FONT face=\"Courier New\">sys.dm_db_index_physical_stats<\/FONT>, respectively, is less than 30%. You also need to consider page density too &#8211; but really this is a topic for a whole other post.<br \/>\n<LI>Be very selective on which indexes you choose to rebuild\/reorganize. A lot of people have a maintenance plan that does this for every index every week, without checking whether the index is even fragmented or whether removing fragmentation for an index improves workload performance. Again, this a whole other topic but there is an old whitepaper for SS2000 I helped with that describes some of this &#8211; <a href=\"https:\/\/technet.microsoft.com\/library\/Cc966523\">Microsoft SQL Server 2000 Index Defragmentation Best Practices<\/a>.<br \/>\n<LI>Partition the tables\/indexes so that the <EM>changing<\/EM> portion of the data is the only portion that&#8217;s affected by index maintenance. If most of your data is read-only, there&#8217;s no point in having it included in reindex\/reorganize operations, right? Kimberly recently wrote a blog post about such an architecture <a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/sql-server-2008-offers-partition-level-lock-escalation-excellent-but\/\">here<\/a>.<\/LI><\/OL><br \/>\n<P>For more info on database mirroring performance considerations, checkout the whitepaper <a href=\"https:\/\/technet.microsoft.com\/library\/Cc917681\">Database Mirroring: Best Practices and Performance Considerations<\/a>. There&#8217;s also a slide deck presentation based on this whitepaper that&#8217;s been presented at various TechEds (I did it in China and Hong Kong last year and Kimberly did it in South Africa last year) &#8211; you can download it from the Hong Kong website here.<\/P><br \/>\n<P>The bottom line is when mirroring is in the mix, you need to be more intentional with your database maintenance.<\/P><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There&#8217;s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [&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,32,66,98],"tags":[],"class_list":["post-1148","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-database-mirroring","category-performance-tuning","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>More on Database Mirroring performance and 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\/more-on-database-mirroring-performance-and-index-maintenance\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"More on Database Mirroring performance and index maintenance - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"There&#8217;s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-19T00:37:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:18+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\/more-on-database-mirroring-performance-and-index-maintenance\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/\",\"name\":\"More on Database Mirroring performance and index maintenance - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-19T00:37:43+00:00\",\"dateModified\":\"2017-04-13T16:54:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"More on Database Mirroring performance and 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":"More on Database Mirroring performance and 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\/more-on-database-mirroring-performance-and-index-maintenance\/","og_locale":"en_US","og_type":"article","og_title":"More on Database Mirroring performance and index maintenance - Paul S. Randal","og_description":"There&#8217;s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-19T00:37:43+00:00","article_modified_time":"2017-04-13T16:54:18+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\/more-on-database-mirroring-performance-and-index-maintenance\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/","name":"More on Database Mirroring performance and index maintenance - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-19T00:37:43+00:00","dateModified":"2017-04-13T16:54:18+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/more-on-database-mirroring-performance-and-index-maintenance\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"More on Database Mirroring performance and 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\/1148","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=1148"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1148\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}