Symptoms:
Slow SQL responses when a mirrored database is experiencing connection speed problems.
Impact: Medium
Expected behavior :
There is no standard metric for this alert. Many normal activities may cause the connection between the two databases to run slower than usual (for example, backup, virus scan) but unexpected delays need to be investigated, since they may be caused by problems with a key resource, such as storage or network bandwidth.
Description:
This alert works alongside the alert for Mirror not connected which reports when the connection between the two databases is completely down, and AlwaysOn loss of quorum which reports when the database can no longer be regarded as healthy because of the loss of quorum.
Possible causes
High Network Latency – Priority: Critical
Higher levels of latency are a by-product of the additional network traffic involved in mirroring. After enabling mirroring, the default thresholds for latency and usage should be adjusted accordingly.
Recommended action :
Follow the same actions recommended for fixing network load problems.
Long SQL tasks – Priority: Medium
Long and log-intensive transactions can impact performance and failover time. Some common examples of long and log-intensive transactions are creating or rebuilding an index on a large table and bulk loading a large amount of data.
Recommended action :
Index rebuilds – Priority: Medium
Rebuilding an index takes longer with safety FULL than it does with safety OFF. Synchronous mirroring introduces more overhead when rebuilding a clustered index than when rebuilding a nonclustered index.
Recommended action :
Schedule index rebuilds for low user-load periods.
Background
In a clustering environment, database mirroring in SQL Server allows you to keep a copy, or mirror, of a SQL Server database on a standby server. Mirroring ensures that two separate copies of the data exist at all times, providing high availability and complete data redundancy. AimBetter monitor is reporting here when delays are introduced and queries are running for longer than the designated time. A quick rule of thumb is that every transaction will take twice as long to complete when participating in a mirror partnership.
Ideally, you should have a dedicated network between the servers that are involved in database mirroring. If the servers share a common network with other servers, the effective bandwidth that is available for mirroring communication will impact performance. It is highly recommended that you configure database mirroring in a high-bandwidth network. The network bandwidth is dictated by the log generation rate of your application. Lower bandwidth networks can adversely impact the performance of database mirroring.
