Professional SQL Server® 2005 Performance Tuning

2.5. What's the Impact of Running System Monitor?

Typically managers want problems fixed fast—with as little risk to further degrading service as possible. Often they'll seek reassurances that any monitoring or troubleshooting steps you take won't impact system performance or affect perceived user experience any further. Unfortunately, without having been in this exact same situation previously and using monitoring tools—the outcome/impact can be difficult to predict. Every type of monitoring, whether System Monitor, PSSDiag, SQL Server Profiler, or third-party tools, creates an overhead on the server. 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.

2.5.1. Managing Monitoring Impact

You should be prudent in the number of counters logged and the sampling frequency. Start ...