SQL Server Database Mirroring Performance Monitoring

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

If you have implemented Database Mirroring, ensure you are tracking these counters.
Review these additional Database Mirroring tips.
Read more about sys.dm_os_performance_counters

Thomas LaRock

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.

LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.

LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.

Leave a ReplyCancel Reply