Databases Reference
In-Depth Information
the latency between the principal and the mirror to determine how far behind the mirror is in applying
changes made at the principal. The following counters will give you some idea as to the performance of
database mirroring:
SQLServer:Database Mirroring — Log Bytes Sent/sec:
Shows the rate at which data is sent from
the transaction log on the principal to the transaction log on the mirror.
❑
❑
SQLServer:Database Mirroring — Log Send Queue KB:
Shows the number of log bytes held
in the transaction log buffer still to be sent to the mirror. Similarly, on the mirror server you
can monitor the following counter to determine the rate at which the received transaction log
is received:
❑
SQLServer:Database Mirroring — Log Bytes Received/sec:
Following this, monitoring the size
of the redo queue and redo bytes/ second will help you understand the rate at which
the mirror is processing log data in order that it stays up to date with the principal.
Monitoring Wait Statistics
There is a new performance object for SQL Server 2005 that monitors wait types. This counter
contains wait information for specific global resources that may experience contention and therefore
cause performance problems.
Each counter object contains the same four instances that describe wait times (average and cumulative),
waits in progress, or waits started per second.
SQLServer:Wait Statistics — Lock Waits:
This counter will give you an idea of whether
processes are waiting too long to acquire locks. Measuring the ability of a process to acquire a
lock is useful in terms of determining whether locking is an issue.
❑
❑
SQLServer:Wait Statistics —Memory Grant Queue Waits:
Shows the number of processes or
length of time processes spend queuing waiting for memory grants to become available.
❑
SQLServer:Wait Statistics — Page I/O Latches:
SQL Server requires latches to ensure data syn-
chronization. The availability of latches directly affects SQL Server performance and as such,
monitoring latches in the event of poor performance can be useful in identifying the root cause.
There are another nine performance counters within the Wait Statistics object, many of which can be
useful in specific troubleshooting circumstances. Take a look through the description in SQL Server
Books Online for a description of each of these, as you'll find it beneficial to be familiar with them.
Typical Performance Problems
When investigating any kind of performance problem, it's important to first identify the area of concern,
and in many causes your first step should be to rule SQL Server in or out of suspicion.
The following section contains an overview of some typical SQL Server poor performance scenarios,
using some of the counters discussed to gain visibility of the problem and a better understanding of
the causes.
Search WWH ::
Custom Search