Databases Reference
In-Depth Information
that meet certain criteria have their actions performed. For example, you may only want data to
be collected when an event happens in the context of a specii c database or when a specii c user
executed the query that caused the event. Although these may seem like reasonably high-level condi-
tions on which to i lter, you would be correct to assume that with Extended Events they can be a lot
more technical, as you'll see in a moment.
Filters can delve deep into a database's internal objects, as well as a SQL Server instance's and que-
ry's runtime properties. For that reason, the most common i lters you'll likely start using are for a
query's runtime duration, the lock mode taken, or the object ID affected by the event. Dei ned i lters
must always evaluate as a Boolean expression; meaning you should focus on using the usual =, >, <,
and <> operators.
Following are some examples of i lters you might dei ne for your sessions:
➤
For events in all user databases:
sqlserver.database_id > 4
➤
For events in all user databases not performed by a system task:
sqlserver.database_id > 4
and sqlserver.is_system = 0
➤
For queries taking longer than 10 seconds:
duration > 10000
The complete list of available i lters, or
predicate sources
, as you'll see them referred to, is available
by querying a DMV as shown here:
select name, description from sys.dm_xe_objects where object_type = 'pred_source'
If you're beginning to use Extended Events instead of Proi ler, these i lters are the ones you are likely
to initially want to use; however, some i lters contain internal logic that wasn't available in Proi ler.
For example, you can set a session to count how many times an event it monitors for occurs, and
only perform an action every
n
occurrences of the event. This might be useful for something that
happens so often that it's not practical to log every occurrence, but you still want to monitor it.
Finally, the way in which i lters are evaluated is also more efi cient than what you might be used to
with other tools, as the objective is always to limit the overhead of using Extended Events as much
as possible. Although i lters are evaluated each time their associated events occur, they don't have
to add signii cant overhead to the user's query execution time if they're written optimally. Extended
Events use a method known as
short circuiting
to evaluate a session's i lters. This means each part
of the i lter's dei nition is put in an ordered list, and each item in the list is evaluated only if the
previous item evaluated to
true
. Therefore, if the most selective items in the i lter are dei ned i rst,
most of the i lter may never need to be evaluated until necessary. This is very different from tradi-
tional SQL Server select queries, for which each part of the
where
clause is evaluated and included
in the query plan before execution, regardless of the value it might actually bring to the query.
Search WWH ::
Custom Search