By: Thomas LaRock | Updated: 2011-01-21 | Comments (2) | Related: > 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.]
- 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.
- 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
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-01-21
