SQL Profiler

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

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
   
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:
   
6. When you're ready to stop the trace, click the "stop trace" icon

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

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

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:

• Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements
• Locks being acquired/released
• Stored procedures being started/stopped
• A login connection/failure/disconnection
• A cursor being opened
• Errors written to the event log
• A remote procedure call (RPC) batch status
• Security permission checks

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

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.

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.

• << Previous Lesson
• Next Lesson >>