Back to article

Introduction to SQL Profiler

February 10, 2005

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.

General properties for a new trace

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.

Event properties for a new trace

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.

Data Columns properties for a new trace

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.

Filters properties for a new trace

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 = _
    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 = _
    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.

SQL Profiler in action

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.


Thanks for your registration, follow us on our social networks to keep up-to-date