Database Reference
In-Depth Information
To add an action, just click the check box in the list provided on the Global Fields page shown in Figure
6-6
.
You can use additional data columns from time to time to diagnose the cause of poor performance. For example,
in the case of a stored procedure recompilation, the event indicates the cause of the recompile through the
recompile_cause event field. (This field is explained in depth in Chapter 17.) A few of the commonly used
additional actions are as follows:
•
plan_handle
•
query_hash
•
query_plan_hash
•
database_id
•
client_app_name
•
transaction_id
•
session_id
Other information is available as part of the event fields. For example, the binary_data and integer_data event
fields provide specific information about a given SQL Server activity. For instance, in the case of a cursor, they specify
the type of cursor requested and the type of cursor created. Although the names of these additional fields indicate
their purpose to a great extent, I will explain the usefulness of these global fields in later chapters as you use them.
Event Filters
In addition to defining events and actions for an Extended Events session, you can also define various filter criteria.
These help keep the session output small, which is usually a good idea. You can add filters for event fields or global
fields. You also get to choose whether you want each filter to be an OR or an AND to further control the methods
of filtering. You also get to decide on the operator, such as less than, equal to, and so on. Finally, you set a value
for the comparison. All this will act to filter the events captured, reducing the amount of data you're dealing with
and, possibly, the load on your system. Table
6-3
describes the filter criteria that you may commonly use during
performance analysis.
Table 6-3.
SQL Trace Filters
Events
Filter Criteria Example
Use
sqlserver.username
= <some value>
This captures events only for a single user or login.
sqlserver.database_id
= <ID of the database to monitor>
This filters out events generated by other databases.
You can determine the ID of a database from its name
as follows:
SELECT DB_ID('AdventureWorks20012')
.
duration
>=200
For performance analysis, you will often capture a trace
for a large workload. In a large trace, there will be many
event logs with a duration that is less than what you're
interested in. Filter out these event logs because there is
hardly any scope for optimizing these SQL activities.
physical_reads
>=2
This is similar to the criterion on the duration filter.
sqlserver.session_id
= <Database users
to monitor>
This troubleshoots queries sent by a specific server
session.
