Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Performance Monitoring


By:   |   Updated: 2011-01-21   |   Comments (2)   |   Related: More > Database Mirroring

Problem

Many people deploy performance monitoring solutions in a "one-size-fits-all" manner. That is, they tend to build a solution that can be easily deployed to multiple servers and capture basic information from each server. The trouble is that not every server is identical, not even within the same shop. For example, not every server may have database mirroring deployed, which means your performance monitoring solution may be missing some critical pieces of information with regards to monitoring database mirroring.

Solution

The solution is simple: start monitoring for database mirroring performance. One of the best ways to do this is to know and understand the SQLSERVER:Database Mirroring object in System Monitor. These counters will help provide more detailed information on the status of your mirrored databases.


The SQLSERVER:Database Mirroring object contains the following counters (from http://msdn.microsoft.com/en-us/library/ms189931.aspx ):

SQLSERVER:Database Mirroring Counters

Bytes Received/sec Number of bytes received per second.
Bytes Sent/sec Number of bytes sent per second.
Log Bytes Received/sec Number of bytes of log received per second.
Log Bytes Redone from Cache/sec

Number of redone log bytes that were obtained from the mirroring log cache, in the last second.

This counter is used on only the mirror server. On the principal server the value is always 0.

Log Bytes Sent from Cache/sec

Number of sent log bytes that were obtained from the mirroring log cache, in the last second.

This counter is used on only the mirror server. On the principal server the value is always 0.

Log Bytes Sent/sec Number of bytes of log sent per second.
Log Compressed Bytes Rcvd/sec Number of compressed bytes of log received, in the last second.
Log Compressed Bytes Sent/sec Number of compressed bytes of log sent, in the last second.
Log Harden Time (ms) Milliseconds that log blocks waited to be hardened to disk, in the last second.
Log Remaining for Undo KB

Total kilobytes of log that remain to be scanned by the new mirror server after failover.

This 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.

Log Scanned for Undo KB

Total kilobytes of log that have been scanned by the new mirror server since failover.

This 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.

Log Send Flow Control Time (ms)

Milliseconds that log stream messages waited for send flow control, in the last second.

Sending 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.

Log Send Queue KB Total number of kilobytes of log that have not yet been sent to the mirror server.
Mirrored Write Transactions/sec

Number 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.

This counter is incremented only when the principal server is actively sending log records to the mirror server.

Pages Sent/sec Number of pages sent per second.
Receives/sec Number of mirroring messages received per second.
Redo Bytes/sec Number of bytes of log rolled forward on the mirror database per second.
Redo Queue KB Total 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.
Send/Receive Ack Time

Milliseconds that messages waited for acknowledgement from the partner, in the last second.

This 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.

Sends/sec Number of mirroring messages sent per second.
Transaction Delay Delay in waiting for unterminated commit acknowledgement.

That's a lot of counters to choose from, how do you know which ones are the most important to track over time? Fortunately there is whitepaper for Database Mirroring Best Practices that helps to identify which counters are considered most useful.

For the principal server here are the following counters:

  • Log Bytes Sent/sec: From above, this is the number of bytes of the transaction log sent to the mirror per second.
  • Log Send Queue KB: Also from above, this is the total number of kilobytes of the log that have not yet been sent to the mirror server.
  • Transaction Delay: Also from above, this is the delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. You can determine the average delay per transaction by dividing this counter by the Transactions/sec counter found in the Databases performance object. [When running asynchronous mirroring this counter will always be 0.]
These counters are found in the Databases performance object and are also useful for the principal:
  • Transactions/sec: The transaction throughput of the database.
  • Log Bytes Flushed/sec: The rate at which log records are written to the disk.

For the mirror server here are the following counters:

  • Redo Bytes/sec: From above, this is the number of bytes of the transaction log applied on the mirror database per second.
  • Redo Queue KB: Also from above, this is the total number of kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.
For both the principal and the mirror, you should make certain you are monitoring the following counter found in the Logical Disk object:
  • Disk Write Bytes/sec: The rate at which the disk is written to. You should monitor this counter for the data as well as the log disks.

If you have a mirroring session enabled then these counters will also appear in the sys.dm_os_performance_counters DMV, making it easier for you to get the details without having to configure System Monitor to do the collection.

 

Next Steps


Last Updated: 2011-01-21


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 07, 2013 - 11:41:18 PM - Elijah Back To Top

What will be the best performance threshold level for each perfmon counters?


Friday, January 21, 2011 - 1:04:19 PM - Abi Chapagai Back To Top

As always, good content Tom.


Learn more about SQL Server tools