How to find the protocol used by the connetion

As you know SQL Server supports three types of protocols, or we can called them as SNI Transport Providers, for communicating with a SQL Server instance. How do we know what SNI Transport Provider is being used for our connection?

Details of the connection established can be obtained from sys.dm_exec_connections dynamic management view. Here is the result of it with for a connection established to local instance.

 SELECT CONVERT(nvarchar(128), SERVERPROPERTY('SERVERNAME')) ServerConnected, *  
 FROM sys.dm_exec_connections 
 WHERE session_id = @@SPID;

Since all protocols are enabled and connection made to a local instance, as you see, Connection uses Shared Memory as the protocol. Here is the result when only Named Pipes is enabled.

With TCP/IP enabled;