Introduction to SQL Profiler

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.

Writing the Test Code

To demonstrate what SQL Profiler can do for you, I need a little code to
execute. So I’ve put together a very simple application in Visual Basic .NET.
This application has a DataGrid and two button controls. When you click the Load
button, it loads the contents of the Northwind Customers table to the DataGrid.
You can then use the DataGrid to add, edit, or delete data in the underlying
DataSet. When you click the Save button, any changes you made are pushed back to
the database. Here’s the bulk of the code:

Private Sub btnLoad_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnLoad.Click
    ' Retrieve all data from the Customers table
    Dim cmd As SqlCommand = _
     sqlConnection1.CreateCommand()
    cmd.CommandText = "SELECT * FROM Customers"
    Dim da As SqlDataAdapter = New SqlDataAdapter
    da.SelectCommand = cmd
    ' Put it into a dataset
    Dim Customers As DataSet = New DataSet
    da.Fill(Customers, "Customers")
    ' And show it on the user interface
    dgCustomers.DataSource = Customers
    dgCustomers.DataMember = "Customers"
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnSave.Click
    ' Reconnect to the Customers table
    Dim cmd As SqlCommand = _
    sqlConnection1.CreateCommand()
    cmd.CommandText = "SELECT * FROM Customers"
    Dim da As SqlDataAdapter = New SqlDataAdapter
    da.SelectCommand = cmd
    ' Use the CommandBuilder to build the INSERT, 
    ' UPDATE, and DELETE commands
    Dim cb As SqlCommandBuilder = _
     New SqlCommandBuilder(da)
    ' Save changes back to the database
    da.Update(dgCustomers.DataSource, "Customers")
End Sub

Now, the question is: what happens at the database when you push those two
simple buttons? That’s exactly the sort of question that SQL Profiler can answer
for you. Let’s find out!

Running the Trace

To start tracing, click the Run button in the New Trace dialog box. There are
also buttons on the SQL Profiler toolbar to start, stop, and pause traces (SQL
Profiler allows you to have multiple trace windows open and active at the same
time). After starting the trace, I fired up the sample application, loaded the
data, and then edited a single row. I saved the change, closed the application,
and stopped the trace. Figure 5 shows the results.

Surprised at the amount of activity? There are statements to set things up
the way that .NET likes them, and to retrieve the initial DataSet. Then there’s
a statement (including the SET FMTONLY command) whose job it is to retrieve the
column names from the table; this information is used by the SqlCommandBuilder
object to come up with its SQL statements. Finally, you can see the statement
that was actually sent to the database to do the update. Here it is, formatted a
bit:

exec sp_executesql N'UPDATE Customers 
 SET CompanyName = @p1 
 WHERE ( (CustomerID = @p2) AND 
  ((@p3 = 1 AND CompanyName IS NULL) OR
  (CompanyName = @p4)) AND 
  ((@p5 = 1 AND ContactName IS NULL) OR 
  (ContactName = @p6)) AND 
  ((@p7 = 1 AND ContactTitle IS NULL) OR 
  (ContactTitle = @p8)) AND 
  ((@p9 = 1 AND Address IS NULL) OR 
  (Address = @p10)) AND 
  ((@p11 = 1 AND City IS NULL) OR 
  (City = @p12)) AND 
  ((@p13 = 1 AND Region IS NULL) OR 
  (Region = @p14)) AND
  ((@p15 = 1 AND PostalCode IS NULL) OR 
  (PostalCode = @p16)) AND 
  ((@p17 = 1 AND Country IS NULL) OR 
  (Country = @p18)) AND 
  ((@p19 = 1 AND Phone IS NULL) OR 
  (Phone = @p20)) AND
  ((@p21 = 1 AND Fax IS NULL) OR 
  (Fax = @p22)) )', 
  N'@p1 nvarchar(4000),@p2 nchar(4000),
  @p3 int,@p4 nvarchar(4000),@p5 int,
  @p6 nvarchar(4000),@p7 int,@p8 nvarchar(4000),
  @p9 int,@p10 nvarchar(4000),@p11 int,
  @p12 nvarchar(4000),@p13 int,
  @p14 nvarchar(4000),@p15 int,
  @p16 nvarchar(4000),@p17 int,
  @p18 nvarchar(4000),@p19 int,
  @p20 nvarchar(4000),@p21 int,
  @p22 nvarchar(4000)', 
  @p1 = N'Alfred Futterkiste', @p2 = N'ALFKI', @p3 = 0, 
  @p4 = N'Alfreds Futterkiste',@p5 = 0, @p6 = N'Maria Anders', 
  @p7 = 0, @p8 = N'Sales Representative', @p9 = 0,
  @p10 = N'Obere Str. 57', @p11 = 0, @p12 = N'Berlin', 
  @p13 = 1, @p14 = NULL, @p15 = 0, @p16 = N'12209', @p17 = 0, 
  @p18 = N'Germany', @p19 = 0, @p20 = N'030-0074321', 
  @p21 = 0, @p22 = N'030-0076545'

Whew! That statement should show you why the CommandBuilder object isn’t
always a good idea. It will build general-purpose SQL statements that work for
any update to the database – but they are often far bulker than necessary. In
this particular case, UPDATE Customers SET CompanyName = 'Alfred
Futterkiste' WHERE CustomerID = 'ALFKI' would have worked just as well.
If you’re trying to tune the performance of your data access code, SQL Profiler
can help you see the effect that your changes are having.

Things to Explore

Of course, there’s more to SQL Profiler than I can cover in a single short
article. Here are some other features that you might like to dig into as you get
more familiar with its capabilities:

• You can save traces to a file or a SQL Server table. Saved traces can be
  used by the SQL Server Index Tuning Wizard to help tune your database.
• You can save traces to a SQL Script. This gives you a way to “replay” a set
  of actions in the future, without going back through the original source of the
  actions. This is useful for automated testing.
• You can replay traces directly in SQL Profiler.
• You can sort and filter the information captured to find performance
  bottlenecks or deadlocks in your application

All in all, SQL Profiler is a great tool for peering under the hood of your
SQL Server installation. Learn how to use it and you’re sure to save
debugging time in the future.

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.