Meditation on sys.dm_exec_connections
I saw an email thread recently regarding sys.dm_exec_connections and I thought I would spread the word if you aren't already using this DMV in your troubleshooting toolbox. This DMV can answer several different questions. For example:
Q. Is a connection using SSL encryption?
A. Use the encrypt_option column.
Q. Is a connection using Kerberos, NTLM, or SQL authentication?
A. Use the auth_scheme column.
Q. How long has the session been connected to SQL Server?
A. See connect_time.
Q. Is this connection doing anything?
A. See num_reads, num_writes, last_read, and last_write.
Q. Where is this client coming from, and how is it connecting?
A. See client_net_address, client_tcp_port, net_transport, and protocol_type.