connection pooling in .net

Connection Pooling in asp.net

If you are using asp.net to a SQL database, you will most likely use a SQL connection object. If you are using SQL connection object, something called connection pooling is enabled by default. Now the question that naturally arises is what is connection pooling?  When your asp.net application connects to SQL server, opening a connection is resource intensive and takes time. So when you call close() on a connection, instead of actually closing the connection it gets returned to a "pool of connections".  So this connection is in fact still open. Then next time you call an Open() on a sql connection, this pre existing open connection from the connection pool gets reused. This is called connection pooling. Connection pooling in managed by the data provider ( in the example below, .NET Framework Data Provider for SQL Server) .

Now what if multiple users perform an operation at the exact same time all of which require database connection? In that case new connections to the database will be opened, then all of them will be returned to the connection pool when close() is called on those connections.

In case you don't close connections and your application pool reaches the maximum number of open connections (default is 100 open connections per connection pool), trying to open another connection after that will throw this error

"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.
"

So it is necessary to close a connection after the required operation is complete.

SqlConnection.ClearAllPools() actually closes all the open SQL connections for a particular application. (if you run an EXEC sp_who in SQL server before and after the  ClearAllPools(), you can see the connections were closed.


Affect of connection poling on performance

Just for the fun of it, I ran the Query below once with the connection pooling disabled and the second time with connection pooling enabled. All I am doing is opening and closing a connection over and over, 100000 times. That would show us how expensive opening and closing a connection can be, without connection pooling.

for (int i = 0; i < 100000; i++)

{

    SqlConnection conn = new SqlConnection();

    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PlayGroundConnectionString"].ConnectionString;

    conn.ConnectionString = connectionString;

    conn.Open();

    conn.Close();

}

These are the results

Connection Pool Enabled?	Time
False	240 seconds
True	2.4 seconds

As you can see with the connection pooling enabled, the performance gains are extra ordinary.

Connection Pooling Quiz

Q>How to disable connection pooling
A> This can be done in the connection string.

<add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;User ID=mySqlUserName;Password=myPassWord;Pooling=False;""

      providerName="System.Data.SqlClient" /> 

Q>How many connection pools can an application have?
A>Many. "Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity." Even one character difference in two connection strings will result in two different connection pools. Hence it is safer to store the connection string in the web.config file. 

Q>If connection pooling is disabled will the connections still be limited to 100 open connections?
A> No.

Q>If you enable windows authentication, what is its significance with respect to connection pooling?
  <connectionStrings>

    <add name="myConnectionString" connectionString="Data Source=MyServerName;Initial Catalog=MyDataBaseName;Integrated Security=True"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

A> Every time a new user logs in a new connection must be opened (because a new connection pool is created for every user). That would cause a large number of open connections if the application has a large number of users.

Q>If you reach the maximum number of permissible connections in a connection pool, how long would it wait before the next connection request is timed out?
A>15 seconds (default time out)

Q>If connection pooling is enabled, when you try to connect to the database, will you see the login event on SQL trace?
A> If an open connection was fetched from the application pool you will not see it.

Further reading

Connection Pooling (ADO.NET)