SQL 2005 connection pooling

Question

SQL 2005 connection pooling

amalin_chatterjee

SSC Veteran

Points: 262
More actions
October 24, 2008 at 3:06 am

#200015

Hi,
this is my first query to the forum.
one of our applications (.net web app) throws the following error frequently
"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."
the above was due to open connections in the code. The same was handled by adding finally & using blocks.
We also want to increase the pool size. Currently we have not specified the pool size in the connection string.
Question
1. what is the default Pool size.
2. What is the maximum pool size which can be specified.
3. should some setting be done in SQL 2005 server or specifying the size in application connection string is enough
4. Most important. What can be the impacts (negative, overheads) of increasing connection pool on the application (specially a web app)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

amalin_chatterjee (10/24/2008)
Hi,
this is my first query to the forum.
one of our applications (.net web app) throws the following error frequently
"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."
the above was due to open connections in the code. The same was handled by adding finally & using blocks.
We also want to increase the pool size. Currently we have not specified the pool size in the connection string.
Question
1. what is the default Pool size.
2. What is the maximum pool size which can be specified.
3. should some setting be done in SQL 2005 server or specifying the size in application connection string is enough
4. Most important. What can be the impacts (negative, overheads) of increasing connection pool on the application (specially a web app)

1. what is the default Pool size.

100

2. What is the maximum pool size which can be specified.

any size you can take as max. pool size in the Web.config in the connectionistring node.

3. should some setting be done in SQL 2005 server or specifying the size in application connection string is enough

yes. but it depends on you

4. Most important. What can be the impacts (negative, overheads) of increasing connection pool on the application (specially a web app)

dynamic slow down of application [sometimes]

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

amalin_chatterjee SSC Veteran Points: 262 More actions · Answer 2

Hi krayknot,

thanks for the reply.

few doubts.

1. Can SQL server handle any pool size specified from the application

Ex if i specify size as 300 or 400 the SQL server will handle the same without any settings

2. about the last point application slowdown. Is the cause for it known. any way to avoid it? Any info about the same would be helpful.

jgrubb SSCrazy Points: 2542 More actions · Answer 3

The main issue with large pools is the memory overhead of the connections. Each active connection consumes resources on both the application server and the database. The application server will drop connections from pool that are not in use (after the timeout). This is also overhead.

Pooling is used to reduce application overhead, allowing the application keep already opened connections and hand them to other threads, since connection and logon is often longer than than the query duration. If you have that many connections (How many user by the way?), the application may not following the acquire late release early method, and carrying the connections too long in code.

amalin_chatterjee SSC Veteran Points: 262 More actions · Answer 4

My application is for sales submission. There are more than 400 users who use the application only for 2 days in a month. So the application has lot of hits in small time frame.

As this is very critical application we cant afford it to be slow or time out. Any suggestions on what value should be given for the pool size so that we get the best out of it?

jgrubb SSCrazy Points: 2542 More actions · Answer 5

Actually, for something that's tremendously spikey, I'd look into a queuing (MSMQ) solution. It depends on you expertise, requirement and the criticality of the app. It can keep the number of sessions on the SQL down, is lower overhead for the client app, and could allow the backend to process the updates in a smoother manner without passing the delays to the UI.

amalin_chatterjee SSC Veteran Points: 262 More actions · Answer 6

Hey thanks,

A good idea. It would be a very good solution. Have to discuss with the team about the same.

Thanks