SQL Script to get Client Connection Details

Leave a comment (0) Go to comments

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_sessionssys.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
FROM con
ORDER by con.TDSVersionHex,con.HostName

-- 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

SQL Script to get Client Connection Details

EOF - SQL Script to get Client Connection Details, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.