Databases Reference
In-Depth Information
Consider a situation in which you have a busy system with a high workload but very short
transactions — sampling every 60 seconds could miss many of these very short transactions.
The sample interval can affect the shape of the data, so always be aware of it and the overall
monitoring window when reviewing performance logs, especially when looking at min, max, and
average values. Take into account system activity and usage patterns to ensure that the log is
representative of typical workload.
Number of Counters
A consideration with similar impact to sample interval, more counters results in a higher cost to
sample and store those counter values. Most instance counters have a _TOTAL counter, which
is a total of the individual counter instances combined. In some cases, such as for disk counters,
this total is of limited use, as usually the details about each disk (instance) counter are required to
identify disk performance problems. The total can hide problems, because an average might look
healthy; but a very busy disk could be masked by several other disks with little activity.
Disk Performance
When capturing performance data using Data Collector Sets, consider where the log i les will be
stored. The objective is to minimize the impact to SQL Server; log performance data to a i le on disk
(not a database); and, where available, use a disk that will not contend with any databases — i.e.,
avoid any disks where data or log i les are stored.
PerfMon logs grow in a linear and predictable pattern (unlike SQL Proi ler trace i les, which
are workload dependent); for example, sampling 100 counters every 15 seconds for 5 minutes
might create a 2MB PerfMon log i le, so it would be reasonable to estimate that logging 100
counters for six hours would generate a 144MB log i le. Generally, I try to avoid capturing data to
a system drive, as the implications of i lling that drive are much greater than when logging to a
nonsystem drive.
Servers Suf ering Very Poor Performance
When capturing PerfMon logs on servers with acute performance problems, run PerfMon as cau-
tiously as possible to reduce the impact while still harvesting performance data. Here are some
guidelines:
➤
Run PerfMon remotely.
➤
Reduce the sampling interval.
➤
Include as few counters as possible.
➤
Log to disk.
Common PerfMon Problems
You may sometimes encounter problems with PerfMon itself — specii cally, counters could
be missing, they might not be displayed correctly, or there could be problems connecting to
servers remotely. This section contains a brief summary of some common issues and how to
resolve them.
Search WWH ::
Custom Search