SQLConnection and Pooling

[Farid]

Hi All,

I'm currently working on my first major project using ASP.NET using VB.
Everything seemed fine when i was initially testing but now I'm running into
issues with too many connections being created and taking over the pool.

I've gone through the entire application and verified that I'm calling a
Conn.Close and a Conn.Dispose anywhere that I create and use a SQLConnection
object. However, i'm still running out of connections. I'm the only one in
the application at this time so I figured that the 100 connection pool limit
would suffice. I guess I was wrong!

It's almost as if pooling isn't working. I've read all of the stuff about
the connection string being constant (and it is). All of the connections
share the same connection string (listed below). Since I haven't specified
any values, all pooling parameters are using default values (per Microsoft
documentation). Therefore, my connections should be reused when I execute
this page. .


By executing the same page multiple times I see my the number of my
connections increase by a count of 3 for each iteration. My questions are as
follows:

1. Does my connection string matter in its current format - are there any
parameters that i'm missing that will assist in getting the pool to work
efficiently?

2. I'm using the Microsoft Data Access Application block version 2.0 to save
some coding time. I've looked through the code and it seems to be disposing
of the conneciton objects appropriately -- Nothing is really left hanging
there. Has anyone else had a problem with these before?

I've attached a copy of the code which I believe is a problem.-- why would
this code create 3 connections for every call?

Thanks for all of your help.

Farid

'/****** CODE *******/
ConnString = "data source=MyServer;Trusted_Connection=no;Initial
Catalog=MyDB;User ID=MyUser;Password=MyPwd;"


Private Sub PopulateGrid()

Dim conn As New
SqlConnection(ConfigurationSettings.AppSettings("DBConnString").ToString)
Dim sh As SqlHelper
Dim ds As New DataSet


Try

PrimarySQL = DirectCast(Session("PrimarySQL"), String)
conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds)
conn.Close()

If ds.Tables(0).Rows.Count <= 0 Then
lblNoRecs.Visible = True
RowCount.Visible = False
DataGrid1.Visible = False
Exit Sub
Else
lblNoRecs.Visible = False
End If

SetRowCount(ds.Tables(0).Rows.Count)

DataGrid1.DataSource = ds
DataGrid1.DataBind()

ds = Nothing
sh = Nothing
Finally
conn.Close()
conn.Dispose()
End Try

 

[David Browne]

Hi All,

I'm currently working on my first major project using ASP.NET using VB.
Everything seemed fine when i was initially testing but now I'm running into
issues with too many connections being created and taking over the pool.

I've gone through the entire application and verified that I'm calling a
Conn.Close and a Conn.Dispose anywhere that I create and use a SQLConnection
object. However, i'm still running out of connections. I'm the only one in
the application at this time so I figured that the 100 connection pool limit
would suffice. I guess I was wrong!

It's almost as if pooling isn't working. I've read all of the stuff about
the connection string being constant (and it is). All of the connections
share the same connection string (listed below). Since I haven't specified
any values, all pooling parameters are using default values (per Microsoft
documentation). Therefore, my connections should be reused when I execute
this page. .


By executing the same page multiple times I see my the number of my
connections increase by a count of 3 for each iteration. My questions are as
follows:

1. Does my connection string matter in its current format - are there any
parameters that i'm missing that will assist in getting the pool to work
efficiently?

You don't have do do anything special.

2. I'm using the Microsoft Data Access Application block version 2.0 to save
some coding time. I've looked through the code and it seems to be disposing
of the conneciton objects appropriately -- Nothing is really left hanging
there. Has anyone else had a problem with these before?

I've attached a copy of the code which I believe is a problem.-- why would
this code create 3 connections for every call?

That code is fine. It will not leak connections.

You are probably leaking connections somewhere else.

You can find out where by attaching a finalizable event target to your
connection.
Here's a C# example using Oracle.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]

And here's a VB example using Sql Server.

Imports System.Data.SqlClient
Imports System.Diagnostics

Public Class ConnectionSpy
Private conn As SqlConnection
Private st As StackTrace

Public Shared Function GetConnection(ByVal constr As String) As
SqlConnection
Dim conn As New SqlConnection(constr)
conn.Open()
Dim spy As New ConnectionSpy(conn)
Return conn
End Function

Public Shared Sub Main(ByVal args() As String)
Dim constr As String = "data source=mydb;Trusted_Connection=no;Initial
Catalog=pubs;User ID=myuser;Password=mypassword;"
Dim con As SqlConnection = ConnectionSpy.GetConnection(constr)
Trace.Listeners.Add(New TextWriterTraceListener(Console.Out))

con = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
Console.WriteLine("Hit Enter to Exit")
Console.ReadLine()

End Sub


Public Sub New(ByVal conn As SqlConnection)
Me.conn = conn
Me.st = New StackTrace(True)


AddHandler conn.StateChange, AddressOf conn_StateChange
End Sub
Private Sub conn_StateChange(ByVal sender As Object, ByVal e As
System.Data.StateChangeEventArgs)

If e.CurrentState = ConnectionState.Closed Then
'detach the spy object and let it float away into space
'if the connection and the spy are already in the FReachable(Queue)
'GC.SuppressFinalize doesn't do anyting.

GC.SuppressFinalize(Me)
RemoveHandler conn.StateChange, AddressOf conn_StateChange
conn = Nothing
st = Nothing
End If

End Sub
Protected Overrides Sub Finalize()
'if we got here then the connection was not closed.
'dump the stack trace
Trace.WriteLine("WARNING: Open Connection is being Garbage Collected")
Trace.WriteLine("The connection was initially opened " & st.ToString())

End Sub
End Class

 

[Angel Saenz-Badillos[MS]]

I disagree, the code is not fine, connection close is not guaranteed.

conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection will
leak!
conn.Close() //never gets called if fill throws.

Your best bet to fix this code is to never open the connection, the adapter
is smart enough to realize the connection is closed and opent the connection
for you, it will then guarantee that connection close gets called. If you
are not using an Adapter your best bet is to guarantee that the connection
Close gets called by placing it inside of a Finally block, in your case it
would look like:

try{
conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection will
NOT leak!
}finally{
conn.Close() //guaranteed to be called
}

Now even if fill throws an exception we will call Close and you will not
leak. Keep an eye on my blog, my latest entry has some information on this
and I am working on a full article coming in the next week or so.

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/

 

[David Browne]

I disagree, the code is not fine, connection close is not guaranteed.

conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection will
leak!
conn.Close() //never gets called if fill throws.

Angel,

There is an enclosing try block.

.. . .
Finally
conn.Close()
conn.Dispose()
End Try

The code is fine.


David

 

[Angel Saenz-Badillos[MS]]

I completely missed it, the first Close call threw me off track.
thanks for pointing it out David.

--
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/

 

[Guest]

Thank you both for the posts.

Angel: The extra .close was an error on my part which I had corrected after
posting. Sorry, I was falling asleep on my PC.

Dave: Thanks for the ConnectionFactory class. I'll see what I can do with
it. I'm not sure if it will assist with my asp.net app.

Anyway, I've currently disabled connection pooling altogether. I didn't have
the time to mess with it - as this is a production app. I've placed all of my
close/dispose/conn = nothing calls into the finally block as Angel would have
expected but I was still getting pool errors. So I opted for no pool.

I was looking at the pool counters in PerfMon and noticed that in certain
instances clicking a button that was creating and using ONLY ONE connection
object was actually creating 2 in the counters. Is it me? Maybe not.

Additionally,

I'm at a loss - I figured connection pooling would be a good way to go for
performance but it didn't seem to make a difference since i've disabled it.
My server is fairly capable (Dual Processor 3GHz P4 with 4GB RAM) so i doubt
that the objects are going to have a major effect on the performance.

I was wondering if the Microsoft Application Data Blocks assembly (SQLHelper
class) would be causing my grief but after looking at the code it doesn't
seem to be the case. I know, I know I could have written something and would
have known it would work but I was on a tight deadline and this is my first
stab at .NET.

Would it be a REALLY bad thing to pass a connection object around ByRef?

I completely missed it, the first Close call threw me off track.
thanks for pointing it out David.

--
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/

Angel,

There is an enclosing try block.

. . .
Finally
conn.Close()
conn.Dispose()
End Try

The code is fine.


David

 

[Angel Saenz-Badillos[MS]]

There is no real need to take out the extra close since calling close twice
will have no effect, it just jumped out at me while reading the code and I
did not realize that you had the whole thing in a try finally block. I am
concerned that the solution has ended up being turning pooling off,
especially for an ASP.NET application...

--
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/

Thank you both for the posts.

Angel: The extra .close was an error on my part which I had corrected after
posting. Sorry, I was falling asleep on my PC.

Dave: Thanks for the ConnectionFactory class. I'll see what I can do with
it. I'm not sure if it will assist with my asp.net app.

Anyway, I've currently disabled connection pooling altogether. I didn't have
the time to mess with it - as this is a production app. I've placed all of my
close/dispose/conn = nothing calls into the finally block as Angel would have
expected but I was still getting pool errors. So I opted for no pool.

I was looking at the pool counters in PerfMon and noticed that in certain
instances clicking a button that was creating and using ONLY ONE connection
object was actually creating 2 in the counters. Is it me? Maybe not.

Additionally,

I'm at a loss - I figured connection pooling would be a good way to go for
performance but it didn't seem to make a difference since i've disabled it.
My server is fairly capable (Dual Processor 3GHz P4 with 4GB RAM) so i doubt
that the objects are going to have a major effect on the performance.

I was wondering if the Microsoft Application Data Blocks assembly (SQLHelper
class) would be causing my grief but after looking at the code it doesn't
seem to be the case. I know, I know I could have written something and would
have known it would work but I was on a tight deadline and this is my first
stab at .NET.

Would it be a REALLY bad thing to pass a connection object around ByRef?

I completely missed it, the first Close call threw me off track.
thanks for pointing it out David.

--
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/

I disagree, the code is not fine, connection close is not guaranteed.

conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection
will
leak!
conn.Close() //never gets called if fill throws.


Angel,

There is an enclosing try block.

. . .
Finally
conn.Close()
conn.Dispose()
End Try

The code is fine.


David

 

[Guest]

It concerns me too, considering I've done all that I know of, albeit I know
only so much right now, in order to correct this problem. I'm still kind of
green when it comes to the ins-and-outs of .NET so I might be something i've
introduced elsewhere - although I doubt it due to the simplicity of the app
and the actual process of creating/"destroying" connections.

What other options would you recommend?

One last thing - how many connections in a pool is too many? 100, 500, 1000?
If I set the Max value high enough will it settle the issue or am I opening a
can of whoop-ass onto the web and DB servers with way too much overhead?

Thanks again.

Farid

There is no real need to take out the extra close since calling close twice
will have no effect, it just jumped out at me while reading the code and I
did not realize that you had the whole thing in a try finally block. I am
concerned that the solution has ended up being turning pooling off,
especially for an ASP.NET application...

--
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/

Thank you both for the posts.

Angel: The extra .close was an error on my part which I had corrected after
posting. Sorry, I was falling asleep on my PC.

Dave: Thanks for the ConnectionFactory class. I'll see what I can do with
it. I'm not sure if it will assist with my asp.net app.

Anyway, I've currently disabled connection pooling altogether. I didn't have
the time to mess with it - as this is a production app. I've placed all of my
close/dispose/conn = nothing calls into the finally block as Angel would have
expected but I was still getting pool errors. So I opted for no pool.

I was looking at the pool counters in PerfMon and noticed that in certain
instances clicking a button that was creating and using ONLY ONE connection
object was actually creating 2 in the counters. Is it me? Maybe not.

Additionally,

I'm at a loss - I figured connection pooling would be a good way to go for
performance but it didn't seem to make a difference since i've disabled it.
My server is fairly capable (Dual Processor 3GHz P4 with 4GB RAM) so i doubt
that the objects are going to have a major effect on the performance.

I was wondering if the Microsoft Application Data Blocks assembly (SQLHelper
class) would be causing my grief but after looking at the code it doesn't
seem to be the case. I know, I know I could have written something and would
have known it would work but I was on a tight deadline and this is my first
stab at .NET.

Would it be a REALLY bad thing to pass a connection object around ByRef?

I completely missed it, the first Close call threw me off track.
thanks for pointing it out David.

--
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/




message
I disagree, the code is not fine, connection close is not guaranteed.

conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection
will
leak!
conn.Close() //never gets called if fill throws.


Angel,

There is an enclosing try block.

. . .
Finally
conn.Close()
conn.Dispose()
End Try

The code is fine.


David

 

[Angel Saenz-Badillos[MS]]

It really depends on the application, for most asp.net applications 100
connections is a very large number. There is one thing that I should have
asked before, how are you determining that you have too many connections? I
guess I just assumed that you had the dreaded client side "Timeout Expired.
The timeout period elapsed prior to obtaining a connection from the pool"
Please let me know if this is not the case.

You should not use the client side performance counters, they accumulate and
are practically worthless. I would rely on the server side counters and
sp_who to track connections and try to narrow down where connections are
getting used.

--
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/

It concerns me too, considering I've done all that I know of, albeit I know
only so much right now, in order to correct this problem. I'm still kind of
green when it comes to the ins-and-outs of .NET so I might be something i've
introduced elsewhere - although I doubt it due to the simplicity of the app
and the actual process of creating/"destroying" connections.

What other options would you recommend?

One last thing - how many connections in a pool is too many? 100, 500, 1000?
If I set the Max value high enough will it settle the issue or am I opening a
can of whoop-ass onto the web and DB servers with way too much overhead?

Thanks again.

Farid

There is no real need to take out the extra close since calling close twice
will have no effect, it just jumped out at me while reading the code and I
did not realize that you had the whole thing in a try finally block. I am
concerned that the solution has ended up being turning pooling off,
especially for an ASP.NET application...

--
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/

Thank you both for the posts.

Angel: The extra .close was an error on my part which I had corrected after
posting. Sorry, I was falling asleep on my PC.

Dave: Thanks for the ConnectionFactory class. I'll see what I can do with
it. I'm not sure if it will assist with my asp.net app.

Anyway, I've currently disabled connection pooling altogether. I

didn't
have

the time to mess with it - as this is a production app. I've placed

all of
my

close/dispose/conn = nothing calls into the finally block as Angel

would
have

expected but I was still getting pool errors. So I opted for no pool.

I was looking at the pool counters in PerfMon and noticed that in certain
instances clicking a button that was creating and using ONLY ONE connection
object was actually creating 2 in the counters. Is it me? Maybe not.

Additionally,

I'm at a loss - I figured connection pooling would be a good way to go for
performance but it didn't seem to make a difference since i've

disabled
it.

My server is fairly capable (Dual Processor 3GHz P4 with 4GB RAM) so i doubt
that the objects are going to have a major effect on the performance.

I was wondering if the Microsoft Application Data Blocks assembly (SQLHelper
class) would be causing my grief but after looking at the code it doesn't
seem to be the case. I know, I know I could have written something and would
have known it would work but I was on a tight deadline and this is my first
stab at .NET.

Would it be a REALLY bad thing to pass a connection object around

ByRef?

:

I completely missed it, the first Close call threw me off track.
thanks for pointing it out David.

--
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/




message
I disagree, the code is not fine, connection close is not guaranteed.

conn.Open()
Dim da As New SqlDataAdapter(PrimarySQL, conn)
da.Fill(ds) //<- If this throws an exception the connection
will
leak!
conn.Close() //never gets called if fill throws.


Angel,

There is an enclosing try block.

. . .
Finally
conn.Close()
conn.Dispose()
End Try

The code is fine.


David