SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2005 connection pooling


SQL 2005 connection pooling

Author
Message
amalin_chatterjee
amalin_chatterjee
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 8
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)
krayknot
krayknot
Say Hey Kid
Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)Say Hey Kid (712 reputation)

Group: General Forum Members
Points: 712 Visits: 534
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
amalin_chatterjee
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 8
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
jgrubb
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 567
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
amalin_chatterjee
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 8
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
jgrubb
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 567
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
amalin_chatterjee
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 8
Hey thanks,

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search