Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 connection pooling Expand / Collapse
Author
Message
Posted Friday, October 24, 2008 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2008 3:43 AM
Points: 4, 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)
Post #591035
Posted Friday, October 24, 2008 3:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 07, 2011 1:41 AM
Points: 346, 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
Post #591047
Posted Monday, October 27, 2008 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2008 3:43 AM
Points: 4, 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.

Post #591920
Posted Tuesday, October 28, 2008 9:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 21, 2014 3:07 PM
Points: 277, Visits: 510

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.
Post #592995
Posted Wednesday, October 29, 2008 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2008 3:43 AM
Points: 4, 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?
Post #594090
Posted Thursday, October 30, 2008 6:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 21, 2014 3:07 PM
Points: 277, Visits: 510

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.

Post #594241
Posted Friday, October 31, 2008 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2008 3:43 AM
Points: 4, Visits: 8
Hey thanks,

A good idea. It would be a very good solution. Have to discuss with the team about the same.
Thanks
Post #594837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse