index > .NET Framework Data Access and Storage > Connection Timeout

Connection Timeout

I have a problem with Connection Pool running out of connections. I have a site which can get upwords of 1000 conncurrent hits at once. We never had problems with the connections when using classic ASP. But now we are running into the issue with timing out before getting an available connection. We are closing all of the connections. We have bumped up the max pool size to 700 but i don't feel this is really the right solution. Should we not use ADO.NET and go back to using ODBC as stated before we don't have this problem with our classic ASP which uses ODBC connections.

Thanks for any help!

AlexStorm

Do you only close connection or call Dispose method as well? Calling dispose could release connections faster. When do you close connection? Is it as soon as you done the job with the data? I would suggest to trace the SQL Server usign SQL Profiler to see if you do not have any connection leaks when they are stayd opened because application did not close it for some reason. I know applications which work with about 5000 concurrent users usign ADO.NET and it works fine.

Val Mazur (MVP) http://xport.mvps.org

VMazur

Here is a sample method that I am using. I do call close not Dispose.
On the front end once the page is completed process in the page's dispose method I then dispose of the dataset (i know this isn't really necessary but I do it as a matter of 'clean up' anyway) I call only a single stored procedure to get all of the information to build the page (the whole page is built dynamically) No single stored procedure takes more than a fraction of a second to return. Thanks again for any help with this problem. We use the Data Application Block and I am currently moving to use the 2.0 release for ADO.NET 2.0 from January 2006.

SqlConnection sConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ToString());
DataSet da = new DataSet();
try
{
SqlParameter[] projectParams = new SqlParameter[3];
projectParams[0] = new SqlParameter("@SessionID", SqlDbType.VarChar, 50);
projectParams[0].Value = SessionID;
projectParams[1] = new SqlParameter("@LangID", SqlDbType.VarChar, 10);
projectParams[1].Value = LangID;
projectParams[2] = new SqlParameter("@PNumber", SqlDbType.Int);
projectParams[2].Value = PageNum;
da = SqlHelper.ExecuteDataset(sConn, "sp_GetCoreQuestions", projectParams);
}
catch
{
throw;
}
finally
{
if (sConn.State == ConnectionState.Open)
{
sConn.Close();
}
}
return da;

AlexStorm

This pattern will leak connections if an exception is thrown. Otherwise, it looks like it should be working, depending on what happens in your ExecuteDataset method. There is currently one known connection leak in ADO.Net involving System.Transaction interaction, but it's somewhat obscure.

Try removing the try-catch block and use the "using" paradigm instead:

using (SqlConnection sConn = new SqlConnection(...)) {
sConn.Open();
DataSet da = new DataSet();
...
da = SqlHelper.ExcuteDataset(sConn, "sp_GetCoreQuestions", projectParams);
}

If that doesn't help, we'll need to dig deeper into what SqlHelper.ExecuteDataset is doing.

Alazel Acheson / ADO.Net Developer / Microsoft Corp.

alazela

I have made the recommended changes. We are not using transactions and the SqlHelper class is the Microsoft Application Block v2. The only exceptions that have happened are those that are thrown because a connection can not be opened. I don't believe that I am leaking any connections. - here is what seems to be happening-- we have a survey that goes out and the link is emailed to 3000 or so people. So even if a portion open the email immediately I will easily max out the 100 default connections if only 150 of those 3000 click the link at the same time I will receive 50 error email.
There I am just not certain how to manage this problem as it happens at the surge of a survey and then becomes managable. As I am creating only a single connection per page I really need to find a way to manage the connections. Is there a way to dynamically expand the pool size if it becomes required?

Thanks for any help.

AlexStorm

If you actually have the potential for a large number of concurrent connections, I recommend going ahead and bumping up the max connection limit. ADO.Net will automatically manage the pool so you aren't maintaining that many actual connections unless they are needed. This assumes your server can handle that number of connections with reasonable performance, of course.

If you would prefer to limit the maximum number of connections, but avoid the timeout issue, you can increase the connection timeout instead.

Alazel Acheson / ADO.Net Developer / Microsoft Corp.

alazela

If the finally block is guaranteed to be executed how can the pattern used leak?

Software Developer

Reza Mirkhani

I believe my earlier comment about leaking connections was due to mis-reading the code and assuming the connection was opened outside the try-catch block. There have been as obscure case or two in v1.x where an exception thrown at just the point in the code will have it's state set to something other than Open, yet still need to have Close called to release the inner connection to the pool, but this case isn't very likely. For v2.0, this isn't really an issue. In any case, it doesn't hurt to call Close on the connection a second time, so I would recommend taking out the comparison from the finally and just call Close() in all cases.

Alazel Acheson / ADO.Net Developer / Microsoft Corp.

alazela

reply 8