Page 172 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes With Windows Authentication mode, the user account that runs SQL Profiler must be granted
permission to connect to an instance of SQL Server.

12.5.1 SQL Profiler Terminology

To use SQL Profiler, you need to understand the terminology that describes the way the tool
functions. For example, you create a template that defines the data you want to collect. You
collect this data by running a trace on the events defined in the template. While the trace is
running, the event classes and data columns that describe the event data are displayed in SQL
Profiler.

Template: A template defines the criteria for each event you want to monitor with SQL Profiler.
For example, you can create a template, specifying which events, data columns, and filters to
use. Then you can save the template and launch a trace with the current template settings. The
trace data captured is based upon the options specified in the template. A template is not executed,
and must be saved to a file with the .tdf extension.
Trace: A trace captures data based upon the selected events, data columns, and filters. For example,
you can create a template to monitor exception errors. To do this, you would select to trace the
Exception event class, and the Error, State, and Severity data columns, which need to be collected
for the trace results to provide meaningful data. After you save the template, you can then run
it as a trace, and collect data on any Exception events that occur in the server. This trace data can
be saved and then replayed at a later date, or used immediately for analysis.

Filter: When you create a trace or template, you can define criteria to filter the data collected by
the event. If traces are becoming too large, you can filter them based on the information you
want, so that only a subset of the event data is collected. If a filter is not set, all events of the
selected event classes are returned in the trace output. For example, you can limit the Microsoft®
Windows® 2000 user names in the trace to specific users, reducing the output data to only those
users in which you are interested.
Event Category: An event category defines the way events are grouped. For example, all lock
events classes are grouped within the Locks event category. However, event categories only
exist within SQL Profiler. This term does not reflect the way engine events are grouped.
Event: An event is an action generated within the Microsoft SQL Server™ engine. For example:

The login connections, failures, and disconnections.
The Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.

The 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 written to the SQL Server error log.

A lock acquired or released on a database object.
An opened cursor.
Security permissions checks.
All of the data that is generated as a result of an event is displayed in the trace in a single row.
This row contains columns of data called event classes that describe the event in detail.

166 LOVELY PROFESSIONAL UNIVERSITY