5

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.

3
  • every connection needs cpu power ad other resources, which can be too much for a given system, so you would only experience what you already noticed
    – nbk
    May 10, 2021 at 8:15
  • Are you sure all connections are disposed correctly, such as using in C#? May 10, 2021 at 12:30
  • @Charlieface : Yes all connexion are closed and disposed properly.
    – El MoZo
    May 10, 2021 at 12:49

2 Answers 2

8

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.

1
  • 1
    Thanks for the answer JD, I understand it more with the 'pie example' now !!
    – El MoZo
    May 10, 2021 at 13:04
1

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).

5
  • Thanks for the answer @Dan What do you mean by "Are the queries mostly in runable status on the database server" ? I have updated my question to add line of sp_who2 during peak. All those 'awaiting command' lines mean that those connexions are ready to be used again right ?
    – El MoZo
    May 10, 2021 at 13:01
  • @ElMoZo, the awaiting command ones are either in the pool awaiting reuse or waiting for another command to be executed on the acquired connection. If that is what you see during peaks when you get the pool error, I suspect connections are not getting closed/disposed (e.g. missing using blocks).
    – Dan Guzman
    May 10, 2021 at 15:17
  • Isn't it how connexion's pooling is supposed to work ? Connexions stay in 'awaiting command' to be reused because it's less consuming than closing a connexion and open a new one ? All the 'lastBatch' (column from sp_who2) are from the past seconds wich seems indicate that they are well reused no ? (but maybe I understand wrong how it work)
    – El MoZo
    May 11, 2021 at 7:55
  • @ElMoZo, yes, unused pooled connections on the server will show "awaiting command". My point is when you reach the max pool size, you should not see many "awaiting command" connections if the app closes them immediately after executing queries. If you see many "awaiting command" connections when the limit is reached, it could be the code performs a long-running operation after executing the query (e.g. return results to remote client) but before closing the connection. Closing the connection earlier will avoid the issue in that case.
    – Dan Guzman
    May 11, 2021 at 9:38
  • Thank you for precisions @Dan !
    – El MoZo
    May 11, 2021 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.