SQL Server Training | Tutorials | How to's

Being a DBA, to optimize a performance, at times, you might want to know

• How many connections are currently established on SQL Server Instance?
• From where SQL Server connection are being made?
• What is client name  / IP Address from SQL Server connection are being made.
• What Login name is being used to make a connection
• What is application name, if connections are being opened by some application.
• What is the connection Type for a particular SQL session
• Which protocol is being used to established a particular connection / session ?
• What sort of Client Interface is being used to make a particular session / connection
• Is a particular session / connection is encrypted or not ?
• What is the Authentication mechanism is being used for a particular session / connection?
• Is that a SQL or Windows credentials is being used for a particular session / client connection ?
• Which client and which application connects in which way?

All these information is really used full, when we are troubleshooting a high number of login / logouts per/sec or we we see a high number of connection where connections are being created but not ended by application etc.

We can find most of these connection information in DMV sys.dm_exec_connections, to effective understanding we might want to connect this DMV with

• sys.endpoints - hold endpoint information that is created in the system. BOL for more info
• sys.dm_exec_sessions – sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. BOL for more info
• sys.dm_exec_connections -  This dmv can be used to show ancient connections via the last_read and/or last_write date times. sys.dm_exec_connections, returns server-level information about the connections to SQL Server. BOL for more info

SQL Script to get Client Connection Details

/*-------------------------------------------------------------------------------------------------------------------------------Description    :     This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG

Author        :    DBATAG
Created on    :    09/01/2011
Modified on   :    09/01/2011
Version       :    1.0
Dependencies  :
                Table                            Procedure            Permissions
                No Dependencies                No Dependencies            View Server State Permissions Required
                ----------------------------------------------------------------------------------------------------------------------------*/
-- Connectivity informations
;WITH con AS
   (SELECT SES.host_name AS HostName
          ,CON.client_net_address AS ClientAddress
          ,SES.login_name AS LoginName
          ,SES.program_name AS ProgramName
          ,EP.name AS ConnectionTyp
          ,CON.net_transport AS NetTransport
          ,CON.protocol_type AS ProtocolType
          ,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex
          ,SES.client_interface_name AS ClientInterface
          ,CON.encrypt_option AS IsEncryted
          ,CON.auth_scheme AS Auth
    FROM sys.dm_exec_connections AS CON
         LEFT JOIN sys.endpoints AS EP
             ON CON.endpoint_id = EP.endpoint_id
         INNER JOIN sys.dm_exec_sessions as SES
             ON CON.session_id = SES.session_id)
-- Detailed list
SELECT *
FROM con
ORDER by con.TDSVersionHex,con.HostName
        ,con.LoginName
        ,con.ProgramName;

/*
-- Count of different connectivity parameters
SELECT COUNT(*) AS [Connections #]
      ,COUNT(DISTINCT con.HostName) AS [Hosts #]
      ,COUNT(DISTINCT con.LoginName) AS [Logins #]
      ,COUNT(DISTINCT con.ProgramName) AS [Programs #]
      ,COUNT(DISTINCT con.NetTransport) AS [NetTransport #]
      ,COUNT(DISTINCT con.TDSVersionHex) AS [TdsVersions #]
      ,COUNT(DISTINCT con.ClientInterface) AS [ClientInterfaces #]
FROM con;
*/

OUTPUT Screenshot