Share via

Meditation on sys.dm_exec_connections

  • Article

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.