To use SQL Server Profiler, you need to understand the terms that describe the way the tool functions.
Event
An event is an action generated within an instance of SQL Server Database Engine. The following are some examples.
- Login connections, failures, and disconnections.
- Transact-SQL
SELECT, INSERT, UPDATE, and DELETE statements.
- Remote procedure call (RPC) batch status.
- The start or end of a stored procedure.
- The start or end of statements within stored procedures.
- The start or end of an SQL batch.
- An error is written to the SQL Server error log.
- A lock acquired or released on a database object.\
- An opened cursor.
- Security permission checks.
All of the data generated by an event is displayed in the trace in a single row. This row is intersected by data columns that describe the event in detail.
EventClass
An event class is a type of event that can be traced. The event class contains all of the data that an event can report.
The following are examples of event classes:
- SQL:BatchCompleted
- Audit Login
- Audit Logout
- Lock: Acquired
- Lock: Released
- EventCategory
An event category defines the way events are grouped within SQL Server Profiler. For example, all lock events classes are grouped within the Locks event category. However, event categories only exist within SQL Server Profiler. This term doesn't reflect the way Engine events are grouped.
DataColumn
A data column is an attribute of an event class captured in the trace. The event class determines the type of data collected. Not all data columns apply to all event classes.
- In a trace that captures the
Lock: Acquired event class, the BinaryData data column contains the value of the locked page ID or row.
- The Integer Data data column does not contain any value because it does not apply to the event class being captured.
Template
A template defines the default configuration for a trace.
- It includes the event classes you want to monitor with SQL Server Profiler.
- You can create a template that specifies the events, data columns, and filters to use.
- A template is not executed but rather is saved as a file with a
.tdf extension.
- Once saved, the template controls the trace data captured when a trace-based on the template is launched.
Trace
A trace captures data based on selected event classes, data columns, and filters. For example, you can create a trace to monitor exception errors by selecting the Exception event class and the Error, State, and Severity data columns.
- Data from these three columns need to be collected for the trace results to provide meaningful data. You can then run a trace, configured in such a manner, and collect data on any Exception events that occur in the server.
- Trace data can be saved or used immediately for analysis.
- Traces can be replayed at a later date, although certain events, such as
Exception events, are never replayed.
- You can also save the trace as a template to build similar traces in the future.
SQL Server provides the following two ways to trace an instance of SQL Server.
- Trace with SQL Server Profiler
- Trace using system stored procedures
Filter
When you create a trace or template, you can define criteria to filter the data collected by the event.
- To keep traces from becoming too large, you can filter them so that only a subset of the event data is collected.
- You can limit the Microsoft Windows user names in the trace to specific users, reducing the output data.
- If a filter is not set, all events of the selected event classes are returned in the trace output.
