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.
Best Answer
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.