Monitoring SQL Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 39

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 enable easier monitoring and
shorter time to identify and resolve issues. Positioning these tools, and familiarizing yourself with
each of them will enable you to use the right tool the first time when working on a production
performance problem. You should feel encouraged and interested in identifying a performance
problem — these are typically challenging and may require computer architecture and software