# of pooled connections seem too high

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

We have a production web site that's data intensive (save user input to DB
and query for displaying) with the ASP.NET app part on one W2K server and
SQL 2000 DB on another W2K server. I have set up performance logs to log #
of pooled database connections on the web server every 60 seconds (.NET CLR
Data(_global_)\SqlClient: Current # pooled connections). The # of
connections is constantly higher than the # of active ASP.NET sessions.
E.g. right now there are 25 connections and 18 active session. Is this
normal? I would think the # of pooled DB connections shouldn't be higher
than the # of active sessions since the connections in the pool are shared
and user certain pauses between loading pages and entering data.

Last year during peak usage when the # of active sessions got around 180 and
# of connections peaked at around 150, the SQL Server experienced deadlock
as too many processes were waiting for one another. User experience was
very bad during that period needless to say. We redesigned the data layer
and made very sure that every call to the database closes the connection
explicitly. So I'm concerned that the effort didn't resolve anything and am
afraid the same problem would happen again. All the calls to the database
are straightforward, no transactions involved.

Could anyone point out what the potential problems we might have?

Thanks a lot
Bob
 
There is often some lagtime. The connections aren't governed by the number
of active sessions.They're basically up to their own garbage collection
routines. It's actually more efficient a lot of the time to avoid closing
down a collection immediately and instead have it ready for the next
request. The opening and closing of connections can be expensive so the
algorithms attempt to optimize this.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage
 
That sounds like a lot of connections for a small ammount of users...
The .NET CLR performance counters are not something that you can rely on,
unfortunately they accumulate due to a bug that we just can't fix. Can you
verify the number of connections on the Server? (using the server side
performance counters or the appropriate stored procedures)

What I would be most scared of is connections being leaked, but you
specified that you are explicitly closing the connection. I assume that you
are using the "using" statement or a "finally" clause right? otherwise leaks
may still happen.

Would it be possible to post the connection string keywords you are using
(no database/password of course).
Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/
 
Back
Top