Connection pool count too high?

[Guest]

I am trying to determine if the # of connection pools our app is using is
'normal'/acceptable, or if there is a problem.

Our web application is all asp.net, using ADO.NET and SQL Server 2000 STD.
The app is run on a Windows 2003 Server Web Edition and IIS 6. The
application is broken up into 2 webs in IIS. One that contains only one
Application Virtual Directory, and the other that is broken up into 7
Application Virtual Directories.

Every Sql Command uses the same connection string. All data access opens and
closes its own connection immediately before and after the command is run.

We normally have about 45 - 60 concurrent users of the app at any given
time. And every page runs several sql commands every time they load.

Below are the performance counters from both the web and sql servers that I
thought were important for this research.

142 connection pools and 429 pooled connections seems very high to me. I
know that it is hard to determine what should be 'normal' without knowing
more about the app, but at a very high level, does this seem normal or would
this indicate there is a problem?


Connection string
-----------------
"User ID=<uname>;Password=<pword>;Initial Catalog=<dbname>;Data Source=<ip>;"


Web Server Perf Counters
------------------------
..NET CLR DATA _global_
SqlClient Current # connection pools: 142
SqlClient Current # pooled and nonpooled connections: 429
SqlClient Current # pooled connections: 429

Web Service
Current Connections: 46
Maximum Connections: 158
Maximum Anonymous Users: 91
Get Request/ sec: <10 - 20 avg>

SQL Server Perf Counters

 

[Norman Yuan]

Connections stay in the same pool only if the ConnectionString are exactly
the same: the same password, the same user name...

 

[Guest]

Sorry for the bluntness, but that doesn't even really relate to my question.

 

[William \(Bill\) Vaughn]

I've written about this many times so search the archives for more details.
Yes, the counts do look high. Each of these numbers can be evaluated in a
number of ways. If the number of pools looks high (and it does) I would tend
to think that the conditions that require a new pool are changing. It's not
just the connection string that determines if a new pool is needed. It's
also the transaction enlistment and the process ID. Each application domain
(app domain) gets its own pool, but within that scope each transaction gets
a new pool. These pools are not destroyed until they are empty for some time
and the app domain is gone.
If the number of concurrent users exceeds the number of pooled connections,
then yes, I expect there is a leak or your system is unable to keep up with
the load. As a new user arrives, if the system is not finished with the
previous user a new connection has to be created to deal with it. The
additional load (of the subsequent user) exacerbates the problem. This is
not an issue of closing connections in code, it's not closing the connection
in time--before it's needed again. Make sense?

I'm giving an ADO.NET workshop in Orlando (Oct 13th) and in Sydney the week
of the 28th of November. I'll discuss these issues there.

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

 

[Guest]

Thanks for your response, now I know where to start looking for problems.

I will be at VSlive Orlando and would love to hear you speak on this topic.
What session will cover this?

 

[William \(Bill\) Vaughn]

I'm doing a "Hitchhikers' Guide to Connecting" (extracted from my book) that
covers it. So does the post-con ADO.NET best practices workshop. Be sure to
tell me you're there (and sit up front so you can see the demos).

--
____________________________________
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.
__________________________________