Finding Application Session Settings

One of the underused troubleshooting and performance tuning techniques is to validate the application session settings. Things can work fabulous inside of SSMS, but run miserably inside the application.

One of the underused troubleshooting and performance tuning techniques is to validate the application session settings. Things can work fabulous inside of SSMS, but run miserably inside the application. I have long been using Extended Events to help me identify these settings (and yes XE has saved the day more than once by identifying the application settings easily). This article will help show how to use XE to help save the day or at least identify what an application is doing when connecting to SQL Server.

This is only one method, there are other methods. My second option is usually to drop into the DMVs – but others exist beyond that. Tara Kizer jumps into some of those other methods here.

Easy Stuff First

Before diving into XE, first it makes sense to get some more data on what the possible connection settings include. We can query SQL Server for most of the applicable information. For the extended details we have to look it up online.

Inside SQL Server, we have been given the information for what the values are and what the setting name happens to be. Querying the spt_values table for the group of values of type “sop” (think set options) we get the results we need. That will yield results similar to this.

If I take that a little further, I can modify the query to figure out what configurations are enabled for my current session (in SSMS).

For me, currently, this yields the following.

Everything marked with a “1” is enabled and the rest are disabled. Ok, easy enough. Now that we can figure out SSMS values and we have an idea of what they mean, it is time to trap the settings from the application. We will be doing that via XE.

App Settings

In order to find the application settings, we need to capture a specific data point called “collect_options_text”. To find which events have this type of data, we can query the XE infrastructure.

Running the preceding query finds two events – login and existing_connection. Both indicate that the “collect_options_text” is a flag that is disabled by default. When enabled it will collect the options_text for each session (new or existing depending on your connections).

If I delve further into the “login” event, I can see some nice data points for troubleshooting and learn more about what the event does.

Which yields this…

Everything in the orange circles is useful in various troubleshooting scenarios. Just a little side tidbit to keep in your reserves. The blue box is highlighting the options and options_text data points. The options_text becomes enabled when we flip the “collect_options_text” flag to on.

Another interesting note is the “SearchKeyword”. This is a category of sorts (it is a category when looking at it in the GUI). This can tell me all of the events that also might be related to the login event. Looking deeper at that, I can see the following.

That is another juicy tidbit to keep in your back pocket as an extra tool for future use! Seventeen events are in the “session” category and could be related, but we will not use them for this particular event session.

The Juicy Center

Having covered some of the path to getting to the events that matter and what data is available in the events, we are now ready to put a session together.

After creating and starting the XE session, all that is needed is to wait for a login event to occur from the application. Once it does, then check the trace file and evaluate the data. As I look at the data from the application and look specifically at the options_text data, I will see something like the following.

I circled an interesting difference that pops up between the XE session and the @@Options server variable. A login captured by XE will also show the language and date settings for the connection.

The Wrap

Creating a session to capture the settings being used by an application is particularly easy. Being able to trap the relevant data and troubleshoot performance issues is a tool necessary (and yes easy to do) to be able to quickly become a rock-star DBA. I showed how to search for the necessary events (quickly) as well as how to figure out relationships between events in a particular category.

Try it out on one or more of your servers and let me know how it goes.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in seeing the power of XE over Profiler? Check this one out!

This has been the eighth article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page.

One thought on “Finding Application Session Settings”

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.