SQL Server 2008: New Performance Counters for Database Mirroring

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 of bytes sent per second.
Log Bytes Received/sec Number of bytes of log received per second.
Log Bytes Sent/sec Number of bytes of log sent per second.
Log Send Queue Total number of bytes of log that have not yet been sent 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 Total 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.
Sends/sec Number of mirroring messages sent per second.
Transaction Delay Delay in waiting for unterminated commit acknowledgement.

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.

Name Description
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 principal server. On the mirror 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.

Here’s a little more explanation and what you can use these new performance counters to troubleshoot:

  • Log Bytes Redone from Cache/sec
    • 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.
    • You could think of this as a cache hit ratio measure for the redo queue.
    • 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.
  • Log Bytes Sent from Cache/sec
    • 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.
    • You could think of this as a cache hit ratio for the send queue.
    • 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.
  • Log Compressed Bytes Rcvd/sec
  • Log Compressed Bytes Sent/sec
    • 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.
  • Log Harden Time (ms)
    • 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).
    • If this number is higher than normal it means the mirror database’s log disk is more heavily loaded and may be  becoming saturated.
  • Log Remaining for Undo KB
  • Log Scanned for Undo KB
    • The Books Online entries for these counters are self-explanatory.
    • These counters give a way to monitor the undo phase after a failover occurs.
  • Log Send Flow Control Time (ms)
    • This measures how long a mirroring connection had to wait before it could us the mirroring flow control buffer.
    • 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.
  • Mirrored Write Transactions/sec
    • 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.
    • If this value is lower than normal (for the same application workload) it means there is a bottleneck somewhere in the system.
  • Send/Receive Ack Time
    • As Books Online mentions, this can be used to measure network latency between the principal and mirror servers.
    • If this value is larger than normal it means that there is a network bottleneck between the principal and mirror servers.

Hopefully Microsoft will publish a whitepaper or some troubleshooting scenarios showing these counters being used.

4 thoughts on “SQL Server 2008: New Performance Counters for Database Mirroring

  1. Send/Receive Ack Time is also available in SQL Server 2005 under the Database Mirroring counter set – it just isn’t documented in BOL but was fixed for Katmai :-)

  2. Paul, if I wanted to troubleshoot the following issue:

    Database mirroring connection error 4 ‘An error occurred while receiving data: ‘121(The semaphore timeout period has expired.)’.’

    Error: 1474, Severity: 16, State: 1.

    Which counters would be prudent to examine?

    I see this a lot on our mirrored servers and none of the obvious stuff seems to be the cause. I’ve searched high and low on the Internet and I’ve not found anything more telling.

    Thank you in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.