SQL Server database mirroring key Performance counters

SQL Server database mirroring key Performance counters

There are 5 basic methods of monitoring database mirroring:

1. Configure the database Mirroring Monitor, set the alarm threshold value.

2. Configure WMI EVENT ALERT to configure the action or alarm after the mirror state is modified.

3. Use System Monitor to view key performance counters.

4. The deployment job monitors the abnormal mirroring status and sends alarms.

5. Use Sp_dbmmonitorresults to get the mirrored data.

Of course, the use of Extended events and SQL Server Profiler can also monitor database mirroring state modifications in database events. This tool is mainly used for tracking, rather than conventional means of monitoring.

This blog post focuses on using System Monitor (formerly known as Performance Monitor) to monitor database mirroring performance. The SQL Server:database mirroring object contains performance counters for database mirroring. The following list describes some of the key counters.

Key Primary Server counters:

• Log Bytes send/sec: transfer rate of logs from primary to mirror

• Log send Queue KB: Total number of logs that have not yet been sent to the mirror (in kilobytes)

• Transaction delay: The latency (in milliseconds) spent waiting to be answered from the mirror submission. This counter is useful for determining whether the primary server database mirroring affects performance.

• log Compress Bytes sent/sec: The compressed byte size of the log sent in the previous second. To calculate the log compression rate, you can use log compressed Bytes sent/sec divided by log Bytes sent/sec.

• log Bytes Sent from cache/sec: the number of send log bytes obtained from the mirrored log cache in the previous second. This counter is used only on the principal server. This value is always 0 on the mirror server.

Critical mirror server counters:

• Redo bytes/sec: the rate at which logs roll forward in the mirrored database.

• Redo Queue KB: The total number of logs that have not been rolled forward to the mirror database. (in kilobytes) to evaluate the time of the mirror redo log, you can divide the redo Queue kb by the redo bytes/sec.

• Log Bytes received/sec: The rate of logs received from the primary. In order to evaluate the time that the mirror catches the master, you can divide the log Send Queue KB by the log Bytes received/sec.

• Log Compressed Bytes received/sec: The size of the compressed log received in the last second.

• log Bytes Redone from cache/sec: The number of redo log bytes obtained from the mirrored log cache in the previous second. This counter is used only on the mirror server. This value is always 0 on the principal server.

Use performance counters to monitor database mirroring performance. For example, you can examine the Transaction Delay counter to determine whether database mirroring affects the performance of the principal server, and you can check the Redo queue and Log Send queue counters to determine the synchronization between the mirrored database and the principal database. You can also check the log Bytes sent/sec counter to monitor the amount of logs sent per second.

For a list of integrity performance counters, please refer to:

Https://msdn.microsoft.com/zh-cn/library/ms189931.aspx

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1907134

SQL Server database mirroring key Performance counters