3

I am using SQL Server 2012 (11.0.5058.0) extended events, and wish to know the network protocol used by each connection (TCP/IP, shared memory etc).

Event session created for the login event via: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(
    ACTION(
sqlserver.client_app_name
,sqlserver.client_connection_id
,sqlserver.client_hostname
,sqlserver.client_pid
...
...

I have added all available actions for the login event (SELECT * FROM sys.dm_xe_objects WHERE package_guid = '655FD93F-3364-40D5-B2BA-330F7FFB6491' AND object_type = 'action' ORDER BY name) but none appear to give the network protocol.

It may be of course that Login is not the correct event to give this information, but I can't see a connection event (or similar) within the XE DMV.

To confirm, I want an extended event session to expose the net_transport information that is returned by dm_exec_connections: -

SELECT net_transport FROM sys.dm_exec_connections
3

Found the answer.

The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(
    sqlserver.session_id
    ,sqlserver.username
    ))
ADD TARGET package0.ring_buffer
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)
GO

And this would then populate options_text with something like: -

network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed

I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.

SSMS | Management | Extended Events | Sessions | Right click | New session | General page | Template = Connection Tracking.

That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.

An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.