Professional SQL Server® 2005 Performance Tuning

2.6. 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 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, ...