Chapter 3. Monitoring SQL Server Resources with System Monitor

When you've established SQL Server as the likely cause of your performance problem, the next step is to drill down to identify the specific SQL Server resource bottleneck causing the issue. When you've located the cause, the next step (and the purpose of this whole activity) is to resolve the problem. System Monitor is able to help with preliminary steps in identifying and resolving SQL Server bottlenecks. Additionally there are a number of tools to allow excellent visibility of SQL Server internals.

Understanding how and when to use System Monitor are both important as this determines the quality of the data collected. You may have had poor experiences with System Monitor in the past. Perhaps this was because you were collecting too much, or not the right kind of data—meaning that you don't see the value this tool can provide. The challenge is multi-layered: collecting the right data, at the right time, and knowing what to do with it. At the end of this chapter, you should be familiar with:

  • Identifying SQL Server resource issues with System Monitor

  • Understanding configuration versus schema problems

  • Knowing best practices for monitoring SQL Server with system monitor

One of the numerous significant improvements in SQL Server 2005 is improved supportability; this means better out-of-the box tools for administration and troubleshooting. SQL Server Profiler and the Database Tuning Advisor (DTA) have both been improved to ...

Get Professional SQL Server® 2005 Performance Tuning now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.