Ready for a change of pace? Good! Today is a really good time to start looking into one of the more basic concepts related to Extended Event Session management.
Consider the occasional need to change a trace. Maybe you added the wrong event to the trace, or maybe you neglected to include the event in the first place. There is also the possibility that all of the events are correct but the actions or predicates may not be completely ideal for your trace session. What if it is just as simple as a missing target or adding another target to the session? All of these are potential changes to an XEvent session that might be needed.
Today, I will cover how easy it is to modify these sessions without dropping the entire session as many examples on the internet show. Each of the configuration changes just mentioned can be handled through some pretty simple scripts (and yes through the GUI as well).
Altered States
There is no getting around it. To demonstrate how to change event sessions, an event session must first exist. Let’s use the following as the demo session.
CREATE EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.auto_stats ( SET collect_database_name = ( 1 ) ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name ) WHERE ( [database_name] = N'AdventureWorks2014' ) ) ADD TARGET package0.ring_buffer ( SET max_events_limit = ( 666 ) , max_memory = ( 65536 ) , occurrence_number = ( 3 ) ) WITH ( MAX_MEMORY = 4096 KB , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 5 SECONDS , MAX_EVENT_SIZE = 2048 KB , MEMORY_PARTITION_MODE = PER_NODE , TRACK_CAUSALITY = ON , STARTUP_STATE = ON ); GO
This session starts with a ring_buffer target. There is a single event in the session. This event has a few actions applied to it along with a predicate on the database_name field. I also have several of the settings for this session defined (whether at the custom field level or at the session level).
After deploying this session, I quickly realize that I flubbed it up a bit. I forgot a few things. Since those items are missing, I need to get them added without negatively impacting the already gathered data. Let’s start with the missing target. I don’t know how I missed it, but I completely forgot to add a file target to this session. The file target is a must have, so let’s get it added.
/* target change */ALTER EVENT SESSION [AlteredState] ON SERVER ADD TARGET package0.event_file ( SET filename = N'AlteredState' , max_file_size = ( 50 ) , max_rollover_files = ( 6 ) ); GO
Phew, that problem has been easily rectified. Further review of the session shows the next problem. My manager requires that the dispatch latency be no more than 4 seconds and that the event memory not exceed 4090 kb. My manager is a little intense about some of these settings. To ensure I adhere to his requirements, I need to change my session settings now.
/* set operations change */ALTER EVENT SESSION [AlteredState] ON SERVER WITH ( MAX_MEMORY = 4090 KB , MAX_DISPATCH_LATENCY = 4 SECONDS ); GO
Good! We are rolling right along here. Fixed a couple of problems real quick with that session and I can continue on with other tasks for the day. Shortly after lunch the manager stops in and asks who changed various database settings. Looking into this session that was just deployed to try and capture some of those events, I get that sinking feeling when I realize that I completely missed that requirement. D’oh! That’s ok for now because I hadn’t turned off the default trace, but I better get the proper event added to the session.
/* add event */ALTER EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.object_altered ( ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name ) ) ; GO
The next time something changes with the database, it will now be captured. Sadly, too many things are crossing over the desk and I am trying to move too quickly. I forgot to enable the custom field to collect the database name, I better make that adjustment.
/* event custom field change */ALTER EVENT SESSION [AlteredState] ON SERVER DROP EVENT sqlserver.object_altered; ALTER EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.object_altered (SET collect_database_name=(1) ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name ) ) ; GO
Nice, I have that adjustment made. Unfortunately this is where it does become a bit more complicated. Notice that I had to drop the event from the session first before making that change to the custom field setting for that event? This should be minimal in overall impact since it does not remove the already captured events from the session target. But it is an extra step that must be remembered when making a change that affects an Event within the session.
So far so good. I can capture the events that relate to a database change. I can figure out who made the change and the statement made for the change, right? Nope. I missed that during the setup. I guess I better add that in so I have better information for auditing the changes made to the database. I can start capturing that with the addition of the correct actions.
/* oops wrong actions for the new event */ALTER EVENT SESSION [AlteredState] ON SERVER DROP EVENT sqlserver.object_altered; ALTER EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.object_altered (SET collect_database_name=(1) ACTION ( package0.event_sequence, sqlserver.client_hostname,sqlserver.nt_username,sqlserver.server_principal_name, sqlserver.database_name, sqlserver.sql_text ) ) ; GO
Everything is now settled in with that session. I go about my merry way for a couple of weeks. Then, one day, I find I need to review the logs to determine who has been changing database settings again. When looking at the log data I discover there are all sorts of object changes being logged to the event session log files. In review of the session definition I figure out the problem. I missed a predicate limiting the captured events to only those that are database type changes. I might as well get that added in.
/* oops, missed that predicate */ALTER EVENT SESSION [AlteredState] ON SERVER DROP EVENT sqlserver.object_altered; ALTER EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.object_altered (SET collect_database_name=(1) ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name, sqlserver.sql_text ) WHERE ([object_type]='DATABASE') ) ; GO
This will now change the event session so the object_altered event will only capture “database” type events. These are the types of events that include settings changes at the database level.
Making changes to an event session without needing to drop and recreate the entire session is a pretty powerful tool. If I script that session as it is now configured, I would get the following:
CREATE EVENT SESSION [AlteredState] ON SERVER ADD EVENT sqlserver.auto_stats ( SET collect_database_name = ( 1 ) ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name ) WHERE ( [database_name] = N'AdventureWorks2014' ) ), ADD EVENT sqlserver.object_altered ( SET collect_database_name = ( 1 ) ACTION ( package0.event_sequence, sqlos.cpu_id, sqlserver.database_id, sqlserver.database_name, sqlserver.sql_text ) WHERE ( [object_type] = 'DATABASE' ) ) ADD TARGET package0.event_file ( SET filename = N'AlteredState' , max_file_size = ( 50 ) , max_rollover_files = ( 6 ) ), ADD TARGET package0.ring_buffer ( SET max_events_limit = ( 666 ) , max_memory = ( 65536 ) , occurrence_number = ( 3 ) ) WITH ( MAX_MEMORY = 4090 KB , EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 4 SECONDS , MAX_EVENT_SIZE = 2048 KB , MEMORY_PARTITION_MODE = PER_NODE , TRACK_CAUSALITY = ON , STARTUP_STATE = ON ); GO
Compare this to the starting session, and it is apparent that all of the changes implemented are now intact.
Altering an Extended Event session is not necessarily a difficult task, but it is a very useful ability. Having this basic skill in your wheelhouse can be very positive and useful for you!
This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.
