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
Best Answer
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: -
And this would then populate options_text with something like: -
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.