SQL SERVER – Network Protocol and IP Address

3 years ago

Today we are going to look at a very simple query my client asked me during a recent Comprehensive Database Performance Health Check. The question was if there is a way to know how many sessions are connected to current SQL Server instance with their network protocol as well as IP address. Absolutely, SQL Server is pretty good at surfacing such information. Here is the script.

Script for Network Protocol

SELECT
c.session_id, c.net_transport,
c.client_net_address,
s.host_name, s.program_name,
s.login_name,
c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id

When you run the query above it will give you a result like the following image.

On that screen, you can see various details related to IP address as well as the application which is connecting the SQL Server instance.

Here are a few blog posts which are related to Network Protocol:

How to Map Network Drive as Fixed Drive? – Interview Question of the Week #258

Question: How to Map Network Drive as Fixed Drive in SQL Server?
Answer: The command to map any drive as a fixed drive is Windows command and you can run that in the SQL Server with the help of the extended stored procedure.

SQL SERVER – Analysis Services and the Network Performance

SQL Server Analysis Services (SSAS) is becoming increasingly popular as an OLAP platform for business analysts. There are many tools available for enhancing the analysts’ ability to process data and get meaningful insights. These vary from direct queries in Excel to custom applications.

Comprehensive Database Performance Health Check

Here are six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

Reference: Pinal Dave (https://blog.sqlauthority.com)