Cost of Increasing max pool size in SQL Server 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.
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 &quot;a dedicated server&quot; 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).