{"id":533,"date":"2010-12-31T21:42:00","date_gmt":"2010-12-31T21:42:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(31-of-31)-Event-Session-DDL-Events.aspx"},"modified":"2017-04-13T12:55:28","modified_gmt":"2017-04-13T16:55:28","slug":"an-xevent-a-day-31-of-31-event-session-ddl-events","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/","title":{"rendered":"An XEvent a Day (31 of 31) – Event Session DDL Events"},"content":{"rendered":"

To close out this month\u2019s series on Extended Events we\u2019ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session.  One of my least favorite quirks about Extended Events is that there is no way to determine the Events and Actions that may exist inside a Target, except to parse all of the the captured data.  Information about the Event Session does exist in the Session Definition Catalog Views and Active Session DMV\u2019s<\/a>, but as you change an Event Sessions Events and Actions while it is running, the information in these change as well, so it is possible that a Target has Events and Actions that are not returned by the current information available about the Event Session.  This is where the DDL Events for the Event Session DDL operations can be useful, if the appropriate framework is deployed.<\/p>\n

The DDL Events for Extended Events are not currently documented in the Books Online.  I only recently learned about them from Mike Wachal during a discussion about what I thought was missing from Extended Events.  This is simply an oversight in the documentation, and something that Mike has stated will be fixed, it doesn\u2019t mean that the DDL Events are undocumented and subject to change without notice like other undocumented features of SQL Server.  We can find the DDL Events for Extended Events in the sys.event_notification_event_types.<\/p>\n

\n
SELECT\r\n    type<\/span>,\r\n    <\/span>type_name<\/span>,\r\n    <\/span>parent_type\r\n<\/span>FROM <\/span>sys<\/span>.<\/span>event_notification_event_types\r\n<\/span>WHERE <\/span>type_name <\/span>LIKE <\/span>'%SESSION%'\r\n<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
These can be used just like any other DDL Event to create a DDL Trigger or Event Notification that takes action when one of the DDL operations occurs.  We can use this to log the DDL to track our changes over time, and we can also use it to create a tracking table of the possible outputs from our Event Session, ensuring that we know what information it may have collected when we parse the Event data from the Targets.  We can also use this information to simplify the generation of our XQuery XPATH statements to parse the data from the Targets with a little extra work.  <\/p>\n
In all of my servers, I have a standard database named sqladmin that I keep DBA related information and objects.  For the examples, I will create this database and use it in all the code.  If you have a different database, the scripts can easily be changed to create the objects in that database.  The first thing we\u2019ll do is create our database, and two tables, one for tracking the DDL operations and the other for tracking all of the possible outputs for our Event Session.<\/p>\n
\n
CREATE DATABASE <\/span>sqladmin\r\nGO\r\nUSE <\/span>sqladmin\r\nGO\r\nCREATE TABLE <\/span>dbo.<\/span>XEvents_DDLOperations\r\n( <\/span>DDLEventData XML<\/span>, \r\n  <\/span>ChangeDate DATETIME DEFAULT<\/span>(<\/span>CURRENT_TIMESTAMP<\/span>), \r\n  <\/span>LoginName NVARCHAR<\/span>(<\/span>256) <\/span>DEFAULT<\/span>(<\/span>SUSER_SNAME<\/span>()),\r\n  <\/span>ProgramName NVARCHAR<\/span>(<\/span>256) <\/span>DEFAULT<\/span>(<\/span>program_name<\/span>())\r\n);\r\n<\/span>GO\r\nCREATE TABLE <\/span>XEvents_SessionOutputs\r\n(\r\n    <\/span>EventSessionName NVARCHAR<\/span>(<\/span>256),\r\n    <\/span>EventName NVARCHAR<\/span>(<\/span>256),\r\n    <\/span>EventID INT<\/span>,\r\n    <\/span>ColumnID INT<\/span>,\r\n    <\/span>ColumnName NVARCHAR<\/span>(<\/span>256),\r\n    <\/span>NodeType NVARCHAR<\/span>(<\/span>10),\r\n    <\/span>DataType NVARCHAR<\/span>(<\/span>50),\r\n    <\/span>XMLLocation NVARCHAR<\/span>(<\/span>10),\r\n    <\/span>TypePrecidence INT\r\n<\/span>)\r\n<\/span>GO<\/span><\/pre>\n<\/blockquote>\n
<\/a>The XEvents_SessionOutputs table will have multiple rows for each Event Session defined on the server that track the EventName, the output ColumnName, the NodeType for the data element in the Event XML, the SQL DataType returned by the output, the XMLLocation for where the data of interest exists, and a TypePrecidence value that can be used when multiple Events return the same Data Element with different DataTypes, ensuring that we can pick the most compatible DataType for the output column.  The table also tracks the Event ID in the Event Session for the Event, the Column ID for the output column so that grouping and ordering can be performed during code generation from this table.<\/p>\n
To get the SQL DataType that an output returns, we have to look at the output type_name in the Extended Events metadata for the output column or Action.  To make this easier to do and allow for code reuse, I create a view that maps the type_name in Extended Events to corresponding SQL DataType.  Since Maps can be a type_name in Extended Events, the view queries the sys.dm_xe_map_values DMV and calculates the maximum length of the map_value column for each Map, and then uses the nvarchar datatype and rounds the length up to the nearest power of 10 (ok, it doesn\u2019t actually round but that is the effect of the math operations).  For the actual Event data columns in the base payload, the type_name is transposed to the equivalent SQL DataType that is compatible with XQuery.<\/p>\n
\n
CREATE VIEW <\/span>dbo.<\/span>XETypeToSQLType \r\nAS\r\n    SELECT \r\n        <\/span>XETypeName = <\/span>mv.<\/span>name, \r\n        <\/span>SQLTypeName = <\/span>'nvarchar('<\/span>+<\/span>CAST<\/span>(<\/span>MAX<\/span>(<\/span>LEN<\/span>(<\/span>mv.<\/span>map_value))-(<\/span>MAX<\/span>(<\/span>LEN<\/span>(<\/span>mv.<\/span>map_value))%<\/span>10) + <\/span>10 AS VARCHAR<\/span>(<\/span>4))+<\/span>')'<\/span>,\r\n        <\/span>XMLLocation = <\/span>'text'<\/span>,\r\n        <\/span>TypePrecidence = <\/span>5\r\n    FROM <\/span>sys<\/span>.<\/span>dm_xe_object_columns <\/span>oc\r\n    LEFT JOIN <\/span>sys<\/span>.<\/span>dm_xe_map_values <\/span>mv\r\n        ON <\/span>oc.<\/span>type_package_guid = <\/span>mv.<\/span>object_package_guid\r\n            AND <\/span>oc.<\/span>type_name <\/span>= <\/span>mv.<\/span>name\r\n    WHERE <\/span>oc.<\/span>column_type = <\/span>'data'\r\n      <\/span>AND <\/span>mv.<\/span>name IS NOT NULL\r\n    <\/span>GROUP BY <\/span>mv.<\/span>name\r\nUNION <\/span>ALL\r\n    <\/span>SELECT \r\n        <\/span>XETypeName = <\/span>o.<\/span>name,\r\n        <\/span>SQLTypeName = <\/span>CASE \r\n                            WHEN <\/span>TYPE_NAME <\/span>IN (<\/span>'int8'<\/span>, <\/span>'int16'<\/span>, <\/span>'int32'<\/span>, <\/span>'uint8'<\/span>, \r\n                                    <\/span>'uint16'<\/span>, <\/span>'uint32'<\/span>, <\/span>'float32'<\/span>) \r\n                                <\/span>THEN <\/span>'int'\r\n                            <\/span>WHEN <\/span>TYPE_NAME <\/span>IN (<\/span>'int64'<\/span>, <\/span>'uint64'<\/span>, <\/span>'float64'<\/span>)\r\n                                <\/span>THEN <\/span>'bigint'\r\n                            <\/span>WHEN <\/span>TYPE_NAME <\/span>= <\/span>'boolean'\r\n                                <\/span>THEN <\/span>'nvarchar(10)' <\/span>--true\/false returned\r\n                            <\/span>WHEN <\/span>TYPE_NAME <\/span>= <\/span>'guid'\r\n                                <\/span>THEN <\/span>'uniqueidentifier'\r\n                            <\/span>ELSE <\/span>'nvarchar(4000)'\r\n                        <\/span>END<\/span>,\r\n        <\/span>XMLLocation = <\/span>'value'<\/span>,\r\n        <\/span>TypePrecidence = <\/span>CASE \r\n                            WHEN <\/span>TYPE_NAME <\/span>IN (<\/span>'int8'<\/span>, <\/span>'int16'<\/span>, <\/span>'int32'<\/span>, <\/span>'uint8'<\/span>, \r\n                                    <\/span>'uint16'<\/span>, <\/span>'uint32'<\/span>, <\/span>'float32'<\/span>) \r\n                                <\/span>THEN <\/span>1\r\n                            WHEN <\/span>TYPE_NAME <\/span>IN (<\/span>'int64'<\/span>, <\/span>'uint64'<\/span>, <\/span>'float64'<\/span>)\r\n                                <\/span>THEN <\/span>2\r\n                            WHEN <\/span>TYPE_NAME <\/span>= <\/span>'boolean'\r\n                                <\/span>THEN <\/span>3 --true\/false returned\r\n                            <\/span>WHEN <\/span>TYPE_NAME <\/span>= <\/span>'guid'\r\n                                <\/span>THEN <\/span>3\r\n                            ELSE <\/span>5\r\n                         END\r\n    FROM <\/span>sys<\/span>.<\/span>dm_xe_objects <\/span>o\r\n    WHERE <\/span>object_type = <\/span>'type'\r\n      <\/span>AND <\/span>TYPE_NAME <\/span>!= <\/span>'null'\r\n<\/span>GO<\/span><\/pre>\n<\/blockquote>\n
Using this view, we can create another view that queries the Session Definition Catalog Views, to retrieve the output columns for an Event Session in a format that matches our XEvents_SessionOutputs table.<\/p>\n
\n
CREATE VIEW <\/span>dbo.<\/span>XESession_OutputsFromDMVs\r\nAS\r\n        <\/span>-- Find a list of all the possible output columns\r\n        <\/span>SELECT \r\n            <\/span>ses.<\/span>name AS <\/span>EventSessionName,\r\n            <\/span>sese.<\/span>name AS <\/span>EventName,\r\n            <\/span>sese.<\/span>event_id AS <\/span>EventID,\r\n            <\/span>oc.<\/span>column_id AS <\/span>ColumnID,\r\n            <\/span>oc.<\/span>name AS <\/span>ColumnName,\r\n            <\/span>'data' <\/span>AS <\/span>NodeType,\r\n            <\/span>xetst.<\/span>SQLTypeName AS <\/span>DataType,\r\n            <\/span>xetst.<\/span>XMLLocation,\r\n            <\/span>xetst.<\/span>TypePrecidence\r\n        FROM <\/span>sys<\/span>.<\/span>server_event_sessions <\/span>AS <\/span>ses\r\n        JOIN <\/span>sys<\/span>.<\/span>server_event_session_events <\/span>AS <\/span>sese\r\n            ON <\/span>ses.<\/span>event_session_id = <\/span>sese.<\/span>event_session_id\r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_packages <\/span>AS <\/span>p \r\n            ON <\/span>sese.<\/span>package = <\/span>p.<\/span>name\r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_object_columns <\/span>AS <\/span>oc \r\n            ON <\/span>oc.<\/span>object_name <\/span>= <\/span>sese.<\/span>name\r\n                AND <\/span>oc.<\/span>object_package_guid = <\/span>p.<\/span>guid\r\n        <\/span>JOIN <\/span>XETypeToSQLType AS <\/span>xetst\r\n            ON <\/span>oc.<\/span>type_name <\/span>= <\/span>xetst.<\/span>XETypeName\r\n        WHERE <\/span>oc.<\/span>column_type = <\/span>'data'\r\n    <\/span>UNION\r\n        SELECT \r\n            <\/span>ses.<\/span>name,\r\n            <\/span>sese.<\/span>name,\r\n            <\/span>sesa.<\/span>event_id,\r\n            <\/span>999 AS <\/span>column_id,\r\n            <\/span>sesa.<\/span>name,\r\n            <\/span>'action'<\/span>,\r\n            <\/span>xetst.<\/span>SQLTypeName,\r\n            <\/span>xetst.<\/span>XMLLocation,\r\n            <\/span>xetst.<\/span>TypePrecidence\r\n        FROM <\/span>sys<\/span>.<\/span>server_event_sessions <\/span>AS <\/span>ses\r\n        JOIN <\/span>sys<\/span>.<\/span>server_event_session_events <\/span>AS <\/span>sese\r\n            ON <\/span>ses.<\/span>event_session_id = <\/span>sese.<\/span>event_session_id\r\n        JOIN <\/span>sys<\/span>.<\/span>server_event_session_actions <\/span>AS <\/span>sesa\r\n            ON <\/span>ses.<\/span>event_session_id = <\/span>sesa.<\/span>event_session_id\r\n                AND <\/span>sesa.<\/span>event_id = <\/span>sese.<\/span>event_id\r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_packages <\/span>AS <\/span>p\r\n            ON <\/span>sesa.<\/span>package = <\/span>p.<\/span>name\r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_objects <\/span>AS <\/span>o\r\n            ON <\/span>p.<\/span>guid <\/span>= <\/span>o.<\/span>package_guid\r\n                AND <\/span>sesa.<\/span>name = <\/span>o.<\/span>name\r\n        JOIN <\/span>XETypeToSQLType AS <\/span>xetst\r\n            ON <\/span>o.<\/span>type_name <\/span>= <\/span>xetst.<\/span>XETypeName\r\n        WHERE <\/span>o.<\/span>object_type = <\/span>'action'\r\n<\/span>GO<\/span><\/pre>\n<\/blockquote>\n
We can then create a Server Level DDL Trigger for the DDL_EVENT_SESSION_EVENTS group that will log the DDL operation to the XEvents_DDLOperations table, and at the same time populate the output information in the XEvents_SessionOutputs table when an Event Session is created, add any new outputs when an Event Session is altered, and delete the Event Session information when an Event Session is dropped.  By adding new outputs when a Event Session is altered, we maintain a record of the original outputs, even if the Event was dropped from the Event Session.<\/p>\n
\n
CREATE TRIGGER <\/span>XEvents_DDLTrigger\r\nON <\/span>ALL <\/span>SERVER \r\nFOR <\/span>DDL_EVENT_SESSION_EVENTS\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON<\/span>;\r\n    <\/span>DECLARE <\/span>@EventData XML <\/span>= <\/span>EVENTDATA<\/span>();\r\n    <\/span>INSERT INTO <\/span>sqladmin.<\/span>dbo.<\/span>XEvents_DDLOperations (<\/span>DDLEventData)\r\n    <\/span>VALUES <\/span>(<\/span>@EventData);\r\n\r\n    <\/span>DECLARE <\/span>@EventType NVARCHAR<\/span>(<\/span>256) = <\/span>@EventData.<\/span>value(<\/span>'(EVENT_INSTANCE\/EventType)[1]'<\/span>, <\/span>'nvarchar(256)'<\/span>)\r\n    <\/span>DECLARE <\/span>@SessionName NVARCHAR<\/span>(<\/span>256) = <\/span>@EventData.<\/span>value(<\/span>'(EVENT_INSTANCE\/ObjectName)[1]'<\/span>, <\/span>'nvarchar(256)'<\/span>)\r\n\r\n    <\/span>IF <\/span>@EventType = <\/span>'CREATE_EVENT_SESSION'\r\n    <\/span>BEGIN\r\n        INSERT INTO <\/span>sqladmin.<\/span>dbo.<\/span>XEvents_SessionOutputs \r\n            (<\/span>EventSessionName, <\/span>EventName, <\/span>EventID, <\/span>ColumnID, <\/span>ColumnName, <\/span>NodeType,\r\n             <\/span>DataType, <\/span>XMLLocation, <\/span>TypePrecidence)\r\n        <\/span>SELECT <\/span>EventSessionName, <\/span>EventName, <\/span>EventID, <\/span>ColumnID, <\/span>ColumnName, <\/span>NodeType,\r\n             <\/span>DataType, <\/span>XMLLocation, <\/span>TypePrecidence\r\n        FROM <\/span>sqladmin.<\/span>dbo.<\/span>XESession_OutputsFromDMVs\r\n        WHERE <\/span>EventSessionName = <\/span>@SessionName\r\n    END\r\n\r\n    IF <\/span>@EventType = <\/span>'ALTER_EVENT_SESSION'\r\n    <\/span>BEGIN\r\n        <\/span>-- Add any new outputs to the Table\r\n        <\/span>INSERT INTO <\/span>sqladmin.<\/span>dbo.<\/span>XEvents_SessionOutputs\r\n            (<\/span>EventSessionName, <\/span>EventName, <\/span>EventID, <\/span>ColumnID, <\/span>ColumnName, <\/span>NodeType,\r\n             <\/span>DataType, <\/span>XMLLocation, <\/span>TypePrecidence)\r\n        <\/span>SELECT <\/span>vdmv.<\/span>EventSessionName, <\/span>vdmv.<\/span>EventName, <\/span>vdmv.<\/span>EventID, <\/span>vdmv.<\/span>ColumnID, <\/span>vdmv.<\/span>ColumnName, <\/span>vdmv.<\/span>NodeType,\r\n             <\/span>vdmv.<\/span>DataType, <\/span>vdmv.<\/span>XMLLocation, <\/span>vdmv.<\/span>TypePrecidence\r\n        FROM <\/span>sqladmin.<\/span>dbo.<\/span>XESession_OutputsFromDMVs vdmv\r\n        LEFT JOIN <\/span>sqladmin.<\/span>dbo.<\/span>XEvents_SessionOutputs xeso\r\n            ON <\/span>vdmv.<\/span>EventSessionName = <\/span>xeso.<\/span>EventSessionName\r\n                AND <\/span>vdmv.<\/span>EventName = <\/span>xeso.<\/span>EventName\r\n                AND <\/span>vdmv.<\/span>ColumnName = <\/span>vdmv.<\/span>ColumnName\r\n        WHERE <\/span>vdmv.<\/span>EventSessionName = <\/span>@SessionName\r\n          AND <\/span>xeso.<\/span>EventSessionName IS NULL\r\n    <\/span>END\r\n\r\n    IF <\/span>@EventType = <\/span>'DROP_EVENT_SESSION'\r\n    <\/span>BEGIN\r\n        <\/span>-- Delete the Output data for the Event Session\r\n        <\/span>DELETE <\/span>sqladmin.<\/span>dbo.<\/span>XEvents_SessionOutputs\r\n        WHERE <\/span>EventSessionName = <\/span>@SessionName\r\n    END\r\nEND\r\nGO\r\n<\/span><\/pre>\n<\/blockquote>\n
If we recreate the TrackResourceWaits Event Session from yesterday\u2019s post and then query the XEvents_SessionOutputs table, we can see the outputs that we can expect from that Event Session:<\/p>\n
\n
SELECT <\/span>*\r\n<\/span>FROM <\/span>sqladmin<\/span>.<\/span>dbo<\/span>.<\/span>XEvents_SessionOutputs\r\n<\/span>WHERE <\/span>EventSessionName <\/span>= <\/span>'TrackResourceWaits'\r\n\r\n<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
Using this information, we can also write a query to generate our XQuery statements for each of the outputs, as well as a column definition stub if we wanted to create a table to hold this information for analysis.<\/p>\n
\n
SELECT \r\n    <\/span>'event_data.value(''(event\/'<\/span>+<\/span>NodeType<\/span>+<\/span>'[@name="'<\/span>+<\/span>ColumnName<\/span>+<\/span>'"]\/'<\/span>+<\/span>XMLLocation<\/span>+<\/span>')[1]'', '''<\/span>+<\/span>DataType<\/span>+<\/span>''') AS '<\/span>+<\/span>QUOTENAME<\/span>(<\/span>ColumnName<\/span>)+<\/span>',' <\/span>AS <\/span>XQuery<\/span>,\r\n    <\/span>QUOTENAME<\/span>(<\/span>ColumnName<\/span>)+<\/span>' '<\/span>+<\/span>DataType<\/span>+<\/span>', ' <\/span>AS <\/span>ColumnDefinition\r\n<\/span>FROM\r\n<\/span>(\r\n    <\/span>SELECT \r\n        <\/span>ROW_NUMBER<\/span>() <\/span>OVER <\/span>(<\/span>PARTITION BY <\/span>ColumnName <\/span>ORDER BY <\/span>TypePrecidence <\/span>DESC<\/span>) <\/span>AS <\/span>partitionid<\/span>,\r\n        <\/span>EventSessionName<\/span>,\r\n        <\/span>EventID<\/span>,\r\n        <\/span>ColumnID<\/span>,\r\n        <\/span>ColumnName<\/span>,\r\n        <\/span>NodeType<\/span>,\r\n        <\/span>DataType<\/span>,\r\n        <\/span>XMLLocation\r\n    <\/span>FROM <\/span>sqladmin<\/span>.<\/span>dbo<\/span>.<\/span>XEvents_SessionOutputs\r\n<\/span>) <\/span>AS <\/span>tab\r\n<\/span>WHERE <\/span>EventSessionName <\/span>= <\/span>'TrackResourceWaits'\r\n  <\/span>AND <\/span>partitionid <\/span>= <\/span>1\r\nORDER BY <\/span>EventID<\/span>, <\/span>ColumnID<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
The information in the XQuery column can be copied and pasted into our TSQL Script for parsing the Event Data from the ring_buffer, pair_matching, or asynchronous_file_target Targets.  You could also use this as the basis for writing your own Extended Events Target Data code generator, similar to the one that Adam Machanic<\/a> created a year ago.<\/p>\n
That\u2019s it for this months series on Extended Events.  You can find links to all of the posts on the round up post from December 1, An XEvent A Day: 31 days of Extended Events<\/a>.  Hopefully its been informative, and you now have a better understanding of how Extended Events can be used inside of SQL Server 2008, 2008R2, and in Denali CTP1.<\/p>\n","protected":false},"excerpt":{"rendered":"
To close out this month\u2019s series on Extended Events we\u2019ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session.  One of my least favorite quirks about […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38,45],"tags":[],"class_list":["post-533","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"To close out this month\u2019s series on Extended Events we\u2019ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session.  One of my least favorite quirks about […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-31T21:42:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (31 of 31) – Event Session DDL Events\",\"datePublished\":\"2010-12-31T21:42:00+00:00\",\"dateModified\":\"2017-04-13T16:55:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/\"},\"wordCount\":1047,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday31of31eventsessionddlevents\\\/43627a13\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/\",\"name\":\"An XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday31of31eventsessionddlevents\\\/43627a13\\\/image_thumb.png\",\"datePublished\":\"2010-12-31T21:42:00+00:00\",\"dateModified\":\"2017-04-13T16:55:28+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday31of31eventsessionddlevents\\\/43627a13\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday31of31eventsessionddlevents\\\/43627a13\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-31-of-31-event-session-ddl-events\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"An XEvent a Day (31 of 31) – Event Session DDL Events\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"An XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias","og_description":"To close out this month\u2019s series on Extended Events we\u2019ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session.  One of my least favorite quirks about […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-31T21:42:00+00:00","article_modified_time":"2017-04-13T16:55:28+00:00","og_image":[{"url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (31 of 31) – Event Session DDL Events","datePublished":"2010-12-31T21:42:00+00:00","dateModified":"2017-04-13T16:55:28+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/"},"wordCount":1047,"commentCount":1,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/","name":"An XEvent a Day (31 of 31) - Event Session DDL Events - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png","datePublished":"2010-12-31T21:42:00+00:00","dateModified":"2017-04-13T16:55:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#primaryimage","url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png","contentUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday31of31eventsessionddlevents\/43627a13\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-31-of-31-event-session-ddl-events\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"An XEvent a Day (31 of 31) – Event Session DDL Events"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=533"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/533\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}