Connection Pooling Problem

[Elroyskimms]

I'm using ASP.Net connecting to MSSQL 2000. I understand that open
connections need to be closed (dbconnection.close) before they can be
returned to the connection pool and re-used. I also understand that if
10 connections are made, each using the exact same connection string,
they will all share the same connection pool, grabbing old connections
if they are available or creating new ones in that pool if there is
nothing available.

All of my connections use the exact same connection string. How do I
know this? Because I have a custom class which has a string called
"ConnectionString" and all of my database connections use it. So, in
theory, I should only have one connection pool showing up in Perf Mon
(SQLClient: Current # Connection Pools) but right now I have 37. Can
anyone help me out with that?

Sample code:

Function qryGetInvoiceTerms() As System.Data.IDataReader
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(sys.ConnectionString)

Dim queryString As String = "SELECT [tblInvoicePaymentTerms].*
FROM [tblInvoicePaymentTerms]"
Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open()
Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

1) sys.connectionstring is the connection string I have stored in a
separate class

2) Most people freak when they see that I am passing around an open
datareader, but I really do close each one as I go. But even if I
missed one, that shouldn't be causing me to have 37 connection pools...
or should it?

 

[PL]

I'm no expert in this field and it certainly seems to me this part needs some
tweaking in the next release because a lot of people are having issues with
the connection pool overflowing.

Anyway, you can have more than one connection in the pool for the simple
reason that if one connection is already being used it needs to open a new one,
so you can actually have a large number of connections in the pool at any given time.

Another issue you could possibly have is that with your code you do not close
the connection if an exception occurs, you should always close the connection
in a finally clause to make sure it's closed no matter what happens.

PL.

 

[Elroyskimms]

Thanks for your response, and I agree with all that you said. I do use
reader.close in my Finally sections. The issue I'm having is not that I
have a lot of pooled connections (which would indicate the system is
using connections faster than it can return them OR I am not closing
old connections)... My problem is that I am showing 37 connection
pools. This should only be the case if I have 37 different connection
strings active, but I only have one. Any thoughts on that?

 

[Angel Saenz-Badillos[MS]]

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314429

Unfortunately performance counters accumulate in both RTM and Whidbey. This
is not something that we were able to fix at that time and it makes the
performance counters fairly unusable. We have fixed the counters in the
whidbey release by making some fairly drastic changes, you can see info on
the new counters here:
http://blogs.msdn.com/angelsb/archive/2004/08/02/206470.aspx

Hope this helps,
--
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/