There is a wealth of information within Extended Events. Throughout this series, I have been working on exposing that wealth of information. A good bit of this information happens to be the metadata for Extended Events, and another bit of this information is tied to the metadata for deployed Sessions. To dive into what I have done so far with this plenitude of information, you can review the series here.

What is the point in covering all of this information? I like to call it building blocks. Take a concept little by little and build upon it, while working toward a more comprehensive understanding as well as to hopefully be able to use it for a bigger product.

Having just completed several segments on the metadata for deployed sessions, it is time to bring all of that information together and see how it can be used.

Bringing it Together

Having a solid foundation and understanding of what the metadata is and where it is really helps in creating scripts that can be useful in the day to day management of deployed Extended Event Sessions. For me, the ability to recreate a deployed session, without the use of the GUI is pretty essential. Contributing to the need to be able to do this via script and not a GUI is that (without a third party product) there is no GUI in SQL Server 2008.

To demonstrate how to create a script to serve as a backup of a deployed session, I will first return to the “demosession” script that I have used throughout this series.

CREATE EVENT SESSION [demosession2] ON SERVER
ADD EVENT sqlserver.auto_stats ( 
SET collect_database_name = ( 1 )--fields
ACTION (
  package0.event_sequence--actions
, sqlos.cpu_id
, sqlserver.database_id
, sqlserver.database_name 
)
WHERE ( [database_name] = N'AdventureWorks2014' )--events
)
ADD TARGET package0.event_file 
( SET filename = N'demosession'--fields
, max_file_size = ( 50 )--fields
, max_rollover_files = ( 6 )--fields
),
ADD TARGET package0.ring_buffer
(SET max_events_limit=(666)--fields
,max_memory=(65536) --fields --max buffer size
,occurrence_number=(3)--fields
)
WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS--session
, MAX_DISPATCH_LATENCY = 5 SECONDS--session
, MAX_EVENT_SIZE = 2 MB--session
, MEMORY_PARTITION_MODE = PER_NODE--session
, TRACK_CAUSALITY = ON--session
, STARTUP_STATE = ON--session
);
GO

And if I color-code this script as I have done previously:

In this image, I have separated out the major components by color. The segments that pertain to the session creation and the settings for that session are in grey. Then the target information is in red and so on. I added my notes (to help indicate where the components can be found in metadata) and colored them purple. The wrinkle here is in the SET operations since that is a component that crosses boundaries of the core concepts.

Taking this breakdown, and a serious desire to be able to recreate any session that has been deployed (without the GOOEY), and I have been able to create the following script that will do just that – backup deployed sessions.

SET NOCOUNT ON;
DECLARE @statement VARCHAR(MAX)
,@statement1 VARCHAR(MAX)
,@statement2 VARCHAR(MAX)
,@sessionname VARCHAR(128) = 'system_health'
/* order of events scripted is different 
default order of creation is different than if scripted from the gui
the gui scripts by alpha order of package then event
while original creation has order by event_id only
*/,@eventorder VARCHAR(64) = 'original'; --'original' for order based on original script, --'ssms' for order based on ssms gui scripter 
SELECT @statement = 'CREATE EVENT SESSION [' + ses.name +'] ON SERVER
' 
FROM sys.server_event_sessions ses
WHERE ses.name = @sessionname;
WITH presel AS (
SELECT ses.name AS SessionName
, sese.event_id
, sese.package
, 'ADD EVENT ' + sese.package +'.' + sese.name + ' (' AS EventName
, CASE WHEN ISNULL(sese.predicate,'') = '' THEN ')' ELSE 'WHERE ' + sese.predicate + ' )' END AS SQLPredicate
, CASE WHEN ISNULL(sesf.name,'') = '' THEN '' ELSE 'SET ' + sesf.name + ' = ( ' + CAST(sesf.value AS VARCHAR(10)) + ' )' END AS SETOperation
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_events sese
ON ses.event_session_id = sese.event_session_id
LEFT OUTER JOIN sys.server_event_session_fields sesf
ON sese.event_session_id = sesf.event_session_id
AND sese.event_id = sesf.object_id
WHERE ses.name = @sessionname
), actpresel AS (SELECT ses.name AS SessionName, sesa.event_session_id, sesa.event_id, sesa.package + '.' + sesa.name AS ActPack
FROM sys.server_event_session_events sese
INNER JOIN sys.server_event_session_actions sesa
ON sese.event_session_id = sesa.event_session_id
AND sese.event_id = sesa.event_id
INNER JOIN sys.server_event_sessions ses
ON sesa.event_session_id = ses.event_session_id
WHERE ses.name = @sessionname)
, pstuff AS (SELECT p.SessionName, p.event_id,'ACTION ( 
' + STUFF((SELECT ', ' + pin.ActPack + '
'
FROM actpresel pin
WHERE pin.SessionName = p.SessionName
AND pin.event_id = p.event_id
FOR XML PATH(''),TYPE).value('.','varchar(max)') , 1, 2, '') + '
)' AS ActionPack
FROM actpresel p
GROUP BY p.SessionName, p.event_id
)
SELECT @statement1 = @statement + STUFF(
(SELECT ', ' + CAST(p.EventName + '
' + p.SETOperation + '
' + CASE WHEN ISNULL(ps.ActionPack,'') = '' THEN '' ELSE ps.ActionPack END + '
' + p.SQLPredicate + '
' AS VARCHAR(MAX) ) AS EventStatement
FROM presel p 
LEFT OUTER JOIN pstuff ps
ON p.SessionName = ps.SessionName
AND p.event_id = ps.event_id
ORDER BY p.event_id 
--ORDER BY p.package, p.EventName 
FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 2, '')
,@statement2 = @statement + STUFF(
(SELECT ', ' + CAST(p.EventName + '
' + p.SETOperation + '
' + CASE WHEN ISNULL(ps.ActionPack,'') = '' THEN '' ELSE ps.ActionPack END + '
' + p.SQLPredicate + '
' AS VARCHAR(MAX) ) AS EventStatement
FROM presel p 
LEFT OUTER JOIN pstuff ps
ON p.SessionName = ps.SessionName
AND p.event_id = ps.event_id
ORDER BY p.package, p.EventName 
FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 2, '')
IF @eventorder = 'original'
BEGIN
SET @statement = @statement1
END
IF @eventorder = 'ssms'
BEGIN
SET @statement = @statement2
END
SELECT @statement = @statement + STUFF((SELECT ', ' +  '
ADD TARGET ' + sest.package + '.' + sest.name + '
(SET ' + ca.setop + ' )
'
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_targets sest
ON ses.event_session_id = sest.event_session_id
CROSS APPLY (SELECT STUFF((SELECT ', ' + CAST(sesf.name AS VARCHAR(128)) + ' = ' + CASE WHEN ISNUMERIC(CAST(sesf.value AS VARCHAR(128))) = 1 
THEN '( ' + CAST(sesf.value AS VARCHAR(128)) + ' )
'
ELSE 'N''' + CAST(sesf.value AS VARCHAR(128)) + '''
' END AS setop
FROM sys.server_event_session_fields sesf
INNER JOIN sys.server_event_sessions sesi
ON sesi.event_session_id = sesf.event_session_id
INNER JOIN sys.server_event_session_targets sesti
ON sesf.object_id = sesti.target_id
AND sesi.event_session_id = sesti.event_session_id
WHERE sest.target_id = sesti.target_id
AND sest.event_session_id = sesti.event_session_id
FOR XML PATH(''),TYPE).value('.','varchar(max)') , 1, 2, '') ) AS ca(setop)
WHERE ses.name = @sessionname
FOR XML PATH(''),TYPE).value('.','varchar(max)') , 1, 2, '') 
;
SELECT @statement = @statement + 
CAST('WITH ( MAX_MEMORY = ' + CAST(ses.max_memory AS VARCHAR(20)) +' KB
,EVENT_RETENTION_MODE = ' + ses.event_retention_mode_desc +'
, MAX_DISPATCH_LATENCY = ' + CAST(ses.max_dispatch_latency/1000 AS VARCHAR(20)) +' SECONDS
, MAX_EVENT_SIZE = ' + CAST(ses.max_event_size AS VARCHAR(20))+ ' KB
, MEMORY_PARTITION_MODE = '+ ses.memory_partition_mode_desc +'
, TRACK_CAUSALITY = ' + CASE ses.track_causality WHEN 1 THEN 'ON' ELSE 'OFF' END +'
, STARTUP_STATE = ' + CASE ses.startup_state WHEN 1 THEN 'ON' ELSE 'OFF' END +'
);' AS VARCHAR(1024)) 
FROM sys.server_event_sessions ses
WHERE ses.name = @sessionname;
PRINT @statement;

I have tested this script back through SQL Server 2008 and up through SQL Server 2014. The script has been tested against complex sessions as well as simple sessions (e.g. the “demosession” session I have been using throughout the series).

While testing, I did run into an interesting anomaly so I had to add a little bit of awkwardness to the script. When comparing the original script for “system_health” (that can be found in the u_tables.sql script in the Install folder), to what was produced while scripting sessions from the GUI, I noticed that my script was recreating the session in the same fashion that the original script was doing it. But this did not match what the GUI did.

When scripting from SSMS (the GUI), the events in the session are ordered by Package and then by Event name. My script, originally, was ordering the events by Event_id which is the same as can be found in the u_tables.sql script. Due to this, I added a parameter to allow for the different sort methods. This also underscores that when a session is deployed, the order of Events in that first script is the order the Events will be created within the metadata.

I decided to output the results of the script via a print statement. This permits me a chance to see the entire script and review it. Then, I can copy to a new window and execute or I can save the script.

With this script, I can quickly do what is an essential function of a DBA, I can perform targeted backups of my deployed sessions. Another way of viewing it is that I can reverse engineer (without the GOOEY) any user definable session that is deployed to the server.

This is just one of many articles in this series. In this article, I demonstrated how to backup a deployed session and create a script for recovery purposes of a targeted session. To find more useful information about extended events, you can get caught up here.