未加星标

Finding Application Session Settings

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

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. 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 s et op tions )we get the results we need. That will yield results similar to this.


Finding Application Session Settings

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.


Finding Application Session Settings
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';
Finding Application Session Settings

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…


Finding Application Session Settings
Finding Application Session Settings
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;
Finding Application Session Settings
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:\Database\XE'; 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 ), co

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Finding Application Session Settings
本站链接:https://www.codesec.net/view/628178.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(136)