Database Reference
In-Depth Information
Although the events listed in Table
6-1
represent the most common events used for determining query performance,
you can sometimes use a number of additional events to diagnose the same thing. For example, as mentioned in
Chapter 1, repeated recompilation of a stored procedure adds processing overhead, which hurts the performance of the
database request. The execution category in the Event library includes an event, sql_statement_recompile, to indicate
the recompilation of a statement (this event is explained in depth in Chapter 11). The Event library contains additional
events to indicate other performance-related issues with a database workload. Table
6-2
shows a few of these events.
Table 6-2.
Events for Query Performance
Event Category
Event
Description
Session
login
logout
Keeps track of database connections when users connect to and
disconnect from SQL Server.
existing_connection
Represents all the users connected to SQL Server before the session
was started.
errors
attention
Represents the intermediate termination of a request caused by
actions such as query cancellation by a client or a broken database
connection including timeouts.
error_reported
Occurs when an error is reported.
execution_warning
Indicates the occurrence of any warning during the execution of a
query or a stored procedure.
hash_warning
Indicates the occurrence of an error in a hashing operation.
warnings
missing_column_statistics
Indicates that the statistics of a column, which are statistics required
by the optimizer to decide a processing strategy, are missing.
missing_join_predicate
Indicates that a query is executed with no joining predicate between
two tables.
sort_warnings
Indicates that a sort operation performed in a query such as
SELECT
did not fit into memory.
lock
lock_deadlock
Occurs when a process is chosen as a deadlock victim.
lock_deadlock_chain
Shows a trace of the chain of queries creating the deadlock.
lock_timeout
Signifies that the lock has exceeded the timeout parameter, which is
set by SET LOCK_TIMEOUT timeout_period(ms).
execution
sql_statement_recompile
Indicates that an execution plan for a query statement had to be
recompiled because one did not exist, a recompilation was forced,
or the existing execution plan could not be reused.
rpc_starting
Represents the starting of a stored procedure. They are useful to identify
procedures that started but could not finish because of an operation that
caused an Attention event.
Query_post_compilation_
showplan
Shows the execution plan after a SQL statement has been compiled.
Query_post_execution_
showplan
Shows the execution plan after the SQL statement has been executed
that includes execution statistics. Note, this event can be quite costly,
so use it extremely sparingly and for short periods of time with good
filters in place.
transactions
sql_transaction
Provides information about a database transaction, including information
such as when a transaction starts, completes, and rolls back.
