How to Find IP Address of All SQL Server Connection? – Interview Question of the Week #280

Question: How to Find the IP Address of All SQL Server Connection?

Answer: I had recently received this question during the Comprehensive Database Performance Health Check. The organization which I was consulting on had extremely heavy traffic from a various mobile application and also had many APIs where the data was constantly uploaded and downloaded.

The question came up as we wanted to know which application from a specific IP address how much is keeping the database busy. Here is the script to figure it out.

SELECT	ecs.client_net_address, ecs.client_tcp_port, ess.[program_name], 
		ess.[host_name], ess.login_name,
		SUM(num_reads) TotalReads, SUM(num_writes) TotalWrites,
		COUNT(ecs.session_id) AS SessionCount
FROM sys.dm_exec_sessions AS ess WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ecs WITH (NOLOCK) 
ON ess.session_id = ecs.session_id 
GROUP BY	ecs.client_net_address, ecs.client_tcp_port, ess.[program_name], 
		ess.[host_name], ess.login_name
ORDER BY SessionCount DESC;

Now when you run the script above, it will give you a list of all the IP addresses and their session count. Additionally, you can also see how much each of the IP address is doing writes and reads into the database.

With the help of the above query, we were able to identify a specific IP address which belonged to one of the mobile application which was doing a lot of Reads. We investigated it further and figured out that the mobile application had issues, which we listed to fix it.

SQL Server Performance Tuning Consulting is indeed a fun and interesting job. Every single day I find new problems and their solutions.

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.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

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