Microsoft® SQL Server® 2012 Bible

940

Part VII: Monitoring and Auditing

FROM sys.dm_xe_objects o

JOIN sys.dm_xe_packages p ON o.package_guid = p.guid

WHERE object_type = 'target'

Predicates

A Predicate is used to specify criteria on Events captured. There are a couple of things worth

noting about Predicates. First, the criteria are evaluated in the order in which the Predicates

are written, so it is important to place the most restrictive criteria fi rst. Second, Predicates

(pred_source values) are evaluated before additional Actions are captured. Because

actions occur synchronously, the evaluation of Predicates before performing Actions leads to

signifi cant performance gains, which were previously not enjoyed in SQL Trace.

Use the following query to fi nd all available Extended Events Predicate (global) fi elds and

the Package they belong to:

SELECT

PackageName = p.name, PredicateName = o.name,

PredicateDescription = o.description

FROM sys.dm_xe_objects o

JOIN sys.dm_xe_packages p ON o.package_guid = p.guid

WHERE object_type = 'pred_source'

There is an additional type of predicate that defi nes the rules in which values against predicate fi elds are compared.

Sometimes known as comparators, these values can be found using the preceding query and searching for the

object_type of pred_compare. In most cases, the usage of commonly known comparison operators, such as

>, <, = will be overloaded and will work as expected.

Maps

A Map is similar to a lookup table in the database engine. Maps store detailed data that

can be traced back to, typically, integer data stored in an Extended Events payload. This

enables the engine to store less data for the Event, which aids performance.

One example you commonly see used with Maps is the lookup of different wait types for a

SQL Server instance. You can use the following query to fi nd the lookup values for all wait

types available to Extended Events:

SELECT *

FROM sys.dm_xe_map_values

WHERE name = 'wait_types'

Sessions

A Session is the cohesive object that brings the rest of the Extended Events objects

together and is similar to the concept of a Trace in SQL Trace. The Session is a collection of

c40.indd 940c40.indd 940 7/31/2012 10:02:41 AM7/31/2012 10:02:41 AM

http://www.it-ebooks.info