Determining what to Raising Max Pool Size to for my Connection Pool

Question

So, I am looking to increase the Max Pool Size. The problem is that I don't know what a "reasonable" increase would be. Right now I am not setting it, just using the default of 100. The following are my more specific questions:

1. Why is this number defaulted at 100, seems low. What are the negatives of significantly raising it to 1000 or something like that?

2. Is there a good way to determine what this Max Should be raised to?

3. What is the "scope" of the Connection Pool? Is this pool all connections to the database? Each "Machine/Server" connecting has is own Pool?

Background:

While my application is running we received the following error:

"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." I have read alot of other post with a similar problem, all of which discuss how I should do one of two things.

1. Make sure I am closing all connections after opening them
2. Increase the max pool size

I have an extremely large application, so the (1) is hard to determine if somewhere a connection is not being closed. I can't find one that is not.

Answer 1

Why is this number defaulted at 100, seems low.

100 connections roughly means that you can handle two hundred 500ms database processings per second on every second without running low on connections. That's quite high. If you hit this limit, it's time to have a look at optimization.

What are the negatives of significantly raising it to 1000 or something like that?

Is there a good way to determine what this Max Should be raised to?

On a rough basis, it should be the number of connections you will need per second times the average execution time necessary before releasing each connection. For example, if you need to open one hundred new connections per second, each of them requiring 10 seconds before release (which would be really huge), you would need something like 1000 connections in your pool to handle it on the long run.

What is the "scope" of the Connection Pool?

I would say it is on the AppDomain (will check that)

Is this pool all connections to the database?

Connections are pooled based on connection strings. Any small difference in the connection strings will lead to different pools (though I'm not sure if it is case sensitive)

Each "Machine/Server" connecting has is own Pool?

About your problem, it might be several things :

• your app receives much more traffic than it can handle (SQL profiler should help about that, raising the pool size would then help)
• you have some queries that takes too much time and are called too often (taking several seconds, called several times per second) creating a bottleneck (profiler would help too)
• you leak connections or open too many of them in some nested (recursive) loop
• you have so many pools that you hit the database limit for connections ( 32767 on my 2008R2 Std Select @@MAX_CONNECTIONS ) That seems unlikely