I don't do as much developer support as I used to, but I still answer
questions on a few mailing lists. Fairly frequently developers seem to lose
track of what's going on when their code calls SQL Server to manipulate
data. "I don't know what's going on when I do this," is the typical lament.
"Have you looked at the actual SQL that's being sent?," I reply. When this
reply generates confusion, I know the original questioner hasn't learned how
to use SQL Profiler. If you haven't either, read on for an introduction to
this useful troubleshooting tool.
Getting SQL Profiler
SQL Profiler is one of the standard suite of client tools that are
distributed with SQL Server 2000. You can install these tools from the SQL
Server setup disk; they are a part of the main SQL Server setup, but you don't
have to install SQL Server to install the client tools. If you don't have SQL
Server, you can still install the client tools by downloading the SQL Server 2000
Trial Software and running its setup. Note that if your copy of SQL Server
is old you should patch it to the Revision A level to avoid the Slammer worm;
the trial download includes the Revision A pieces.
After installing the client tools, you can launch SQL Profiler by selecting
Start, Programs, Microsoft SQL Server, Profiler. SQL Profiler will open without
apparently doing anything. It's waiting for you to define a trace: a set of
characteristics that tell it what to keep an eye on.
Creating a Trace
To get started with SQL Profiler, click the New Trace toolbar button or
select New Trace from the File menu. This will prompt you to connect to a SQL
Server. After selecting a server and providing your authentication information,
click OK, you'll be presented with the Trace Properties dialog box. To set up a
new trace, you need to fill out the four tabs of this dialog box. Don't be
intimidated, though; you can accept all of the defaults and still get a
reasonable trace for most purposes.
Figure 1 shows the General tab. You needn't bother to name the trace unless
you want to save it for later reuse. Trace templates define a set of choices for
the other panels in the dialog box. For starters, use the SQLProfilerStandard
template. As you can see, you can also save your trace results automatically. If
you don't, they'll be available within the SQL Profiler interface, and you can
save them later if you like.
The next stop is the Events tab, shown in Figure 2. There are a tremendous
number of events that you can audit here; don't select them all unless you want
to be truly overwhelmed by information! The default set is a good starting point
when you want to know what's going on with SQL statements sent to your database.
In some cases, you may want to add more events to focus on a particular problem.
For example, if you're struggling with a cursor or transaction issue you'll find
entire categories of events just for those issues.
On the Data Columns tab (see Figure 3) you can tell SQL Profiler how much
information to save for each event that it captures. Again, there are quite a
few choices here, and the defaults provide plenty of information for most casual
tracing. Indeed, you might want to remove a few of the defaults if you're trying
to track down a problem in testing; if you're the only user, then such
information as user name, application name, and login name are unlikely to
provide useful information. You can also use this tab to specify grouping
for the traced events as well as the order of display for columns.
Finally, the Filters tab lets you winnow down the events a bit. By default,
SQL Profiler is set not to capture the SQL statements that it sends to the
server itself, and that's a good starting point. But you can also add many other
criteria. The user interface here is a bit idiosyncratic, but easy enough to
use. For example, to tell SQL Profiler to only listen for events in the
Northwind sample database, expand the Database Name node in the treeview and
then the Like node. This will reveal a textbox where you can type the name of
the database, as shown in Figure 4. Hit Enter to set up the filter.
IE 7
Firefox 2.0
