Connection pooling for same connection string but from different hosts/IP in SQL Server, ADO.NET

user_3479041 0
Viewed 75

I have explored the concept of connection pooling and Max Pool size for SQL Server and it says that for same connection string SQL Server will open the new connections in same pool.

Now, we have two web servers and a load balancer directing requests to either of them. So, the connection string being used by the API from both the servers will be same.

My question is, will both the servers use the same pool to open new connections or will it open the new connections in different pools?

As the host_name is different for the sessions in sys.dm_exec_sessions table, my guess is it should use different pools but I couldn't find any article specifying the same.

ado.net connection-pooling sql-server .net
1 Answers

The connection pool is on the client, not the database server. Each of your web servers has a separate connection pool per distinct connection string and security context.

This query will show the current number of database connections per host for all connection pools:

SELECT host_name, COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
GROUP BY host_name;

Although there is no notion of connection pools on the server, you can get an estimate of connections per pool by aggregating on available connection string attributes:

SELECT host_name, program_name, host_process_id, client_interface_name, login_name, COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
GROUP BY host_name, program_name, host_process_id, client_interface_name, login_name;
user_37111620
Related