Databases Reference
In-Depth Information
implementation experience and as a result are usually very satisfying once resolved. Almost every
performance problem will offer you an opportunity to learn more about how Windows, SQL Server,
and your specific applications communicate and respond to each other.
The Beginning
System Monitor is the beginning of performance troubleshooting. The tool does a great job of providing
a real time overview and logging of system performance data. There are a number of other applications
and functionality that you should consider part of your troubleshooting toolset. In fact, these tools are so
good and enable you to harvest such a lot of useful information that the rest of this topic is dedicated to
their use!
In SQL Server 2000 some of this diagnosis information was exposed through system tables such as
sysprocesses. However, there wasn't always sufficient detail to enable you to determine the root cause
of a problem. As such, memory dumps were frequently required to enable detailed performance
analysis — for most customers these were difficult to obtain; the process was time consuming and usually
resulted in a support call to Microsoft for analysis. Microsoft has reviewed these cases, and in an attempt
to reduce the cost to them of supporting SQL Server 2005 and improving customer satisfaction with the
product they have provided extensive information on the SQL Server Operating System (SQLOS) and
internal structures through Dynamic management views (DMVs).
There are few genuine performance problems that you'll be able to resolve with any one tool; most will
require data from at least two and often three separate tools to be analyzed and interpreted to identify
and resolve the bottleneck. The purpose of this chapter is for you to understand how and when to use
System Monitor. Invariably, in many poor performance situations, you'll reach a point when you'll need
a SQL Profiler trace or DMVs to reveal more information. By the end of this chapter you should be
familiar with the point at which this switch should take place and by the end of the topic, you should be
comfortable with how to use these other tools too!
System Monitor should be considered largely un-intrusive, assuming sensible sample intervals and
a realistic number of counters monitored. System Monitor has the advantage of availability on every
Windows server — there are many other third-party applications specifically targeted at meeting similar
need. However these usually require additional license purchases and most organizations will have some
change control or acceptance process.
Types of Performance Problems
In its simplest form, performance problems have as many potential causes as the number of hardware
and software components in a system. On this basis the scope of an individual problem can be quite huge,
but it is possible to reduce this scope somewhat. Typically we can characterize SQL Server performance
problems in one of two areas:
❑
Configuration-based performance problems
❑
Schema-based performance problems
That isn't to say that every single SQL Server performance problem you'll ever investigate will fall into
one of these two areas, but it provides a useful logical grouping for most problems. The following section
Search WWH ::
Custom Search