Page 173 - DCAP508_DATABASE_ADMINISTRATION

Unit 12: Tools and Techniques for Monitoring Performance

Event Class: An event class is the column that describes the event that was produced by the Notes
server. The event class determines the type of data collected, and not all data columns are
applicable to all event classes. Examples of event classes include:
SQL:BatchCompleted, which indicates the completion of an SQL batch.
Audit Login, which collects all new connection events since the trace was started.

Audit Logout, which collects all new disconnect events since the trace was started.
Lock:Acquired, which indicates a lock on a resource, such as a data page, has been achieved.
Lock:Released, which indicates a lock on a resource, such as a page, has been released.
Data Column: The data columns describe the data collected for each of the event classes captured
in the trace. Because the event class determines the type of data collected, not all data columns are
applicable to all event classes. For example, the Binary Data data column, when captured for the
Lock:Acquired event class, contains the value of the locked page ID or row but has no value for the
Integer Data data column. Default data columns are populated automatically for all event classes.

12.5.2 SQL Profiler Scenarios

Typically, you use SQL Profiler to:
Find the worst-performing queries: For example, you can create a trace that captures events
relating to TSQL and Stored Procedure event classes, specifically RPC:Completed and
SQL:BatchCompleted. Include all data columns in the trace, group by Duration, and specify
event criteria. For example, if you specify that the Duration of the event must be at least
1,000 milliseconds, you can eliminate short-running events from the trace. The Duration
minimum value can be increased as required. If you want to monitor only one database at
a time, specify a value for the Database ID event criteria.

Identify the cause of a deadlock: For example, you can create a trace that captures events
relating to TSQL and Stored Procedure event classes (RPC:Starting and SQL:BatchStarting)
and Locks event classes (Lock:Deadlock and Lock:Deadlock Chain). Include all data
columns in the trace and group by Event Class. If you want to monitor only one database
at a time, specify a value for the Database ID event criteria.

To view the connections involved in a deadlock, do one of the following:
Open the trace containing the captured data, group the data by ClientProcessID, and
expand both connections involved in the deadlock.

Save the captured data to a trace file and open the trace file twice to make the file
visible in two separate SQL Profiler windows. Group the captured data by
ClientProcessID and then expand the client process ID involved in the deadlock;
each deadlocked connection is in a separate window. Tile the windows to view the
events causing the deadlock.

Monitor stored procedure performance: For example, you can create a trace that captures
events relating to Stored Procedures event classes (SP:Completed, SP:Starting,
SP:StmtCompleted and SP:StmtStarting), and TSQL event classes (SQL:BatchStarting and
SQL:BatchCompleted). Include all data columns in the trace and group by ClientProcessID.
If you want to monitor only one database at a time, specify a value for the Database ID
event criteria. Similarly, if you want to monitor only one stored procedure at a time,
specify a value for the Object ID event criteria.

Audit Microsoft® SQL Server™ activity: You can audit activity in SQL Server using SQL
Profiler. For example, if the security administrator always needs to know who is logged
in to the server, you can create a SQL Profiler trace that provides a complete view of users

LOVELY PROFESSIONAL UNIVERSITY 167