{"id":4793,"date":"2017-08-15T11:56:07","date_gmt":"2017-08-15T18:56:07","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4793"},"modified":"2020-03-22T15:13:34","modified_gmt":"2020-03-22T22:13:34","slug":"sqlskills-sql101-log-shipping-performance-problems","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/","title":{"rendered":"SQLskills SQL101: Log shipping performance problems"},"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

One question I’m asked regularly is this:\u00a0\u00a0When our log shipping secondary is applying log backups, sometimes it takes a lot longer than usual.\u00a0Any idea why this might be the case?<\/em><\/p>\n

Log shipping has been around forever, and it\u2019s still a hugely applicable and useful feature for\u00a0very simply maintaining one or more secondary copies of a database. You can also use a secondary copy for reporting, where the restore of the log backup uses the WITH STANDBY<\/em>\u00a0option, leaving the secondary database in an accessible, but read-only state (when the logs aren\u2019t being applied).<\/p>\n

This works as follows:<\/p>\n

    \n
  1. Make sure all users are disconnected from the secondary database<\/li>\n
  2. Write all the log records from the log backup into the secondary database\u2019s log file<\/li>\n
  3. Perform the REDO part of recovery (ensuring that all operations from committed transactions are present in the secondary database)<\/li>\n
  4. Perform the UNDO part of recovery (ensuring that all operations from uncommitted transactions are not present in the secondary database)<\/li>\n<\/ol>\n

    Step 4 writes all the log records generated by the UNDO operations into a special file called the undo file. This means that the secondary database is in read-only mode and is transactionally-consistent so\u00a0that users can access it. The reason the log records are written into the undo file is so that the\u00a0transaction log of the secondary database is not altered in any way, allowing subsequent log backups to be\u00a0restored. If this weren\u2019t the case, the UNDO log records would advance the secondary database\u2019s LSN (Log Sequence Number),\u00a0meaning that subsequent log backup restore operations would fail.<\/p>\n

    When the restore process begins on the secondary database, if an undo file exists, there is another\u00a0step that is performed before steps 2-4 above. This additional step needs to take all the log\u00a0records in the undo file and undo the effects of them \u2013 essentially putting the secondary database back into\u00a0the state as of the end of step 3 from the previous restore. This database state is the same as if the previous log backup had\u00a0been restored using WITH NORECOVERY<\/em> instead of WITH STANDBY<\/em>.<\/p>\n

    The occasional long-running restore problem happens when a log backup is restored that\u00a0contains a long-running transaction that does not commit before the end of the log backup. This\u00a0means that it must be completely undone as part of restoring the log backup (step 4), resulting in\u00a0a very large undo file. This in itself can make restoring a log backup take a lot longer than usual.\u00a0When the next log backup is restored, the additional step that undoes all the log records in the\u00a0undo file has a very large undo file to process and takes much, much longer than usual. And if\u00a0the log backup being restored also has an uncommitted, long-running transaction then it\u2019s the perfect storm as the step 4 will also take a long time. These steps are all made even longer still if the log file has too many VLFs (called VLF fragmentation<\/em>).<\/p>\n

    The situation where I\u2019ve seen this most often is when the primary database is undergoing index maintenance\u00a0and a log backup finishes near the end of a very long-running index rebuild operation of a large\u00a0clustered index. The initial restore of that log backup on the secondary database takes much\u00a0longer than usual to complete because of step 4 in the restore process. The next log backup on\u00a0the primary also completes just before an index rebuild completes. When it is restored on the\u00a0secondary, the whole of the large undo file has to be undone again, then the log restore occurs,\u00a0and then another large undo file is generated to undo the second uncommitted index rebuild.<\/p>\n

    This is a possibility you have to be aware of if the secondary database must be available 24×7 for\u00a0reporting, with only minimal downtime when each log backup is restored. In that case I would\u00a0carefully\u00a0change\u00a0the index maintenance operations and log backups on the primary to ensure\u00a0that only complete, committed index rebuilds are present in the log backups being restored on the\u00a0secondary database. Similar precautions should be taken if you have other, occasional, long-running operations.<\/p>\n

    An alternative would be to move from log shipping to database mirroring or availability groups,\u00a0where the log records are continually being sent from the principal to the mirror database (or\u00a0primary to secondary replica databases, in availability group terms) and there are no extra steps\u00a0involving undoing log operations multiple times.\u00a0With database mirroring, the drawback of this is that reporting would have to use database\u00a0snapshots, so there\u2019s a complexity trade-off involved. With availability groups, the reporting\u00a0would have to use a readable secondary, which can lead to index fragmentation on the primary\u00a0replica, but that can be compensated for with index fill factors (see here for more details<\/a>).<\/p>\n

    So there you have it.\u00a0Another example where understanding how\u00a0SQL Server performs common operations can make it much easier to diagnose performance\u00a0problems.<\/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. One […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,58,108],"tags":[],"class_list":["post-4793","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-log-shipping","category-sql101"],"yoast_head":"\nSQLskills SQL101: Log shipping performance problems - 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-log-shipping-performance-problems\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Log shipping performance problems - 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. One […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-15T18:56:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-22T22:13:34+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=\"5 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-log-shipping-performance-problems\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/\",\"name\":\"SQLskills SQL101: Log shipping performance problems - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-08-15T18:56:07+00:00\",\"dateModified\":\"2020-03-22T22:13:34+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Log shipping performance problems\"}]},{\"@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: Log shipping performance problems - 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-log-shipping-performance-problems\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Log shipping performance problems - 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. One […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/","og_site_name":"Paul S. Randal","article_published_time":"2017-08-15T18:56:07+00:00","article_modified_time":"2020-03-22T22:13:34+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/","name":"SQLskills SQL101: Log shipping performance problems - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-08-15T18:56:07+00:00","dateModified":"2020-03-22T22:13:34+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-log-shipping-performance-problems\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Log shipping performance problems"}]},{"@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\/4793","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=4793"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4793\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}