MaxPoolSize - Recommendations

[Guest]

I'm testing now a third part web application (ASP.NET 2.0, MS SQL Server
2005) that seems to work properly but creates up to 400 (sic!) connections
for average 80 concurrent users. The application is pretty straightforward:
login, some grids with data and that’s it. In my opinion (I’m an experienced
..NET architect 10 concurrent connection (in a peak) should be enough but
400 (in the pool) are very strange for me… I’ve informed the vendor that the
current situations is not acceptable for me but I’ve got an answer that
Microsoft doesn’t have any recommendations concerning “max pool size†value,
so it can be set even to the maximum value of connections that are allowed by
MS SQL Server (~ 32.000!). I’ve tried to find any official recommendations in
msdn but I’ve found only sentences like “application dependentâ€, “sense,
rational value†and so on. I have to protect my department from an
application with wrong architecture but I can’t find any strong-based
arguments. Help !

 

[Cowboy \(Gregory A. Beamer\)]

You want a sanity check?

We have an application that had to be built rather quickly. The architecture
had to scale up, but the maintenance had to be simple. As teh director of
business systems had a copy of CodeSmith, we opted for .NET Tiers (may move
to LLBLGen or another O/R mapper). So here are the specs:

Application:
ASP.NET 2.0 front end
..NET mapped business and data layers
SQL Server 2005 data store

Stress test
100 users for 10 minutes - way over the load for rollout

Number of connections consumed
SQL Server activity monitor - 2 connections
Connections instantiated in pool - 2

Not 400 for 100 users, but 2. The only reason two is necessary is the each
set of objects that are unrelated require more than one "provider" to
connect to the database. On the initial load, we cache some information from
three different types of profiles. During this hit, there is a need for two
connections as the second request fires prior to dispose of the connection.

I am sure if I pumped it up, I could consume a few more connections in the
pool. I would have to really screw up the architecture to consume 400
connections, however.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Patrice]

It would look rather that the connection string is not always the same....
Have you checked in SQL Server under which account those connections are
done ?

Also if you look at the max poll size (100 if I remember) and the default
pool size (don't remember) it's likely that :
- either those settings are increased for some reason by the application
- either they create mutliple pools

Anyway the idea is when you are above the default pool size you should have
less connections than users when here you have more connections than
users...


Good luck.

 

[William \(Bill\) Vaughn]

I'm with Mr. Beamer. I've heard of very heavily accessed sites having 25
connections in the pool. 400 is nuts. Patrice, I agree, there is some
serious problem here. Perhaps the pool is not leaking, but it's clearly not
using connections efficiently. I would chart the use of the pool via the
performance counters and see if the number stabilizes at 400 or continues to
rise--it might very well be leaking--slowly. For example, if a query
generates an exception every 1000 operations and the exception handler
bypasses the Close, it could leak slowly and not overflow for days.

Ah, if the ConnectionString changed, there would be 400 pools not 400
connections in the pool.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Patrice]

Not necessarily each tme but sometimes if for example 4 parts in the app
have each their own settings or do something swith the connection string...

--