You’ve been told that your SQL Server app is very slow, especially in the morning hours. You’re pretty sure the problem is in the database itself, and since you’re the only developer, it’s your job to fix it. How do you go about that, and where do you start? Your best bet would be to perform some analysis so that you understand the cause of the performance degradation.

The SQL Profiler tool, which ships with SQL Server, provides the most effective way to perform such analysis. If you are using SQL 6.5, you’ll know this tool as SQL Trace. But whatever you call it, it’s a tool that for monitoring events from a SQL Server. It can report on many kinds of events, such as when a new connection is established, a SQL batch is started, or a SQL batch ends. In fact, there are so many events that to monitor them all would lead to serious information overload.

Getting to know the Profiler dialog
You can get into SQL Profiler from the Tools menu of the SQL Server Enterprise Manager console or from the SQL Server program group in your Start menu. To monitor a particular SQL Server, you’ll first need to create a new trace. Let’s look at how that’s done. The wizard will ask you to specify the SQL Server that will be monitored, and then the Trace Properties dialog box will appear (Figure A).

Figure A
The SQL Profiler Trace Properties dialog

There are four tabs on the Trace Properties dialog: General, Events, Data Columns, and Filters.

The General tab allows you to configure the destination for the trace data; you can choose a text file or specify a SQL Server table. This is really the trivial part, though; the most useful part of the General tab is often skipped. Take a look at the Template Name area, which allows you to select from a set of predefined traces. Being able to use an existing trace as a template for a new one is very useful, because configuring a trace can be a time-consuming affair.

The Events tab is where you define the events you want to receive notifications for when they occur. SQL Profiler can report on many events—more than I could possibly cover here. These events are grouped into categories, which I’ve listed in Figure B.
Figure B


Event category
Description
Cursors

 		Events produced by cursor operations.
Database

 		Events produced when data or log files automatically grow or shrink.
Errors and Warnings

 		Events produced when a SQL Server error or warning occurs.
Locks

 		Events produced when a lock is acquired, cancelled, released, etc.
Objects

 		Events produced when database objects are created, opened, closed, dropped, or deleted.
Performance

 		Events produced when SQL data manipulation (DML) operators execute.
Scans

 		Events produced when tables and indexes are scanned.
Security Audit

 		Events used to audit server activity.
Sessions

 		Events produced by clients connecting to and disconnecting from a SQL Server.
Stored Procedures

 		Events produced by the execution of stored procedures.
Transactions

 		Events produced by the transactions.
TSQL

 		Events produced by the execution of T-SQL statements.
User Configurable

 		User-configurable event classes.

Event categories. Source: MSDN

Each event has one or more data columns that you can use to further customize the information for your trace, and you’ll do that using the Data Columns tab. You’ll want to refer to the documentation (linked in Figure B) for each event for more information on what each data column records.

The Filters tab helps ensure that your trace gathers only the information you need. You can filter the trace results by database name, application name, client ID, error code, etc.

Creating a trace
You can learn more about traces by examining and customizing one of the simpler templates. Open up SQL Profiler and select the SQLProfilerTSQL_Duration template. From the Events tab, shown in Figure C, you can tell that this particular trace has only two events:

  • RPC:Completed is fired upon completion of any Remote Procedure Call.
  • SQL:BatchCompleted is fired upon completion of any batch of SQL statements.

Figure C
Event setup for the SQLProfilerTSQL_Duration template

The data columns that the trace records, shown on the Data Columns tab, include:

  • Duration, which is the time in milliseconds taken by the event.
  • TextData, which is a text value that depends on the event the trace is capturing. In this case, the TextData value shows the SQL statements executed.

As it stands, this trace will record events related to the execution of any SQL statement or stored procedure for all databases in the selected SQL Server, which probably isn’t what you want to record. Instead, you can use the Filters tab to restrict the events that will appear in the trace to a single database.

Let’s say you’re interested only in events from the Pubs database. Go to the Filters tab, and scroll down the Trace Event Criteria tree view until you see the DatabaseName item. Expand it, expand the Like item, and type pubs into the box provided, as shown in Figure D. This will set up a trace that will show you only events from the Pubs database.

Figure D
Filtering the trace to include only events from the Pubs database

Once the trace is configured, click the Run button, and the SQL Profiler will run it. The trace window will update with duration information for all SQL statements and stored procedures executed against the Pubs database. Try it out by running a few simple SELECT queries in Query Analyzer; you might be surprised at how many steps are involved in actually running a simple query.

If you were interested in only long-running events, you could have added a duration filter to the trace by typing in a value in the Duration:GreaterThanOrEqual item on the Filters tab.

Create your own templates

If you find yourself using SQL Profiler often, you’ll quickly get tired of setting the same trace events and filters over and over again. So you’ll probably want to make use of SQL Profiler’s ability to let you create new trace templates. The process is very similar to customizing an existing template and uses the same tabbed dialog. The difference is, after you’ve finished, you can save your template and call it back up at any time.

Locating and eliminating slowdowns of SQL Server applications can be a chore. A knowledge of how to use SQL Profiler to create trace logs of actions performed on your SQL Server databases can help take away much of the pain.