Sql – Increasing the Max Pool Size and performance

asp.netconnection-poolingsqlsql server

In my ASP.net website I have a connection to a SQL Server Express database. And sometimes I do get lot of errors like

System.InvalidOperationException: Timeout expired. 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.

After searching for the error I found that it may be due to unclosed SQL Server connections. But I have used SQL Server connections properly and I have disposed it properly.I have used using-statement to dispose the connection. In my application I get a lot of requests (connections to SQL Server database) at some peak time of the day.

So I am planning to increase the max pool size. Now I have not specified it in my connection string. So it will be 100 (default). I am planning it to increase to 20000 so that I won't get the error.

So does increasing to the max pool size to this number will cause any problem? Does increasing the max pool will cause any performance problem?

EDIT:
Below is a sample of how i used Using in my code

Using con1 As New SqlConnection
    con1.ConnectionString = "" //conn string here
    con1.Open()
    doSomething()      
End Using 

Using con2 As New SqlConnection
                con2.ConnectionString = "" //conn string here
                con2.Open()
                doSomething()
            Using con3 As New SqlConnection
               con3.ConnectionString = "" //conn string here
               con3.Open()
               doSomething()       
            End Using

End Using 

Catch ex As Exception

End Try

EDIT:
I just tested by setting Max pool size to 20000 in my application to check if max pool is really causing the problem.I was expecting to not get this exception any more. But it didn't solve the problem or may not be the reason of the error. After setting max pool also i got above error about 50 times in a duration of 3 hours. So I doubt is it really due to pool size? The error message states that "This may have occurred because all pooled connections were in use and max pool size was reached". So is there any other case where the same error message shown? Please help.

Best Answer

You can increase the pool size if you want. There are two downsides:

More connections mean more resource usage.
SQL Server has a connection limit of about 30k connections. When you exhaust it you will lose availability.

I recommend going higher more slowly. Don't increase the pool size to 20k immediately. Think about what 20k connections would mean: It would mean roughly 20k threads (or less if a single thread uses more than one connection) executing at the same time. If you are in that spot you probably have load problems.

Why do you even need that many connections? For web apps, you usually have only one connection open at the same time per running request. A few hundred should be enough for most cases.

Try to shorten the duration that each connection must be open.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

.net – How to solve a connection pool problem between ASP.NET and SQL Server

In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);

connection.Open();
// some code
connection.Close();

The correct way would be this:

var connection = new SqlConnection(ConnectionString);

try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());

result = command.ExecuteNonQuery();
connection().Close();

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

.net – How to solve a connection pool problem between ASP.NET and SQL Server

Related Topic