Cost of Increasing max pool size in SQL Server

Question

We have a public front website that run fine most of the time. But we sometimes experience peaks of connections (like after a mailing campaign) and we were getting error like :

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

We are pretty sure that all our connections are closed properly (no connection leak).

So we decided to increase the number of connection allowed in the pool (which is 100 by default).

We increased it to 1000 (max pool size=1000; in our connection string), and now our site can handle most peaks of connections. (I precise the site run on a dedicated server)

My question is : What may be the negative effects of the increase of the max connections pool ?

EDIT : Here's whats I have when I use sp_who2 during those peaks :

If max pool is set to 100, I have more than 100 lines of those 'awaiting command' lines :

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID	REQUESTID
number	sleeping	db_user	IIS pool name of my website	.	MyDatabaseName	AWAITING COMMAND	0	0	05/10 14:48:59	.Net SqlClient Data Provider	137	0

And I can see that all 'LastBatch' were executed just a few second before.

Answer 1

Think of your server's CPU like a pie, and SQL Server is a party, the connections are your guests at that party. If you know you're going to have 10 guests (connections) then you need to cut the pie into 10 equal slices so everyone gets a fair share of a piece.

Now imagine the scenario you have 10 guests but 10 more show up unexpectedly, making you have a total of 20 guests at the party. Well either you can still cut the pie into 10 equal slices and then only 10 guests can have dessert at one time, or you can cut those 10 slices smaller so you have 20 equal sized slices of pie now so you can give each guest an equal slice (like increasing your max pool size).

So essentially you're allowing more connections to your SQL Server which will need to be allocated a fair amount of CPU per connection. With a fixed amount of CPU (taking scale-up cloud services out of this discussion), that means less CPU may be available to your other connections for the queries they need to run, which theoretically could make those queries take longer to complete. Other server resources can be affected in a similar fashion as well (like nbk mentions) such as the Memory since more connections will now be competing for the available Memory for it's queries as well.

It depends on how your application is designed and how busy the load can get up to, but increasing the max pool size is a fair solution to try for the issue you're facing. Though you might want to not take such a big jump at first. Perhaps try doubling or tripling the initial setting and work your way up. If you still run into that error or other performance issues, your only options then might be to provision more CPUs to your server, taking a look at how the application can be optimized to be faster and smarter with the database operations it does, and looking at if there's performance optimization opportunities with the queries and/or database design itself.

Answer 2

What may be the negative effects of the increase of the max connections pool?

If connections are indeed properly closed and disposed when not actively used (i.e. immediately returned to the pool) and you have capacity headroom on both the app server and database server, I would not expect negative effects with a larger pool size. However, increasing the max pool size to avoid errors at peak load might not be the best approach depending on the root cause. Consider the implications on both application and database server resources.

Are the queries mostly in runable status on the database server? This may indicate query/index tuning is needed or the database server could use more cores to handle peak demand within SLAs.

Are the queries on the server side mostly suspended due to async network waits? This may be a symptom the web server is slow consuming query results under load, indicating the app server might need more cores, instances, or network bandwidth. You mentioned "a dedicated server" so perhaps additional app servers are in order. Note that 100 active queries also requires 100 threads on both app server and database server.

Are the queries on the server side mostly suspended due to other resource waits, such as IO, locks, or latches? This can be mitigated with query/index tuning or db server configuration (e.g. more tempdb files in the case of tempdb page latch contention).