Deployed Session Settings

Comments: 2 Comments
Published on: September 28, 2015

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.

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:

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.

And the results:

sessionview_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.

2 Comments - Leave a comment
  1. […] Deployed Session Settings September 28, 2015 […]

  2. […] Deployed Session Settings September 28, 2015 […]

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp

  • @live_sql: #sqlhelp Is there a way to allow users attach DB's using the GUI and them not being SA. I crested custom server roles but still no GUI?
  • @joachimpr: Are your reports at work always taking forever to show you data? This could be the reason why! #SQLServer #sqlhelp
  • @NickyvV: Did anyone ever find a solution for memory consumtion of PP in Excel 2010 that @Kjonge made for 2013 and SSAS? #sqlhelp #ssashelp
  • @Ranjeeth: Can I have DBSer01, DBServ02 having replicas in DBServ03(2 AGs, diff clusters one target). #sqlhelp #alwayson trying to save testing time.
  • @jdanton: @Sir_NiN_Sir both free, but dev=enterprise, feature wise, express, much more limited. For learning, I’d go dev, or just use Docker. #sqlhelp
  • @Sir_NiN_Sir: How's different MSSQL Developer edition and Express edition? both edition for free licensed or not? If I need to practice t-sql.#sqlhelp
  • @jdanton: @Spinner_ Nope. Added a bunch of features to it, as it of SP1 2016. #sqlhelp
  • @Spinner_: #sqlhelp are Microsoft's deprecating sql server express?
  • @SQLHA: @ke0mms Would need to know about your issue before I could even think about giving an answer. #sqlhelp
  • @SQLHA: @ke0mms What do you mean by availability server? That's not a "thing" :) #sqlhelp

Welcome , today is Wednesday, July 26, 2017