Databases Reference
In-Depth Information
Summary
System Monitor is an effective tool for gathering data about overall server and SQL Server
performance. System Monitor can provide you with data to help identify and diagnose resource
bottlenecks. SQL Server is provided with many System Monitor performance counters, SQL Profiler,
dynamic management views, Database Tuning Advisor, and error logs to assist in troubleshooting.
Knowing when to use each of these tools is important as they each perform a different role and provide
access to performance data at different levels.
Once you're accustomed with using System Monitor to collect performance data, you'll be interested to
know what tools are available to assist with analyzing the data in order to help you determine a
performance baseline for capacity planning or future reference, as well as in troubleshooting
scenarios. The longer monitoring continues, and the more servers or SQL instances that are monitored,
the more important tools for managing the data become. SQL Profiler itself provides the powerful capa-
bility of combining system monitor data with profiler logs, which enables direct correlation between
resources usage spikes and SQL execution on the server.
Taking a baseline of system performance during a period of normal activity will be invaluable when
investigating any performance problems. Knowing CPU utilization, typical disk I/O, and memory
consumption during a normal working day can significantly reduce the mean-time-to-resolution of
performance-related issues, as this often takes the guess work out of decisions/recommendations.
Performance data can be logged directly or imported post-capture into a SQL Server database where
TSQL and SQL Server Reporting Services make a powerful combination for managing and manipulating
performance data, allowing fast access to the data required to make decisions and recommendations
based on performance criteria.
Search WWH ::
Custom Search