You may continuously experience the following error with your application;
Unhandled Exception: 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.
You may rarely see sudden crash in your application. You may see that SQL Server suddenly hangs forever until you restart the service. There could be many reasons for this and one of the reasons could be related to the Connection Pool.
What is Connection Pooling? How does it work and what limitations are there with it? How can I check whether there are pooled connections? These are the things you should know if you experience an issue related to it.
Let's understand Connection Pooling
Establishing a connection goes through several steps that need resources and has a cost. If an application continuously uses the SQL Server Database by opening and closing the connection, it is better to keep the connection without discarding and reuse when required without re-creating for each an every call. This is purely for improving the performance. When the application first time tries to make the connection, it creates the Connection Pool. Then the connection is added to the pool and once the connection is closed, it will be kept for a certain time period for reusing for new requests come for the same Connection Pool. This connection is called as Pooled Connection. If the Pooled Connection is being used and a new request is received for the same Connection Pool, then a new Pooled Connection is created in the same pool and used. Connection Pool is based on several keys in connection string and they are used for identifying the right Connection Pool to get a connection. If the request comes from a different connection string, it creates a new Connection Pool and maintains Pooled Connection in it.
By default, maximum number of Pooled Connections can be maintained in a Connection Pool is 100. If there are 100 Pooled Connections in the pool and all are being used, and another request comes for the same pool, you will experience one of above mentioned issue.
How can I see the number of Pooled Connections in Connection Pool?
There are multiple ways of seeing this. What I use is a simple TSQL as below;
xxx
This gives you connections and number of Pooled Connections (NumberOfConnections). There is no specific threshold for determining whether the number shows is good or bad. However, if you see ALWAYS see a number above 50, that might indicate an issue.
Why I see a higher number of Pooled Connections?
There can be two main reasons for seeing a larger number. One is, obviously, if you have many number of concurrent users working with your database using the same connection string. Second is, your application does not close the connection used and opens again. It will add a new Pooled Connection to the pool. Remember, opened connections cannot be used until they are closed (There can be exceptions, see the below example).
See the below C#.Net code;
int x = 1;
SqlConnection con;
SqlCommand cmd;
string sql;
while (x < 1000)
{
con = new SqlConnection("Server=.;Database=AdventureWorks2014;Integrated Security=SSPI;");
con.Open();
sql = "BEGIN TRAN; SELECT * FROM Production.Product WHERE ProductID = " + x.ToString();
cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
Console.WriteLine(x.ToString());
x = x + 1;
}
As you see, I executes the database code many times. Once executed, it will iterate the loop making multiple connections in the same pool and at a certain stage, an exception is thrown;
And if I check my TSQL code at this time, I will see that number of Pooled Connection as 100 for the application session.
Once the pool is filled with 100 connections, it cannot add another connection to the pool. Did you notice that code has created through 238 connections? This indicates that some connections have been reused even though they are not closed. However, this is something you need to always check if you get this error. If you see that code does not close the connection, it is better to check all codes and add closing code, making sure that the connection can be reused and no connection pool related errors.
Once the pool is filled with 100 connections, it cannot add another connection to the pool. Did you notice that code has created through 238 connections? This indicates that some connections have been reused even though they are not closed. However, this is something you need to always check if you get this error. If you see that code does not close the connection, it is better to check all codes and add closing code, making sure that the connection can be reused and no connection pool related errors.
