Many people find themselves without a clear understanding of what the counters mean and how to use them even after reading the Books Online article.
Bytes Received/sec
BOL description: Number of bytes received per second.
Where to use: Mirror and principal
How to use: Measures the amount of bytes received on the partner. Covers all communications between the partners. Not very useful for on-going monitoring. Bytes Received/sec is cumulative and not the actual bytes/sec. Bytes Received/sec should be failrly close to the Bytes Sent/seccounter on the other partner. To use this counter, measure the value, wait a set number of seconds and measure again then subtract to get the change over the period per time, and lastly divide by the number of seconds waited to get the actual bytes per second.
Here’s some sample code:
Declare @BytesReceivedSec bigint,
@BytesReceivedSecDelta bigint;
Select @BytesReceivedSec = cntr_value
From sys.dm_os_performance_counters
Where object_name Like '%:Database Mirroring%'
And counter_name = 'Bytes Received/sec'
And instance_name = '<Database Name>';
Waitfor Delay '0:00:10';
Select @BytesReceivedSecDelta = cntr_value
From sys.dm_os_performance_counters
Where object_name Like '%:Database Mirroring%'
And counter_name = 'Bytes Received/sec'
And instance_name = '<Database Name>';
Select Convert(decimal(11,2), (@BytesReceivedSecDelta - @BytesReceivedSec)/10.0);
Bytes Sent/sec
BOL description: Number of sent per second.
Where to use: Mirror and principal
How to use: This is the opposite of the Bytes Received/sec counter. It is cumulative and you would need to calculate the change delta over time using the same method as Bytes Received/sec.
Log Bytes Received/sec
BOL description: Number of bytes of log received per second.
Where to use: Mirror
How to use: This is the amount of log received on the mirror server. This counter is cumulative. To see the actual log bytes received per second, calculate the change delta and divide by the sampling seconds. This counter can be used in conjuction with Log Bytes Redone from Cache/sec to determine if the log is being sent faster than it can be applied.
To uderstand the counters regarding log sent/received and log redone, it helps to understand how log records are sent to a mirror. Even when safety is set to full, a transaction only has to be hardened to the log of the mirror to be considered committed and hardened on the mirror. There is a separate process that is applying the log records to the data pages. This process is called Redo and occurs anytime a database is recovered, restored or started up; a database snapshot is created; or a mirroring failover occurs. This process is often referred to “rolling transactions forward.”
Log Bytes Redone from Cache/sec
BOL description: 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.
Where to use: Mirror
How to use: This counter is the amount of log that has been redone from the log cache per second. In a perfectly in sync system, this counter and Log Bytes Received/sec should be identical. The value is cumulative and you would need to calculate the change delta over a set length of time. See Log Bytes Received/sec above for more info.
Log Bytes Sent from Cache/sec
BOL description: 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.
Where to use: Principal
How to use: Log Bytes Sent from Cache per second is the opposite of Log Bytes Redone from Cache/sec. It is cumulative and you shoudl calculate the change delta to use this counter. This counter should be fairly close or identical to Log Bytes Sent/sec.
Log Bytes Sent/sec
BOL description: Number of bytes of log sent per second.
Where to use: Principal
How to use: This is the total log bytes sent per second from the principal to the mirror. If the log bytes are not being sent from cache, it could be an indicator that mirroring is not able to send the log quick enough and the log bytes are being cleared from cache before tehy can be sent.
Log Compressed Bytes Rcvd/sec
BOL description: Number of compressed bytes of log received, in the last second.
Where to use: Mirror
How to use: This counter can indicate how much benefit you are getting from using compression of the log stream. The value in this counter represents the same log records as the Log Bytes Received/sec over the same sampling period. This counter is cumulative and you need to calculate the change delta over the sampling time period.
Log Compressed Bytes Sent/sec
BOL description: Number of compressed bytes of log sent, in the last second.
Where to use: Principal
How to use: This counter is the opposite of Log Compressed Bytes Rcvd/sec. This is the amount of log sent in the compressed log stream. This counter is representative as the same log records as indicated for Log Sent/sec over the same time period. This counter is cumulative and you need to calculate the change delta over the sampling time period.
Log Harden Time (ms)
BOL description: Milliseconds that log blocks waited to be hardened to disk, in the last second.
Where to use: Mirror
How to use: This counter is the total number of milliseconds that log recrods waited to be hardened to disk. This represents the time from when the log block was sent to the mirror until it was written to the log of the mirror. This number should be very low. If this number is high, it could be an indicator of problems with the log file. This counter should be close to zero and the log file should definitely be investigated if this number is high.
This counter is cumulative, and you should calculate the change delta over a given period of time.
Log Remaining for Undo KB
BOL description: 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.
Where to use: Mirror
How to use: Undo is the polar opposite of Redo and is often referred to as “rolling back” transactions. After a failover occurs, and the previous principal comes online as the mirror (in the case of an automatic failover due to principal going offline), the Undo process must run on the mirror server. Any transactions that were not committed on either the principal or the mirror will be rolled back.
This counter represents the current total value. Once Undo completes, the counter will return to 0. Use this counter to estimate how long it will take for the former principal to come online in the mirror role. Calculate how quickly it is changing over time to estimate how long until it completes.
Log Scanned for Undo KB
BOL description: 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.
Where to use: Mirror
How to use: This counter is the amount of log that has been scanned for the Undo phase. This counter is the current total amount while the Undo phase is occuring. This counter can be added to Log Remaining for Undo KB to calculate the total amount of log that participate in the Undo phase. This counter is the opposite of Log Remaining for Undo KB and should be trending down at the same rate as Log Remaining for Undo KB is trending up. Once the Undo phase is complete, this counter returns 0.
Log Send Flow Control Time (ms)
BOL description: 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.
Where to use: Principal
How to use: Database mirroring is built on top of Service Broker, hence the usage of queues. Log Send Flow Control is the service broker mechanism that controls the flow of data going through the mirroring session. This is the amount of time in milliseconds that log records waited on the principal to be sent to the mirror. The numbers here should be very low over a given time range. High numbers could be indicative of long delays committing transactions in either operating mode.
This counter is cumulative. Calculate the change delta over a given period of time.
Log Send Queue KB
BOL description: Total number of kilobytes of log that have not yet been sent to the mirror server.
Where to use: Principal
How to use: This counter represents the amount of log not yet sent to the mirror. Both operating modes for database mirroring require that the log records be sent to the mirror before the transaction can be committed on the principal side. This counter should always report 0 as long as the session is in a synchronized state. If the session loses synchronization, log records can be queued in the send queue. For a safety full session, this can only happen if the principal has become disconnected from the mirror. Even in high safety, this counter is indicative of the mirror falling behind.
This counter is the current total and you do not need to calculate anything for it to be useful. If running in high safety mode, this counter could be used to raise alerts that the mirror became disconnected and the mirroring session is no longer enforcing safety.
If the Log Queue has log records queued, you can estiamte the amount of time it will take to apply all of the log records by dividing the Log Send Queue KB by the current rate of Log Bytes Sent/sec using this query. You can download this query from here: LogSendQueueEstimate.sql (1 KB)
Mirrored Write Transactions/sec
BOL description: 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.
Where to use: Principal
How to use: This counter indicates the number of transactions that participated in a 2 phase commit and waited for the transaction to be committed on the mirror before being committed on the principal. This counter is cumulative and is only incremented when safety is being enforced. If safety is off, this counter will not be incremented. If safety is full and the session is synchronized, the counter should equal the total number of transactions that resulted in changes being sent across to the mirror.
This counter is cumulative and you need to calculate the change delta to see how the transaction count is changing over a period of time.
Pages Sent/sec
BOL description: Number of pages sent per second.
Where to use: Principal
How to use: This counter is incremented by 1 every time the mirroring session performs a synchronization check. This is a rare event. It is performed initially when mirroring is configured or if mirroring is failed over. It is also incremented if the mirroring session is changed from safety off to safety full or if a session becomes disconnected and then reconnects.
This counter is cumulative and represents the total number of synchronization checks. The rate of change in this counter should be very low or almost zero. If you are seeing frequent changes in this number, then your partners are likely becoming disconnected frequently.
Receives/sec
BOL description: Number of mirroring messages received per second from the other partner.
Where to use: Principal and Mirror
How to use: This counter is cumulative and you need to calculate the rate of change of a given time period. This counter can be compared to Sends/sec to determine if there are network problems or communication problems between the partners.
Redo Bytes/sec
BOL description: Number of bytes of log rolled forward on the mirror database per second.
Where to use: Mirror
How to use: This counter is cumulative. You need to calculate a change delta over a period of time to get the rate per second. This counter can be used in conjunction with the Redo Queue KB counter to calculate how long it will take to Redo the log records in the Redo Queue.
Redo Queue KB
BOL description: 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.
Where to use: Mirror
How to use: This counter is the current total amount in KBof log records in the Redo queue waiting to be applied to the database. If this amount is large or growing, it could be an indicator that the mirror database is not able to apply transactions as quickly as they are coming in. This could be an indicator of a performance bottleneck on the mirror side.
In the event of a failover, all records in the Redo queue must be applied before the database comes online. This value should be low or zero or failover time will be affected. Additionally, if you are creating database snapshots of the mirror, the snapshot must roll these transactions forward in its context. The snapshot can take a long time to create if the Redo queue is very large. If you are creating a database snapshot regularly as part of an automated process, you may want to check this counter to estimate the time to create the snapshot before creating it.
The query for estimating the time to redo the log records currently in the queue is almost identical to the query for estimated the amount of time to send log records in the Send queue. You can download this query from here: RedoQueueEstimate.sql (1 KB)
Send/Receive Ack Time
BOL description: 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.
Where to use: Principal, Mirror, and Witness (_Total instance)
How to use: This counter is cumulative and you should calculate the change over a given time period. This is the only counter that can be used on the witness and indicates the amount of time the witness waiting for acknowledgement of its pings of the other partners. High rates in this counter can indicate network problems or bottleneck.
Sends/sec
BOL description: Number of mirroring messages sent per second.
Where to use: Principal and Mirror
How to use: This counter is cumulative and you need to calculate the rate of change of a given time period. This counter can be compared to Receives/sec to determine if there are network problems or communication problems between the partners.
Transaction Delay
BOL description: Delay in waiting for unterminated commit acknowledgement.
Where to use: Principal and Mirror
How to use: This is how long a transaction had to wait for the commit to be acknowledged. This is a key indicator of transaction lag as seen by the end user. This counter is cumulative and you should calculate the rate of change over a set period of time. The rate of transaction delay should be zero or close to zero at all times. Spikes or high values in this counter cna be a clear indicator of a bottleneck that is affecting performance and that end users are seeing a delay in their transactions.
Which Counters to Baseline/Monitor
The counters listed below (and described above) are the key counters I recommend monitoring on an on-going basis if you are using database mirroring.
- Log Bytes Redone from Cache/sec
- Log Bytes Sent from Cache/sec
- Log Send Flow Control Time (ms)
- Log Send Queue KB
- Mirrored Write Transactions/sec
- Pages Sent/sec
- Redo Bytes/sec
- Redo Queue KB
- Send/Receive Ack Time
- Transaction Delay
Oscar Zamora
Very comprehensive, thanks for sharing.
Is compression enabled by default?
SQLSoldier
Compression of the log stream is automatic and only available in SQL Server 2008 and newer. It is used in all versions that support database mirroring (Standard and up).
Something for the Weekend – SQL Server Links 22/04/11 | John Sansom - SQL Server DBA in the UK
[…] SQLU HA/DR Week – Database Mirroring Performance Counters – More SQLU lessons, this time with Robert L. Davis(Blog|Twitter) giving his lesson on performance counters for Mirroring. […]
Less Than Dot - Blog - Awesome
[…] SQLU HA/DR Week – Database Mirroring Performance Counters […]