Databases Reference
In-Depth Information
iterations of performance logging using System Monitor. Once the scope of the problem has been more
closely defined (through system-wide troubleshooting early in the process), you can refine the number of
counters you're monitoring by removing less relevant or superfluous counters and drilling down in the
areas of specific interest or relevance.
The two factors that significantly influence the amount of data generated by System Monitor are
as follows:
❑
Number of counters
❑
Sampling interval
Managing the overhead of monitoring system performance and the volume of data generated is a delicate
balance that can be achieved by carefully observing the output and continuously refining counters.
As a best practice it's a good idea with any kind of monitoring (System Monitor, SQL Profiler, and so on)
to avoid logging to a system partition. In the event that you have selected too many counters or objects or
if the server is significantly busier than you expected, the impact of filling a non-system drive may still be
problematic, but it shouldn't be disastrous. If you fill a disk partition that contains a database log file, the
log file will be unable to grow, and without the ability to log operations to the transaction log, SQL Server
will stop servicing the database. This may be disastrous in terms of providing service to your users, as
they will no longer be able to go about their work in the database, but this is a relatively straightforward
position to recover from (simply free some disk space and SQL will resume fairly promptly). However,
if you were in a position where the system partition became full, this could cause some real problems for
Windows that might be much harder to recover from. Therefore, it's generally a good idea to avoid any
risk of getting into this situation by always logging to a target drive that is non-system.
The actual amount of data generated by System Monitor can be predicted fairly reliably — when com-
pared with a load-dependent tool like SQL Server Profiler, where the volume of trace data is highly
variable and as such can be difficult to predict. System monitor data capture is essentially linear. For
example, if you're capturing 30 counters with a 15-second sampling interval (polling interval), this might
produce 1MB of data in the first 15 minutes of data capture. It would be fairly safe to assume that log
growth will continue at 4MB per hour, regardless of whether the server is being thrashed by thousands
of concurrent users or sitting idle. Predictable log growth will allow you to run medium- to long-term
system monitoring with some confidence around the size and nature of the data being produced.
Resource Utilization
Ideally, you'll estimate your workload accurately, buy commodity hardware, and distribute
application roles or functions to ensure an even spread of work and effective use of all resources while not
over-burdening any single server or resource. The practicalities of the testing and measurement required
to estimate workload often mean that you'll be in a situation where you have to go live with a service or
application with less testing than you'd have liked (indeed occasionally with none), and you have to deal
with the problems there and then.
On many occasions, making sensible assumptions and realistically quantifying risks along with a
mitigation plan is the only way to get projects off the ground. In these situations the tendency is
usually to conservatively over-specify hardware in an effort to avoid upgrading at a later date. One
of the characteristics of working with SQL Server that makes it so interesting is that frequently
no amount of hardware can compensate for a bad database design or poorly thought-through
disk subsystem.
Search WWH ::
Custom Search