Connection Pooling problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We've been seeing a number of problems regarding connection pooling in an
application we've written in -house here -- we're getting the dreaded:
"The timeout period elapsed prior to obtaining a connection from the pool"
(connecting to SQL Server 2005, from 1.1 Framework application)

We've checked all the obvious things --
* checked that every new SqlConnection() is in a try...finally block that
will dispose the SqlConnection;
* Checked that SQL Debugging is disabled

We even ran a trace on the Server to track down every call made by a single
client from application start to error -- this did highlight that we were
only ever using a handful of different SPIDs on the server (max of 7 per
connection string) -- as this is significantly lower than the expected 100
(or so).

I'm at a bit of a loss as to what else to look at? We explicitly call
Dispose in all cases.

We're getting a little suspicious of the Server, as we've also seen separate
cases of simple queries timing out when made through a .Net application that
return instantaneously through Query Analyser or SQL Server Management Studio
 
Rowland Shaw said:
We've been seeing a number of problems regarding connection pooling in an
application we've written in -house here -- we're getting the dreaded:
"The timeout period elapsed prior to obtaining a connection from the pool"
(connecting to SQL Server 2005, from 1.1 Framework application)

We've checked all the obvious things --
* checked that every new SqlConnection() is in a try...finally block that
will dispose the SqlConnection;
* Checked that SQL Debugging is disabled

We even ran a trace on the Server to track down every call made by a single
client from application start to error -- this did highlight that we were
only ever using a handful of different SPIDs on the server (max of 7 per
connection string) -- as this is significantly lower than the expected 100
(or so).

I'm at a bit of a loss as to what else to look at? We explicitly call
Dispose in all cases.

We're getting a little suspicious of the Server, as we've also seen separate
cases of simple queries timing out when made through a .Net application that
return instantaneously through Query Analyser or SQL Server Management Studio
Click to expand...
 
Hi,

I've got the same problem with our own product, a server having hundreds of
clients and DB access were critical. After a lot of different designs, I
finally go through this problem adding a personal connection pool layer :

public class CnxPool
{
int MaxAdoCnx, CurrentCnx;
string DefCnxString;

public CnxPool(int maxPool)
{
CurrentCnx = 0;
MaxAdoCnx = maxPool;
DefCnxString = MccADO.CnxStr+String.Format(";Connect TimeOut =
1;Connection Lifetime=0;Max Pool Size={0};Min Pool Size = {0}", new object[]
{MaxAdoCnx.ToString()});
}

public CnxPool():this(50)
{
}

protected SqlConnection InternalGetCnx()
{
while (MaxAdoCnx<=CurrentCnx)
Thread.Sleep(10);
lock(this)
{
SqlConnection cnx;
try
{
cnx = new SqlConnection(DefCnxString);
cnx.Open();
}
catch(Exception)
{
cnx = null;
}
if (cnx!=null)
CurrentCnx++;

return cnx;

}
}

public SqlConnection GetCnx()
{
SqlConnection cnx;
while ((cnx = InternalGetCnx())==null)
Thread.Sleep(10);
return cnx;
}

public void ReleaseCnx(SqlConnection cnx)
{
lock(this)
{
try
{
if (cnx!=null)
CurrentCnx--;
cnx.Close();
cnx = null;
}
catch(Exception e)
{
}
}
}
}


To explain my expectation regarding this code : I want to be sure I get back
an available connection(that will not wait in .Net pool and return a time
out) when my cnx is return from this pool.
You can see in the constructor that I'm building (it would have been better
using stringbuilder !) a default connectionstring. It will be used for every
SQLConnection creation. I add a few information on our default connection
string (I access to the default connection string with MccADO.cnxstr, usually
used for backoffice executables, they need only 1 or 2 connections - you only
need to change it and you will obtain a working pool)

To use it properly, you have to create the CnxPool with enough connection.
The best for you will to do some tests regarding your domain of activity,
maximum number of clients....
Then just call :
SqlConnection cnx = pool.GetCnx(); // to obtain your cnx
try
{
//place your code here
}
finally
{
pool.ReleaseCnx(cnx);
}


Hope it will help...
 
Hi,

Is this a windows form app or ASP.NET app? Could you post some of your code
that opens and closes the connection here?

On SQL Server 2005, you can also check how many active connections there
are, to see if the the connection has exceed limits on the server. By the
way, what's the edition of your SQL server 2005? Enterprise or Developer?

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Hi Kevin,

This is a WinForms app that we're seeing problems with, connecting to a SQL
2005 Server (Enterprise edition)

Typical code that we have for connecting to the db would be:

Public Sub Load(ByVal id As Integer)

Dim cmd As SqlCommand = Nothing
Dim con As SqlConnection = Nothing
Dim dr As IDataReader = Nothing

Try
con = New SqlConnection(DATABASE_CONNECTION_STRING)
con.Open()

' Run SP with params
cmd = New SqlCommand("dbo.usp_Thing_Select", con)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@Id", SqlDbType.Int)
cmd.Parameters("@Id").Value = id

' Use result to build up collection
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection Or
CommandBehavior.SingleResult)

While (dr.Read())
' Construction Logic
_id = CType(dr("Id"), Integer)
' snip for brevity
End While
dr.Close()
Finally
If Not (dr Is Nothing) Then
dr.Dispose()
dr = Nothing
End If

If Not (cmd Is Nothing) Then
cmd.Dispose()
cmd = Nothing
End If

If Not (con Is Nothing) Then
con.Dispose()
con = Nothing
End If
End Try

End Sub
 
Your connection string would help

Rowland Shaw said:
Hi Kevin,

This is a WinForms app that we're seeing problems with, connecting to a
SQL
2005 Server (Enterprise edition)

Typical code that we have for connecting to the db would be:

Public Sub Load(ByVal id As Integer)

Dim cmd As SqlCommand = Nothing
Dim con As SqlConnection = Nothing
Dim dr As IDataReader = Nothing

Try
con = New SqlConnection(DATABASE_CONNECTION_STRING)
con.Open()

' Run SP with params
cmd = New SqlCommand("dbo.usp_Thing_Select", con)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@Id", SqlDbType.Int)
cmd.Parameters("@Id").Value = id

' Use result to build up collection
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection Or
CommandBehavior.SingleResult)

While (dr.Read())
' Construction Logic
_id = CType(dr("Id"), Integer)
' snip for brevity
End While
dr.Close()
Finally
If Not (dr Is Nothing) Then
dr.Dispose()
dr = Nothing
End If

If Not (cmd Is Nothing) Then
cmd.Dispose()
cmd = Nothing
End If

If Not (con Is Nothing) Then
con.Dispose()
con = Nothing
End If
End Try

End Sub
Click to expand...
 
Hi,

1. Please use SqlDataReader.GetInt32 to get the integer value.
2. The other part of code is fine. When the connection cannot be obtained
from a connection pool, how many users are connecting to the database? Is
this error from a stress test? Could you show me your connection string?

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Embaressingly, I've managed to track the problem down -- the application
connects to two database servers.

The symptoms suggested one database, whilst the leaking connection was in a
component that had been brought in that connected to the other database.

Still, we've now resolved the issue...
 
It was nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.


Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Back
Top