Connection Pooling

Introduction

Connecting to the database is resource intensive and a relatively slow operation in an application but the most crucial of them all. A Connection Pool is a container of open and reusable connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. Connection Pooling gives you an idle, open, reusable connection instead of opening a new one every time a connection request to the database is made. When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.

How does a Connection Pool work?

Connection pools are actually containers that contain open and reusable connections. Multiple pools can exist in the same application domain at the same point in time, but Connection Pools cannot be shared across application domains. Note that one pool is created per unique connection string. A Connection Pool is created the first time a request for a connection to the database comes in with a unique connection string. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created. Hence, we have one Connection Pool per connection string and not per database. The following code listings below illustrate this. Listing 1 // A new pool is created. SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString =  "Server=REDDY-PC;Database=Sample;User ID=reddy;Password=reddy;Trusted_Connection=False"; sqlConnection.Open();       Listing 2 // A new pool is created as the connection strings differ. SqlConnection conn = new SqlConnection(); sqlConnection.ConnectionString =  "Server=REDDY-PC;Database=Sample;User ID=reddy;Password=reddy;Trusted_Connection=False"; sqlConnection.Open();    Listing 3 // The connection string is the same as in Listing 2 so no new pool is created. SqlConnection conn = new SqlConnection(); sqlConnection.ConnectionString =  "Server=REDDY-PC;Database=Sample;User ID=reddy;Password=reddy;Trusted_Connection=False"; sqlConnection.Open();       When a request for a new connection comes in the connection is served from the Connection Pool without creating a new one, i.e., the connections are re-used without creating new ones.  Therefore, it improves the performance and scalability of your applications. When your application closes an open connection, it is returned to the pool where it waits until a reconnect time out period expires. This is the period within which it waits to connect to the same database using the same credentials. If none comes in within this period, the connection to the database is closed and the connection instance is removed from the pool. When a new Connection Pool is created, the connection objects are placed in the pool and the pool and the connections contained in it are made available for immediate use. The pool can house connections up to the maximum limit as specified in the connection string that was used to connect to the database. Connections are removed from the pool when they remain inactive for a long period of time or have outlived its specified life time or server connectivity. A Connection Pool is maintained internally by the Connection Pool Manager. When a request for a subsequent connection comes in, the Connection Pool Manager searches the pool for the availability of a free connection and returns it to the application if one is available. The following points elaborate how the Connection Pool Manager works- the operations that it performs when a request for a new connection comes to it. ·         If any unused connection is available, it returns one. ·         If all connections are used up, a new connection is created and added to the pool. ·         If the number of connections reaches the maximum number of connections in the pool, the requests are queued until a connection becomes free for reuse. Connection Pooling is controlled and the parameters passed to a connection string that basically comprises the following: ·         Connect Timeout ·         Min Pool Size ·         Max Pool Size ·         Pooling In order to use Connection Pooling efficiently, remember to close the connections once you are done with it so that it returns to the pool.

Improving Connection Pooling Performance

We should always open the connections late and release them early; in other words, immediately after we are done using it. Connections should be opened only at the time when they are actually required.  The connections should not be acquired prior to its usage as it would decrease the number of available connections in the Connection Pool and, hence, have detrimental effects to the operation of the Connection Pool and the application's performance. The connections should be explicitly released immediately when we are done using it. This would facilitate better Connection Pooling as the connection would be returned to the pool and be available for reuse. The following snippet of code shows how we should open and close connections in our applications efficiently. Listing 4 SqlConnection sqlConnection = new SqlConnection(connectionString); try {   sqlConnection.Open();   //Some Code }   finally {   sqlConnection.Close(); } The code in Listing 4 can be simplified further by using the "using" keyword as shown in the code snippet below. Listing 5 using(SqlConnection sqlConnection = new SqlConnection(connectionString)) {   sqlConnection.Open();   //Some Code } Note that using the "using" keyword as shown in Listing 5 above, generates the try-finally block implicitly. The following points illustrate the measures that can be taken for better utilization of the Connection Pool. ·         Always open connections when needed and close it immediately when you are done using it. ·         Close the user-defined transactions before closing the related connections. ·         Ensure that there is at least one connection open in the pool to maintain the Connection Pool. ·         Avoid using connection pooling if integrated security is being used. Connection Pooling can be monitored in one of the following ways: ·         Executing the sp_who or sp_who2 stored procedures ·         Using the SQL Server Profiler ·         Using the Performance Monitor and Performance Counters. Connection Pooling Settings KEY DESCRIPTION Connection Life time Connection creation time is compared with the current time, span exceeds the Connection Lifetime value, and object pooler destroys the connection. The default value is 0, which will give a connection the maximum timeout. Connection Reset Determines whether a connection is reset after it was removed from the pool. The default value is true. Max pool size Maximum number of connections allowed in the pool. The default value is 100. Min pool size Minimum number of connections allowed in the pool. The default value is 0. Pooling When true, the connection is drawn from the pool or created if necessary. The default value is true.