Introduction to SQL Profiler
· February 10, 2005
· By Mike Gunderloy
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.