Troubleshooting
SQL 2005:
Opening the Database
Administrator’s
Toolbox
1-800-COURSES
www.globalknowledge.com
Expert Reference Series of White Papers
Introduction
There are many methods for troubleshooting databases. Many people jump straight to a Google search, or run
to find the senior DBA who, most likely, has seen this problem before. While these may provide some results,
there’s no better solution than tracking down empirical evidence as to the cause of a bottleneck or to the
identity of the troublesome transaction. In addition to getting real answers (as opposed to trying other peo-
ple’s solutions), you gain a much greater understanding of SQL’s inner workings and of how to optimize your
database
.
Upon first hearing the words “empirical evidence,” many of you may be immediately dissuaded. Fear not! The
tools are at hand, and they are relatively simple to use
. A familiarity with logs, monitors, and other built-in pro-
grams will ensure that you have a number of research choices when trouble arises.
Two Types of Troubleshooting
Depending upon your needs, you can choose to monitor your server in two ways.
Real Time: When you need information about what is happening at this very moment
Trend: Used when you need to see or record the performance and information over a period of time, or if you
can recreate the problem for recording purposes
Most people will jump to real time monitoring as their first option. It gives instant information, and, if you
know what you are looking for, it can provide fast verification to your analysis. Trend monitoring is often used
when trying to optimize a server or to deal with a problem of unknown origins. It can be used to cast a wide
net and then to review the situation in detail.
Real Time Monitoring
Raw SQL Counters

use a single value as a reason to go shopping for hardware, it can be used to corroborate other factors.
Copyright ©2007 Global Knowledge T
raining LLC. All rights reserved.
Page 3
Mini Cheat Sheet for SQL 2005 Counter
s
**Note that most of these v
alues assume that the physical server’s sole responsibility is running SQL Server
SQL’s Built-In Monitoring Systems
In addition to gathering generic data for you to analyze, SQL also has a number of built-in tools for monitoring
specific situations
. Activity Monitor, Replication Monitor, and Job Activity Monitor are some of these utilities
and are all found within Management Studio. While very narrow in focus, they can allow you to verify or dis-
credit specific concerns quickly.
Activity Monitor will give you detailed information on what is happening right now in terms of locks and
macroscopic activity by user or process. Replication Monitor will give you the rundown on latency for each
individual subscription being replicated. The Job Activity Monitor will give not only the ability to start, stop,
and monitor all jobs from a particular server, but it will also provide historical information on a job-step by job-
step basis for each time that a job has run. It will be a rare occurrence when a DBA will not be able to deci-
sively narrow their focus when troubleshooting jobs using these tools. (Figure 2)
Trend Monitoring
While troubleshooting in real time is often the first response to an immediate need, the monitoring of trends is
more likely to provide results when faced with improving efficiency in the long run.
Baselines
Trend monitoring recognizes that a value gleaned from system monitor does not normally give a clear picture
by itself
.
T
he power of these v
alues increase dramatically when we have something with which to compare

used format.
Figure 2: Launch Activity Monitor, Job Activity Monitor, and Replication Monitor from within Management Studio.
(right-click on Replication to access Replication Monitor)
Counter Logs
System monitor is normally a place to go for a quick glance at the immediate health of your server. After open-
ing the utility, move down a section in the left pane and you will find counter logs (Figure 3).
This portion of the utility will allow you to monitor the same counters we mentioned earlier, but it also gives
you the opportunity to record these counters for later use and comparison. By keeping this log, you are creat-
ing your baseline. Any time a major change occurs on your network (new hardware or software, additional
users), the baseline should be renewed. You can also view a timeline of the events you recorded to investigate
the timings of slow downs and other events.
Copyright ©2007 Global Knowledge T
raining LLC. All rights reserved.
Page 5