{"id":1176,"date":"2007-10-01T20:31:38","date_gmt":"2007-10-01T20:31:38","guid":{"rendered":"\/blogs\/paul\/post\/SQL-Server-2008-New-Performance-Counters-for-Database-Mirroring.aspx"},"modified":"2013-04-15T15:05:03","modified_gmt":"2013-04-15T22:05:03","slug":"sql-server-2008-new-performance-counters-for-database-mirroring","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/","title":{"rendered":"SQL Server 2008: New Performance Counters for Database Mirroring"},"content":{"rendered":"

Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities.<\/p>\n

SQL Server 2005 provided the following 11 counters (from Books Online):<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Name <\/span><\/th>\nDescription <\/span><\/th>\n<\/tr>\n
\nBytes Received\/Sec<\/b> <\/span><\/td>\n\nNumber of bytes received per second.<\/span><\/td>\n<\/tr>\n
\nBytes Sent\/sec<\/b> <\/span><\/td>\n\nNumber of bytes sent per second.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Received\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log received per second.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Sent\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log sent per second.<\/span><\/td>\n<\/tr>\n
\nLog Send Queue<\/b> <\/span><\/td>\n\nTotal number of bytes of log that have not yet been sent to the mirror server.<\/span><\/td>\n<\/tr>\n
\nPages Sent\/sec<\/b> <\/span><\/td>\n\nNumber of pages sent per second.<\/span><\/td>\n<\/tr>\n
\nReceives\/sec<\/b> <\/span><\/td>\n\nNumber of mirroring messages received per second.<\/span><\/td>\n<\/tr>\n
\nRedo Bytes\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log rolled forward on the mirror database per second.<\/span><\/td>\n<\/tr>\n
\nRedo Queue<\/b> <\/span><\/td>\n\nTotal number of bytes of hardened log that currently remain to be applied to the mirror database to roll it forward. This is sent to the Principal from the Mirror.<\/span><\/td>\n<\/tr>\n
\nSends\/sec<\/b> <\/span><\/td>\n\nNumber of mirroring messages sent per second.<\/span><\/td>\n<\/tr>\n
\nTransaction Delay<\/b> <\/span><\/td>\n\nDelay in waiting for unterminated commit acknowledgement.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

SQL Server 2008 now provides 21 counters, with the new ones highlighted in red. This info is taken from the 2008 July CTP 08Books Online that is downloadable here<\/a>.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Name <\/span><\/th>\nDescription <\/span><\/th>\n<\/tr>\n
\nBytes Received\/sec<\/b> <\/span><\/td>\n\nNumber of bytes received per second.<\/span><\/td>\n<\/tr>\n
\nBytes Sent\/sec<\/b> <\/span><\/td>\n\nNumber of bytes sent per second.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Received\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log received per second.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Redone from Cache\/sec<\/span><\/b> <\/span><\/td>\n\nNumber of redone log bytes that were obtained from the mirroring log cache, in the last second.<\/span>
\nThis counter is used on only the mirror server. On the principal server the value is always 0.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Sent from Cache\/sec<\/span><\/b> <\/span><\/td>\n\nNumber of sent log bytes that were obtained from the mirroring log cache, in the last second.<\/span>
\nThis counter is used on only the principal server. On the mirror server the value is always 0.<\/span><\/td>\n<\/tr>\n
\nLog Bytes Sent\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log sent per second.<\/span><\/td>\n<\/tr>\n
\nLog Compressed Bytes Rcvd\/sec<\/span><\/b> <\/span><\/td>\n\nNumber of compressed bytes of log received, in the last second.<\/span><\/td>\n<\/tr>\n
\nLog Compressed Bytes Sent\/sec<\/span><\/b> <\/span><\/td>\n\nNumber of compressed bytes of log sent, in the last second.<\/span><\/td>\n<\/tr>\n
\nLog Harden Time (ms)<\/span><\/b> <\/span><\/td>\n\nMilliseconds that log blocks waited to be hardened to disk, in the last second.<\/span><\/td>\n<\/tr>\n
\nLog Remaining for Undo KB<\/span><\/b> <\/span><\/td>\n\nTotal kilobytes of log that remain to be scanned by the new mirror server after failover.<\/span>
\nThis counter is used on only the mirror server during the undo phase. After the undo phase completes, the counter is reset to 0. On the principal server the value is always 0.<\/span><\/td>\n<\/tr>\n
\nLog Scanned for Undo KB<\/span><\/b> <\/span><\/td>\n\nTotal kilobytes of log that have been scanned by the new mirror server since failover.<\/span>
\nThis counter is used on only the mirror server during the undo phase. After the undo phase completes, the counter is reset to 0. On the principal server the value is always 0. <\/span><\/td>\n<\/tr>\n
\nLog Send Flow Control Time (ms)<\/span><\/b> <\/span><\/td>\n\nMilliseconds that log stream messages waited for send flow control, in the last second.<\/span>
\nSending log data and metadata to the mirroring partner is the most data-intensive operation in database mirroring and might monopolize the database mirroring and Service Broker send buffers. Use this counter to monitor the use of this buffer by the database mirroring session.<\/span><\/td>\n<\/tr>\n
\nLog Send Queue KB<\/b> <\/span><\/td>\n\nTotal number of kilobytes of log that have not yet been sent to the mirror server.<\/span><\/td>\n<\/tr>\n
\nMirrored Write Transactions\/sec<\/span><\/b> <\/span><\/td>\n\nNumber of transactions that wrote to the mirrored database and waited for the log to be sent to the mirror in order to commit, in the last second.<\/span>
\nThis counter is incremented only when the principal server is actively sending log records to the mirror server.<\/span><\/td>\n<\/tr>\n
\nPages Sent\/sec<\/b> <\/span><\/td>\n\nNumber of pages sent per second.<\/span><\/td>\n<\/tr>\n
\nReceives\/sec<\/b> <\/span><\/td>\n\nNumber of mirroring messages received per second.<\/span><\/td>\n<\/tr>\n
\nRedo Bytes\/sec<\/b> <\/span><\/td>\n\nNumber of bytes of log rolled forward on the mirror database per second.<\/span><\/td>\n<\/tr>\n
\nRedo Queue KB<\/b> <\/span><\/td>\n\nTotal number of kilobytes of hardened log that currently remain to be applied to the mirror database to roll it forward. This is sent to the Principal from the Mirror.<\/span><\/td>\n<\/tr>\n
\nSend\/Receive Ack Time<\/span><\/b> <\/span><\/td>\n\nMilliseconds that messages waited for acknowledgement from the partner, in the last second.<\/span>
\nThis counter is helpful in troubleshooting a problem that might be caused by a network bottleneck, such as unexplained failovers, a large send queue, or high transaction latency. In such cases, you can analyze the value of this counter to determine whether the network is causing the problem.<\/span><\/td>\n<\/tr>\n
\nSends\/sec<\/b> <\/span><\/td>\n\nNumber of mirroring messages sent per second.<\/span><\/td>\n<\/tr>\n
\nTransaction Delay<\/b> <\/span><\/td>\n\nDelay in waiting for unterminated commit acknowledgement.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Here’s a little more explanation and what you can use these new performance counters to troubleshoot:<\/p>\n

    \n
  • Log Bytes Redone from Cache\/sec<\/strong>\n
      \n
    • This measures how much of the transaction log in the redo queue is being read by the log redo task from the mirror’s in-memory transaction log cache. Reading from the cache is a lot faster than having to read from the mirror’s actual transaction log. Even though the log gets hardened on the mirror database’s log disk, it does not need to be removed from the cache until the cache fills up with new transaction log from the principal.<\/li>\n
    • You could think of this as a cache hit ratio measure for the redo queue.<\/li>\n
    • If this number is lower than usual, it means that transaction log is arriving from the principal faster than the log redo task can roll forward the transaction log in the redo queue.<\/li>\n<\/ul>\n<\/li>\n
    • Log Bytes Sent from Cache\/sec<\/strong>\n
        \n
      • This is similar to the counter above. It measures how much of the transaction log being sent from the principal to the mirror is being read from the principal’s in-memory transaction log cache. Sending from the cache is a lot faster than having to go to the transaction log itself and read from disk.<\/li>\n
      • You could think of this as a cache hit ratio for the send queue.<\/li>\n
      • If this number is lower than usual it means that the transaction log is being generated on the principal faster than it can be sent to the mirror.<\/li>\n<\/ul>\n<\/li>\n
      • Log Compressed Bytes Rcvd\/sec<\/strong><\/li>\n
      • Log Compressed Bytes Sent\/sec<\/strong>\n
          \n
        • These are simple counters and can be used with the Log Bytes Sent\/sec and Rcvd\/sec counters to determine the compression ratio. Log stream compression is another enhancement in SQL Server 2008 that I’ll cover in a future post.<\/li>\n<\/ul>\n<\/li>\n
        • Log Harden Time (ms)<\/strong>\n
            \n
          • This measures the delay between the mirror server receiving a chunk of transaction log and it being hardened on the mirror database’s log disk (i.e. the delay before the chunk of transaction log becomes part of the redo queue on the mirror server).<\/li>\n
          • If this number is higher than normal it means the mirror database’s log disk is more heavily loaded and may be\u00a0 becoming saturated.<\/li>\n<\/ul>\n<\/li>\n
          • Log Remaining for Undo KB<\/strong><\/li>\n
          • Log Scanned for Undo KB<\/strong>\n
              \n
            • The Books Online entries for these counters are self-explanatory.<\/li>\n
            • These counters give a way to monitor the undo phase after a failover occurs.<\/li>\n<\/ul>\n<\/li>\n
            • Log Send Flow Control Time (ms)<\/strong>\n
                \n
              • This measures how long a mirroring connection had to wait before it could us the mirroring flow control buffer.<\/li>\n
              • If this number is higher than normal it means there is contention for the buffer, most likely because there are too many Database Mirroring partnerships running from a single instance.<\/li>\n<\/ul>\n<\/li>\n
              • Mirrored Write Transactions\/sec<\/strong>\n
                  \n
                • As Books Online mentions, this counts the number of transactions in the principal database that had to wait for a commit record to harden in the mirror database’s transaction log.<\/li>\n
                • If this value is lower than normal (for the same application workload) it means there is a bottleneck somewhere<\/em> in the system.<\/li>\n<\/ul>\n<\/li>\n
                • Send\/Receive Ack Time<\/strong>\n
                    \n
                  • As Books Online mentions, this can be used to measure network latency between the principal and mirror servers.<\/li>\n
                  • If this value is larger than normal it means that there is a network bottleneck between the principal and mirror servers.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

                    Hopefully Microsoft will publish a whitepaper or some troubleshooting scenarios showing these counters being used.<\/p>\n","protected":false},"excerpt":{"rendered":"

                    Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received\/Sec Number of bytes received per second. Bytes Sent\/sec Number […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,66,86],"tags":[],"class_list":["post-1176","post","type-post","status-publish","format-standard","hentry","category-database-mirroring","category-performance-tuning","category-sql-server-2008"],"yoast_head":"\nSQL Server 2008: New Performance Counters for Database Mirroring - 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\/sql-server-2008-new-performance-counters-for-database-mirroring\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2008: New Performance Counters for Database Mirroring - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received\/Sec Number of bytes received per second. Bytes Sent\/sec Number […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-01T20:31:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-15T22:05:03+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=\"7 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\/sql-server-2008-new-performance-counters-for-database-mirroring\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/\",\"name\":\"SQL Server 2008: New Performance Counters for Database Mirroring - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-10-01T20:31:38+00:00\",\"dateModified\":\"2013-04-15T22:05:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2008: New Performance Counters for Database Mirroring\"}]},{\"@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":"SQL Server 2008: New Performance Counters for Database Mirroring - 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\/sql-server-2008-new-performance-counters-for-database-mirroring\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2008: New Performance Counters for Database Mirroring - Paul S. Randal","og_description":"Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received\/Sec Number of bytes received per second. Bytes Sent\/sec Number […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-01T20:31:38+00:00","article_modified_time":"2013-04-15T22:05:03+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/","name":"SQL Server 2008: New Performance Counters for Database Mirroring - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-10-01T20:31:38+00:00","dateModified":"2013-04-15T22:05:03+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sql-server-2008-new-performance-counters-for-database-mirroring\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2008: New Performance Counters for Database Mirroring"}]},{"@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\/1176","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=1176"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1176\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}