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.
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:
The output for SQL Server Denali CTP1 is:
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.