Deployed Session Settings

You may recall that I wrote about how to look at the deployed session metadata. Today, I will be talking about deployed sessions again. And it may even be considered metadata as well. This time around, the metadata I will be discussing is a little bit different but will build on that first article. For a refresher on the previous article, you can read it here.

From that first article on session metadata, I listed some views that would be relevant to deployed session metadata. For today, my focus will be on the sys.server_event_sessions view. Beyond building upon the previously mentioned article, I also want to build on a few things that have been discussed over the past couple of articles – particularly the articles demonstrating how to create an event session via script and GUI. You can get links to those articles as well as all articles in the series by checking out the Table of Contents.

Deployed Session

To get things started, I will bring back a script that was recently used to build a session.

CREATE EVENT SESSION [demosession] ON SERVER
ADD EVENT sqlserver.auto_stats ( 
SET collect_database_name = ( 1 )--Event Fields Screen
ACTION (
  package0.event_sequence--Actions Screen
, sqlos.cpu_id
, sqlserver.database_id
, sqlserver.database_name 
)
WHERE ( [database_name] = N'AdventureWorks2014' ) --Predicates Screen
)
ADD TARGET package0.event_file 
( SET filename = N'demosession'  --Data Storage Screen
, max_file_size = ( 50 )  --Data Storage Screen
, max_rollover_files = ( 6 ) --Data Storage Screen
),
ADD TARGET package0.ring_buffer
(SET max_events_limit=(666)  --Data Storage Screen
,max_memory=(65536) --Data Storage Screen
,occurrence_number=(3) --Data Storage Screen
)
WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS--Advanced Screen
, MAX_DISPATCH_LATENCY = 5 SECONDS--Advanced Screen
, MAX_EVENT_SIZE = 2048 KB--Advanced Screen
, MEMORY_PARTITION_MODE = PER_NODE--Advanced Screen
, TRACK_CAUSALITY = ON--Opening Screen
, STARTUP_STATE = ON--Opening Screen
);
GO

You may recall this example session, only this time I have not color coded any of the components. The focus of this article will be around the session specifically, saving the components for future articles. This means that if I were to strip the components out of the script, I would see a script such as the following:

/* Just the Session */CREATE EVENT SESSION [demosession] ON SERVER
...
WITH ( EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS--Advanced Screen
, MAX_DISPATCH_LATENCY = 5 SECONDS--Advanced Screen
, MAX_EVENT_SIZE = 2048 KB--Advanced Screen
, MEMORY_PARTITION_MODE = PER_NODE--Advanced Screen
, TRACK_CAUSALITY = ON--Opening Screen
, STARTUP_STATE = ON--Opening Screen
);
GO

That significantly simplifies things. And that script brings into focus the settings and metadata that I will be discussing.

Session Settings

Keep that script close at hand, I will be referencing it more. Now it is time to start looking into the catalog view sys.server_event_session. This view is the source of the definition of any event sessions that have been deployed. If I have already deployed the event session from the prior script, a quick query to the view, for that session name, is in order.

I am going to break this query up into two for ease in viewing the results.

SELECT ses.event_session_id
, ses.name
, ses.event_retention_mode
, ses.event_retention_mode_desc
, ses.memory_partition_mode
, ses.memory_partition_mode_desc
FROM sys.server_event_sessions ses
WHERE ses.name = 'demosession';
SELECT ses.event_session_id
, ses.name
, ses.max_dispatch_latency
, ses.max_memory
, ses.max_event_size
, ses.track_causality
, ses.startup_state
FROM sys.server_event_sessions ses
WHERE ses.name = 'demosession';

And the results:

Between the script to query the view and the results, it should appear evident that this catalog view stores some key configuration settings for the event session. Or as was previously stated – the session definition. The highlighted data in the image relate directly back to the script used to create the session. As I mentioned in the article on creating a session via the GUI, the first requirement of a session is a session name. Here, we see that the session name is stored and subsequently exposed via this view.

When creating a session via script, if there is a setting specific to the session that you can’t recall the name of exactly, querying this particular view can help remedy that. One would just use the column names and use the columns whose names do not end in _desc where one of with and one without may exist (e.g. event_retention_mode vs. event_retention_mode_desc).

Important notes here with this data are:

1. MAX_DISPATCH_LATENCY is stored as milliseconds but seconds can be supplied via GUI or TSQL Script.
2. MAX_EVENT_SIZE is stored as KB yet MB (for example) can be supplied and it will be converted automatically.
3. MAX_MEMORY is also stored as KB with the same note as MAX_EVENT_SIZE. The default value here is 4 MB.
4. EVENT_RETENTION_MODE accepts ALLOW_SINGLE_EVENT_LOSS, ALLOW_MULTIPLE_EVENT_LOSS, and NO_EVENT_LOSS as values where “ALLOW_SINGLE_EVENT_LOSS” is the default.
5. MEMORY_PARTITION_MODE accepts NONE, PER_CPU, and PER_NODE as values where “None” is the default.
6. The startup_state in my script is on and in the view it reports as off. This is because I altered that setting after the fact.

So what do these settings mean? Very quickly, here is the run-down.

1. MAX_DISPATCH_LATENCY – The max time in milliseconds that an event will stay in buffers before being sent on to the target.
2. MAX_EVENT_SIZE – Memory set aside for events too big to fit in session buffers.
3. MAX_MEMORY – Memory allocated for event buffering.
4. EVENT_RETENTION_MODE – How many events can be afforded to be lost.
5. ALLOW_SINGLE_EVENT_LOSS – Single event is permitted to be lost when all event buffers are full.
6. ALLOW_MULTIPLE_EVENT_LOSS – Allows potentially large numbers of events to be lost from the session.
7. NO_EVENT_LOSS – Not recommended. The name defines it.
8. MEMORY_PARTITION_MODE – Location in memory where event buffers are created.
9. NONE – Single set of buffers created within SQL Server
10. PER_CPU – A buffer is created for each CPU
11. PER_NODE – Buffers created for each NUMA node.
12. TRACK_CAUSALITY – Enables the ability to track related events on different connections. By default this is disabled.
13. STARTUP_STATE – Enables the session to be started when the server is started. The default setting is off.

I have just shown how to view deployed session settings and then briefly described these settings that are available for an Event Session. Having these settings exposed can be a very handy tool for the Database Administrator. I will be demonstrating how useful this can be in an upcoming article.