SQL Profiler

Note that this tutorial was written for those using the 2000 edition of SQL Server (SQL Server 2000). For later versions of SQL Server, check out the SQL Server 2008 tutorial.

SQL Profiler is a handy tool that enables you to monitor events within your SQL Server (or more specifically, a SQL Server instance). For example, you could use SQL Profiler to check the performance of a stored procedure. You could also use SQL Profiler to troubleshoot a problem that's occuring in your production environment.

When using SQL Profiler, you can save the output to a "trace file" so that you can later analyze the events that occured during your trace.

Accessing SQL Profiler

To access SQL Profiler:

  1. Open Enterprise Manager
  2. Select Tools > SQL Profiler menu
Accessing SQL Profiler

Creating & Running A "Trace"

To monitor activity with SQL Profiler, you create a "trace". A trace captures data based on activity at the time it is run.

To create a trace:

  1. Open SQL Profiler as demonstrated above
  2. Select File > New > Trace...
  3. Select the SQL Server you'd like to connect to, provide authentication details, then click OK
    Creating a trace
  4. In Trace Name field, enter a name for the trace, then set the other trace properties as required. For now, just use the default settings
  5. When you're ready to run the trace, click "Run". You will see something like this:
    Running a trace with SQL Profiler
  6. When you're ready to stop the trace, click the "stop trace" icon Stopping a trace

Explanation of the "Trace Properties" Screen

In the previous steps, when we got to the "Trace Properties" screen (step 4), we used the default trace properties. You can change any of these properties as required. For example, you could save the trace output to a file or database table. You could also change the events, data columns, and/or filter the data presented in the trace.

Following is a more detailed explanation of each tab on the Trace Properties screen.

The "General" Tab

Creating a trace - Trace Properties

The following table provides an explanation of the options on the General tab.

FieldDescription
Trace nameProvides a name for the trace.
Trace SQL serverThis is the server you'd like to run the trace against.
Template nameThis allows you to select a template to base the trace on. Templates provide you with a trace where the options from the Events, Data Columns, and Filters tabs are pre-configured. SQL Server includes a number of templates that you can use. SQLProfilerStandard is the default. The template you select will depend on the data you need from your trace. You can also create your own templates if required.
Trace file nameAllows you to provide the full path to a template.
Save to fileAllows you to save the trace output to a file.
Save to tableAllows you to save the trace output to a database table.
Enable trace stop timeAllows you to specify a date and time for the trace to stop.

The "Events" Tab

The Events tab consists of event classes grouped by event categories. An event class is the column that describes the event produced by SQL Server. An event is an action generated within the SQL Server engine. Some examples of events include:

Try adding/removing events to see how this affects the trace output.

Trace Properties - Events

The "Data Columns" Tab

Data columns describe the data collected for each event class included in the trace. When you view the results of a trace, the data is presented within these data columns.

Note that the data columns that are available will depend on the event classes selected. This is because the event class determines the type of data available.

Also note that default data columns are selected automatically with all event classes.

Trace Properties - Data Columns

The "Filters" Tab

Filters allow you to filter the data collected by the event. This can help you eliminate data you're not interested in, so that you can concentrate on the data you are interested in. For example, if you're only interested in activity from one user, you can filter the trace to only that user.

Trace Properties - Filters

Enjoy this page?

  1. Link to this page (copy/paste into your own website or blog):
  2. Link to Quackit using one of these banner ads.

Thanks for supporting Quackit!

Sponsored Link: Design CODE-FREE Websites

Check a Domain:

ZappyHost Logo