Extended events advanced

 I would like to describe useful queries for extended events in more detail in this post Extended events get description. I will follow my previous post where I put few basic queries to get some extended events info. 

Extended events are triggers occurring in SQL system. Each extended event has its own set of properties -data you get from result. Extended events are collected in packages, events can be combined from different packages. List of packages defined in SQL server instance 

SELECT * FROM sys.dm_xe_packages

 Beside of that each event can fire other actions synchronously int the same thread within event was invoked. Action extend extended events with own data in response.

For example, if you wold like to get sql text of a query it is defined by an action.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='action'

To filter event data, you can define Predicates. There is a list of objects you can use as predicate source.

 SELECT *
 FROM sys.dm_xe_objects o 
WHERE o.object_type='pred_source'

List of operators

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='pred_compare'

 To start collecting data you must define session object which is set of actions, event data, predicates and target object.

 Target object collects output event data by synchronous or asynchronous way. Data can be collected for example to buffer, file, Windows Events, etc.

SELECT * FROM sys.dm_xe_objects o WHERE o.object_type='target' 

 User – friendly description of internal key words which could be returned from events data

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='map' 

  Relationship between events implemented in created sessions and actions

SELECT * FROM sys.dm_xe_session_event_actions

 Data types you can expect from data collection.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='type'

 Next time I would like to share some solutions implemented using extended events. Stay tuned.