Database Reference
In-Depth Information
Limit the Number of Counters
Monitoring large numbers of performance counters with small sampling intervals could incur some amount
of overhead on the system. The bulk of this overhead comes from the number of performance objects you are
monitoring, so selecting them wisely is important. The number of counters for the selected performance objects does
not add much overhead because it gives only an attribute of the object itself. Therefore, it is important to know what
objects you want to monitor and why.
Prefer Counter Logs
Use counter logs instead of viewing a Performance Monitor graph interactively because Performance Monitor
graphing is more costly in terms of overhead. Monitoring current activities should be limited to short-term viewing
of data, troubleshooting, and diagnosis. Performance data reported via a counter log is
sampled
, meaning that data
is collected periodically rather than traced, whereas the Performance Monitor graph is updated in real time as events
occur. Using counter logs will reduce that overhead.
View Performance Monitor Graphs Remotely
Since viewing the live performance data using Performance Monitor graphs creates a fair amount of overhead on
the system, run the tool remotely on a different machine and connect to the SQL Server system through the tool.
To remotely connect to the SQL Server machine, run the Performance Monitor tool on a machine connected to the
network to which the SQL Server machine is also connected.
As shown in Figure
5-1
, type the computer name (or IP address) of the SQL Server machine in the Select Counters
from Computer box. Be aware that if you connect to the production server through a Windows Server 2012 R2
terminal service session, the major part of the tool will still run on the server.
However, I still encourage you to avoid using the Monitor Graphs for viewing live data. You can use the graphs to
look at the files collected through counter logs and should have a bias toward using those logs.
Save Counter Log Locally
Collecting the performance data for the counter log does not incur the overhead of displaying any graph. So,
while using counter log mode, it is more efficient to log counter values locally on the SQL Server system instead of
transferring the performance data across the network. Put the counter log file on a local disk other than the ones that
are monitored, meaning your SQL Server data and log files.
Then, after you collect the data, copy that counter log to your local machine to analyze it. That way, you're
working only on a copy, and you're not adding I/O overhead to your storage location.
Increase the Sampling Interval
Because you are mainly interested in the resource utilization pattern during baseline monitoring, you can easily
increase the performance data sampling interval to 60 seconds or more to decrease the log file size and reduce
demand on disk I/Os. You can use a short sampling interval to detect and diagnose timing issues. Even while viewing
Performance Monitor graphs interactively, increase the sampling interval from the default value of one second per
sample. Just remember, changing the sampling size up or down can affect the granularity of the data as well as the
quantity. You have to weigh these choices carefully.
