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.
SELECT sv.name AS ConfigName, sv.number AS ConfigValue FROM master.dbo.spt_values sv WHERE sv.type = 'sop';
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).
DECLARE @Options BIGINT = @@OPTIONS; SELECT @Options AS OptionsValue SELECT sv.name AS ConfigName, sv.number AS ConfigValue ,CASE WHEN sv.number & @Options > 0 THEN 1 ELSE 0 END AS EnabledForCurrentSession FROM master.dbo.spt_values sv WHERE sv.type = 'sop';
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.
SELECT xo.name, xo.object_type, xo.description--, xoc.name AS ColumnName , xoc.column_type, xoc.capabilities_desc, xoc.description AS ColumnDescription FROM sys.dm_xe_objects xo INNER JOIN sys.dm_xe_object_columns xoc ON xo.name = xoc.object_name AND xo.package_guid = xoc.object_package_guid WHERE xoc.name = 'collect_options_text';
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.
DECLARE @EventName VARCHAR(64) = 'login' --'existing_connection ,@ReadFlag VARCHAR(64) = 'readonly' --readonly' --ALL if all columntypes are desired SELECT oc.OBJECT_NAME AS EventName ,oc.name AS column_name, oc.type_name ,oc.column_type AS column_type ,oc.column_value AS column_value ,oc.description AS column_description ,ca.map_value AS SearchKeyword FROM sys.dm_xe_object_columns oc OUTER APPLY (SELECT TOP 1 mv.map_value FROM sys.dm_xe_object_columns occ INNER JOIN sys.dm_xe_map_values mv ON occ.type_name = mv.name AND occ.column_value = mv.map_key WHERE occ.name = 'KEYWORD' AND occ.object_name = oc.object_name) ca WHERE oc.object_name = @EventName AND oc.column_type <> @ReadFlag;
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.
/* Keyword search */DECLARE @Keyword VARCHAR(64) = 'session'
SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS DLLName
,olm.file_version
,xp.name AS PackageName
,mv.map_value AS SearchKeyword
,ch.Channel
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values mv
ON oc.type_name = mv.name
AND oc.column_value = mv.map_key
AND oc.object_package_guid = mv.object_package_guid
AND oc.name = 'KEYWORD'
INNER JOIN sys.dm_xe_packages xp
ON oc.object_package_guid = xp.guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel
FROM sys.dm_xe_object_columns c
INNER JOIN sys.dm_xe_map_values v
ON c.type_name = v.name
AND c.column_value = CAST(v.map_key AS NVARCHAR)
WHERE c.name = 'channel') ch
ON ch.EventName = oc.object_name
AND ch.PkgGuid = oc.object_package_guid
WHERE mv.map_value = @Keyword
ORDER BY oc.object_name;
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.
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AppSessionOptions' )
DROP EVENT SESSION AppSessionOptions
ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION AppSessionOptions ON SERVER
ADD EVENT sqlserver.login ( SET collect_database_name = ( 1 ), collect_options_text = (1)
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name
,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence,
sqlserver.database_id, sqlserver.database_name,
sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id,
sqlserver.context_info, sqlserver.client_connection_id )
),
ADD EVENT sqlserver.existing_connection ( SET collect_database_name = ( 1 ), collect_options_text = (1)
ACTION ( sqlserver.sql_text,sqlserver.nt_username,sqlserver.server_principal_name
,sqlserver.client_hostname,
package0.collect_system_time,package0.event_sequence,
sqlserver.database_id, sqlserver.database_name,
sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id,
sqlserver.context_info, sqlserver.client_connection_id )
)
ADD TARGET package0.event_file ( SET filename = N'C:DatabaseXEAppSessionOptions.xel'
, max_file_size = ( 5120 )
, max_rollover_files = ( 4 ) )
WITH (STARTUP_STATE = OFF
,TRACK_CAUSALITY = ON);
/* start the session */ALTER EVENT SESSION AppSessionOptions
ON SERVER
STATE = START;
GOAfter 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.
