Monitoring connection pooling between .NET 1.1 & SQL Server 2000

[Mark Rae]

Hi,

According to an MSKB article
(http://support.microsoft.com/default.aspx?scid=kb;en-us;314429), there is a
bug in PerfMon whereby certain counters are not reset correctly.

I need to stress test an ASP.NET solution with specific respect to
performance and connection pooling.

The client is pretty certain that all SqlConnection objects are being closed
and disposed correctly - and, looking at the code, this certainly appears to
be the case - but is experiencing lots of the dreaded "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." errors.

Strangely, though, even with the reported bug in PerfMon, this appears to be
happening way before the max pool size (100) is reached - typically around
50-60 pooled connections...

Can anyone recommend a good utility for monitoring this instead of PerfMon?

Any assistance gratefully received.

Mark

 

[William \(Bill\) Vaughn]

We have discussed this any number of times so I would search google groups
for some of the archived answers. Here is one that might help:
http://groups-beta.google.com/group...william+(Bill)"&rnum=4&hl=en#59db889f3d831f8b

All too often we hear about connection pool troubleshooting that focuses
entirely on closing connections, but not on performance issues. I expect
that the performance problem is the most likely issue if the connections are
getting closed in your code. In a heavily loaded system, leaving a
connection open will cause the system to fail at the 100th user. Leaving two
connections open (in the same pool) would fail on the 50th user. However, if
your heavily loaded site seems to support more than 100 users, you're
probably not leaving a connection open--you might simply be running out of
throughput horsepower to deal with the load.
Since you're accessing SQL Server, you can see the number of connections in
use by checking the (reliable) SQL Server performance counters (general
statistics|user connections). This is an accurate, reliable count of the
number of open connections. No, it's not broken down by pool, but SP_WHO2
will tell you that (it will show the application owner).

See www.betav.com for a whitepaper on the connection pool.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

 

[Mark Rae]

Thanks very much.