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.

How to Find IP Address of All SQL Server Connection? - Interview Question of the Week #280 IPAddress-800x533

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.

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

Computer Network, SQL DMV, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Restore Database With Just Log File
Next Post
SQL SERVER – How to Order By a Parameter?

Related Posts

2 Comments. Leave new

Leave a Reply