Extended Events Series (2 of 31) – Querying the Extended Events Metadata

In yesterdays post, An Overview of Extended Events, I provided some of the necessary background for Extended Events that you need to understand to begin working with Extended Events in SQL Server.  After receiving some private feedback on the initial post, I have changed the post naming convention associated with the post to reflect “2 of 31” instead of 2/31, which apparently caused some confusion in Paul Randal’s and Glenn Berry’s series which were mentioned in the round up post for this series.

In today’s post we’ll look at the DMV’s that contain the Extended Events Metadata, and how to query those to find out information about the objects that can be used in Extended Events.

What No UI Support?

One of the first things you’ll notice is that there is no native UI support in Extended Events.  This was a problem very early on and makes working with Extended Events difficult for most people.  If you are the type of person that doesn’t want to write code, you can download the Extended Events SSMS Addin I wrote from Codeplex, and it will go a long way in simplifying working with Extended Events.  However, to really exploit the power of Extended Events, you are going to have to write some things manually.  I generally use the Addin to create my basic Event Session definition and script that to a SSMS editor window, where I then go back and manipulate the DDL as needed.  I’ll cover the Addin in detail in a later post, but one of the items included in it is a Metadata Viewer that allows you to browse the package metadata in a TreeView to see all of the objects available in the Engine.

image 

The Metadata DMV’s

Since there is no UI support, all of the metadata for Extended Events is exposed through a set of DMV’s that I refer to as the Metadata DMV’s.  There are other sets of DMV’s associated with Extended Events that will be covered in later posts as well and I have similar reference names for them as logical groups.  There are four DMV’s that contain metadata information about Extended Events in SQL Server; sys.dm_xe_packages, sys.dm_xe_objects, sys.dm_xe_object_columns, and sys.dm_xe_map_values.  Not all of the objects that are returned by the DMV’s are useable in Extended Events.  To identify the objects that can not be used, the first three DMV’s include a bitwise integer column named capabilities that can be checked with a bitwise logical AND against the number one to determine if the object is for internal usage only.

sys.dm_xe_packages

The sys.dm_xe_packages DMV contains a single entry for each of the packages that has been registered in the Extended Events Engine.  The packages each have a unique guid associated with them that is used to identify the objects that belong to that package.  As mentioned yesterday, there are four packages in SQL Server 2008, but one of those, the SecAudit package is for internal usage only by Server Audits.  When we filter out the internal packages using the previously mentioned bitwise logical AND against the capabilities column, only the useable packages remain.

	-- Extended Event Packages
	SELECT
	    name,
	    guid,
	    description
	FROM sys.dm_xe_packages p
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	
	

In yesterday’s post I talked about the changes between SQL Server 2008 and SQL Server Denali CTP1 with regards to the number of available packages, and the modules that load them. The sys.dm_xe_packages DMV includes the module_address column that maps to the base_address column in the sys.dm_os_loaded_modules DMV, which can be used to see the specific modules name that loaded the package.

	SELECT
	    p.name,
	    p.description,
	    lm.name 
	FROM sys.dm_xe_packages p
	JOIN sys.dm_os_loaded_modules lm
	    ON p.module_address = lm.base_address
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	
	

The output for SQL Server 2008 is:

image

The output for SQL Server Denali CTP1 is:

image 

sys.dm_xe_objects

The sys.dm_xe_objects DMV contains information about all of the objects (events, actions, predicates, targets, types and maps) available in the packages registered in the Extended Events Engine.  It is joined to the sys.dm_xe_packages DMV by the package_guid column to map specific objects back to the package that loaded them.  The object_type column determines the type of object and can be used to filter on to only find specific object types.  The following examples show how to use this DMV to find the specific objects by type.

Events

	-- Event objects
	SELECT p.name AS package_name,
	        o.name AS event_name,
	        o.description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o 
	     ON p.guid = o.package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'event'
	

Actions

	-- Actions
	SELECT p.name AS package_name,
	        o.name AS action_name,
	        o.description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o
	      ON p.guid = o.package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'action' 
	

Targets

	-- Targets
	SELECT p.name AS package_name,
	        o.name AS target_name,
	        o.description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o ON p.guid = o.package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'target'
	

Predicates

Predicates are a special type of object in Extended Events because there are two different types of predicate objects in the Metadata; source objects which provide the global state data elements for filtering on and comparators which provide the textual comparisons that can be performed between a data element and the specified value.

Predicate Sources
	-- State Data Predicates
	SELECT p.name AS package_name,
	        o.name AS source_name,
	        o.description
	FROM sys.dm_xe_objects AS o
	JOIN sys.dm_xe_packages AS p
	      ON o.package_guid = p.guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'pred_source'
	
Predicate Comparators
	-- Comparison Predicates
	SELECT p.name AS package_name,
	        o.name AS source_name,
	        o.description
	FROM sys.dm_xe_objects AS o
	JOIN sys.dm_xe_packages AS p
	      ON o.package_guid = p.guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'pred_compare'
	

Maps

	-- Maps
	SELECT p.name AS package_name,
	        o.name AS source_name,
	        o.description
	FROM sys.dm_xe_objects AS o
	JOIN sys.dm_xe_packages AS p
	      ON o.package_guid = p.guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'map'
	

Types

	-- Types
	SELECT p.name AS package_name,
	        o.name AS source_name,
	        o.description
	FROM sys.dm_xe_objects AS o
	JOIN sys.dm_xe_packages AS p
	      ON o.package_guid = p.guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'Type'
	
	

sys.dm_xe_object_columns

The sys.dm_xe_object_columns DMV provides the information about the columns, or data elements, that exist for a specific object.  There are three types of columns that are returned from this DMV; readonly, data, and customizable.  Readonly columns are additional system metadata about an event, that allows the integration with Event Tracing for Windows.  Data columns are the data elements that are returned by default when the event fires.  Customizable columns have different usages depending on the object type and are covered in detail below.

Event Data Elements

To know if an event is going to be useful or not often requires knowing the data elements returned when the event fires. This can then be used with the requirements to determine what actions to add to a specific Event during Session creation.

	-- Event Columns
	SELECT oc.name AS column_name,
	        oc.column_type AS column_type,
	        oc.column_value AS column_value,
	        oc.description AS column_description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o
	      ON p.guid = o.package_guid
	JOIN sys.dm_xe_object_columns AS oc
	      ON o.name = oc.OBJECT_NAME
	     AND o.package_guid = oc.object_package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND (oc.capabilities IS NULL OR oc.capabilities & 1 = 0)
	   AND o.object_type = 'event'
	   AND o.name = 'wait_info'
	

Configurable Event Elements

Part of the flexibility behind Extended Events, and one of the design features that keeps it highly performant is the inclusion of Event Data Elements that can collect additional data in the base payload for an event that may be more expensive than acceptable for general uses, but may be useful at times, and acceptable to expend the additional impact on. When these types of information exist, the Event will have a customizable data element that has to be turned on in the Event Session definition to collect the additional information. An example of this is the collect_path element for the file_write_completed Event. By default this Event won’t collect the file path, but if this information is needed or considered to be important, it can be turned on and will be collected when the event fires.

	-- Configurable Event Columns
	SELECT oc.name AS column_name,
	        oc.column_type AS column_type,
	        oc.column_value AS column_value,
	        oc.description AS column_description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o
	      ON p.guid = o.package_guid
	JOIN sys.dm_xe_object_columns AS oc
	      ON o.name = oc.OBJECT_NAME
	     AND o.package_guid = oc.object_package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND (oc.capabilities IS NULL OR oc.capabilities & 1 = 0)
	   AND o.object_type = 'event'
	   AND o.name = 'file_write_completed'
	   AND oc.column_type = 'customizable'
	

Target Configurable Options

Some of the targets available in Extended Events have configurable elements that are required to use the target.  An example of this would be the asynchronous_file_target, which requires that you provide a filename for the file.  Other options are optional like the max_file_size and max_rollover_files options for the asynchronous_file_target.

	-- Target Configurable Fields
	SELECT oc.name AS column_name,
	        oc.column_id,
	        oc.type_name,
	        oc.capabilities_desc,
	        oc.description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o
	      ON p.guid = o.package_guid
	JOIN sys.dm_xe_object_columns AS oc
	      ON o.name = oc.OBJECT_NAME
	     AND o.package_guid = oc.object_package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'target'
	   AND o.name = 'asynchronous_file_target'
	

sys.dm_xe_map_values

The sys.dm_xe_map_values DMV provides the key/value pairs for each of the Maps defined in the system.  Maps are linked by their object_package_guid to the specific package that created them.

	-- Map Values
	SELECT name, map_key, map_value 
	FROM sys.dm_xe_map_values
	WHERE name = 'wait_types'	

In SQL Server Denali CTP1, it may seem like there are duplicate Maps for sqlserver, but remember that there are two sqlserver packages, and they are loaded from different modules, so each module has to load its own Maps into the Engine.  It would be theoretically possible for the modules to have different Map definitions for the same Map name in the Engine, if there was a change in one module that didn’t occur in the other module.  Hopefully we don’t actually find that to be true one day.

What’s next?

Now that we have information about the objects available in Extended Events, in the next post we’ll look at the DDL commands used to create and manage Extended Events Sessions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.