Databases Reference
In-Depth Information
outcome/impact can be difficult to predict. Every type of monitoring, whether SystemMonitor, PSSDiag,
SQL Server Profiler, or third-party tools, creates anoverheadontheserver.Essentially you're asking
the server to record all activity or a subset of activity to a performance tool. This activity record will reside
either on disk or in memory, and the performance of this media will itself affect the impact of monitoring.
Additionally, the target disk or memory could reside on a different server from the one you are
monitoring, which will introduce its own characteristics that you should also be aware of.
Creating a System Monitor log file on the same disk volume that hosts a database file or the operating
system paging file will exaggerate any existing memory problems. This is because the monitoring process
competes for server resources with genuine user activity on a server, increasing contention, disk queue
lengths, and wait times.
Managing Monitoring Impact
You should be prudent in the number of counters logged and the sampling frequency. Start lightweight
and broad to provide a holistic system overview, and as you zoom in to the problem area, remember to
remove counters that are no longer relevant as you add more detailed and specific counters based on
your findings. Changing the polling interval from the default 15 seconds to 30 seconds reduces the load
of monitoring by 50 percent.
Performance tuning is often an iterative process. Based on results of monitoring, you can further
refine what and how you monitor, and perhaps change between tools that are more appropriate
for the job.
Capturing at the Right Time, for the Right Duration
Customers who lack troubleshooting experience or lack confidence in the impact of monitoring tools or
the durability and robustness of their applications tend to be cautious around the time and duration of
monitoring. Frequently engineers will select periods of low activity and limit monitoring duration to 15
minutes or less, but this can often prove inefficient in terms of getting the problem resolved. A concise
problem description along with the SystemMonitor log and SQL Profiler trace in many cases will provide
all the data you need to identify and resolve a performance problem. However, you're entirely reliant on
the problem showing itself during your data capture window and as such, the longer you monitor, the
more likely you are to capture an occurrence of the problem.
When handling performance problems, troubleshooting servers with mediocre performance (such as
''somewhat slower than usual'' problem statements) can be much harder than troubleshooting servers
with acute performance issues. Similarly, during your data capture (monitoring) window, you should
look to gain an insight into the server at its worst. This will provide you with the most meaningful data
and give you the very best chance of identifying the cause as early as possible!
How Much Data Will System
Monitor Generate?
Ideally, your performance log contains just enough information to allow you to identify the
problem — no more. Getting to the point that you only record meaningful data (data that will be used in
diagnosing an issue) is not trivial. To do this it will be necessary to undertake a number of
Search WWH ::
Custom Search