Cuando se solucionan problemas y se optimiza el rendimiento de las aplicaciones de SQL Server, es importante validar la configuración de la sesión de la aplicación. Si bien las cosas pueden funcionar bien dentro de SQL Server Management Studio (SSMS), es posible que no se desempeñen como se espera dentro de la propia aplicación. Una técnica poco utilizada para identificar y comprender estas configuraciones es mediante el uso de Extended Events (XE).

En este artículo, exploraremos cómo utilizar XE para identificar y capturar la configuración de la sesión de la aplicación en SQL Server. Este método puede ayudar a salvar el día al proporcionar información valiosa sobre lo que una aplicación está haciendo al conectarse a SQL Server.

Comprendiendo la configuración de conexión

Antes de adentrarnos en XE, es útil tener una comprensión clara de las posibles configuraciones de conexión. Podemos consultar SQL Server para obtener la mayoría de la información aplicable. Para obtener información más detallada, podemos consultar recursos en línea.

SELECT sv.name AS ConfigName, sv.number AS ConfigValue
FROM master.dbo.spt_values sv
WHERE sv.type = 'sop';

Esta consulta recupera el nombre de configuración y el valor para cada configuración de conexión. Al consultar la tabla spt_values para valores de tipo “sop” (opciones establecidas), podemos obtener la información necesaria.

Además, podemos modificar la consulta para determinar qué configuraciones están habilitadas para la sesión actual en 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';

Esta consulta muestra las configuraciones habilitadas para la sesión actual de SSMS. La columna “EnabledForCurrentSession” indica si una configuración está habilitada (1) o deshabilitada (0).

Capturando la configuración de la aplicación con Extended Events

Para capturar la configuración de la sesión de la aplicación, debemos habilitar la bandera “collect_options_text” en XE. Podemos consultar la infraestructura de XE para encontrar eventos que tengan este tipo de datos:

SELECT xo.name, xo.object_type, xo.description, 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';

Esta consulta identifica los eventos que tienen la bandera “collect_options_text”. En este caso, encontramos dos eventos: “login” y “existing_connection”. Al habilitar esta bandera, podemos recopilar el options_text para cada sesión.

Al profundizar en el evento “login”, podemos obtener más información sobre el evento y sus puntos de datos:

DECLARE @EventName VARCHAR(64) = 'login'
,@ReadFlag VARCHAR(64) = 'readonly'
 
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;

Esta consulta proporciona puntos de datos valiosos para solucionar problemas y comprender el evento “login”. Los puntos de datos de options y options_text se habilitan cuando establecemos la bandera “collect_options_text” en on.

Al buscar palabras clave relacionadas con el evento, podemos descubrir otros eventos que pueden ser relevantes:

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;

Esta consulta proporciona eventos adicionales relacionados con la categoría “session”. Si bien estos eventos pueden no ser utilizados en la sesión de eventos actual, pueden ser herramientas valiosas para uso futuro.

Creando una sesión de Extended Events

Ahora que hemos explorado los eventos y puntos de datos necesarios, podemos crear una sesión de XE para capturar la configuración de la sesión de la aplicación:

USE master;
GO

-- Crear la sesión de eventos
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), 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:\Database\XE\AppSessionOptions.xel',
    max_file_size = (5120), max_rollover_files = (4))
WITH (STARTUP_STATE = OFF, TRACK_CAUSALITY = ON);

/* Iniciar la sesión */
ALTER EVENT SESSION AppSessionOptions ON SERVER STATE = START;
GO

Este script crea una sesión de XE llamada “AppSessionOptions” y agrega los eventos “login” y “existing_connection”. La sesión captura los puntos de datos necesarios y los escribe en un archivo de eventos. La sesión se almacena en el directorio “C:\Database\XE”.

Una vez que se crea y se inicia la sesión de XE, podemos esperar a que ocurra un evento de inicio de sesión desde la aplicación. Luego podemos verificar el archivo de seguimiento y evaluar los datos capturados. Los datos de options_text proporcionarán información sobre la configuración de la sesión de la aplicación.

Conclusión

Al utilizar Extended Events, podemos capturar y analizar fácilmente la configuración de la sesión de la aplicación en SQL Server. Esta técnica es invaluable para solucionar problemas y optimizar el rendimiento de las aplicaciones de SQL Server. Con la capacidad de capturar datos relevantes, los DBA pueden identificar y resolver rápidamente problemas de rendimiento, convirtiéndose en DBA estrella en el proceso.

Prueba este método en tus servidores y observa cómo te ayuda a obtener una comprensión más profunda del comportamiento de tu aplicación. Para obtener más información sobre Extended Events, consulta nuestra serie de artículos diseñados para ayudarte a aprender XE poco a poco. Si estás interesado en comparar el poder de XE con Profiler, asegúrate de leer este artículo.

Gracias por leer el octavo artículo de nuestra serie “12 Days of Christmas” de 2018. Para ver una lista completa de los artículos, visita esta página.