Solutions with Practical SQL DBA : Connection Pooling :At a Glance

Friday, 15 June 2012

Connection Pooling :At a Glance

What is a Connection Pool?

A connection pool is a group of database connections (with same connection properties) maintained in the application server so that these connections can be reused when future requests to the database are required.Creating a connection to the database servers is a time consuming process.To establish a connection to the database server , a physical channel such as socket or named pipe must be established , the connection string information to be parsed, it should be authenticated by the server and so on.Connection pool helps to reuse the established connection to serve multiple database request and hence improve the response time. In a practical world, most of the application use only one or a few different connection configuration.During the application execution, many identical connections will be opened and closed. To minimize the cost of opening connections each time,ADO.NET uses the technique called Connection Pooling. Many people has the misunderstanding that, connection pool is managed in the database server.
Connection pooling reduces the number of times that new connection must be opened.The pooler maintains ownership of the physical connection. Whenever a user calls open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls close on the connection, the pooler returns it to the pool instead of closing it. Once the connection is returned to the pool, it is ready to reused on the next open call.

How the Connection Pool Works ?

Many people are not very familiar with the connection pool and how it works. This is because connection pooling is a default property of ADO.NET.We do not have to do anything special to enable the connection pooling.It can be enabled or disabled by setting the value true or false for connection string property Pooling (;pooling =true). Connection pool is tightly coupled with connection string . A slight change in the connection string (change in the case / change in the order of connection property ) will force the ADO.NET to open a new connection pool.Every pool is associated with distinct connection string. ADO.NET maintain separate connection pool for each distinct application ,process and connection string. When first time application request for a connection , ADO.NET look for any associated connection pool. As it is a first request, there will not be any connection pool and ADO.NET negotiate with the database server to create fresh connection.When application close/dispose this connection after completing the process, the connection will not get closed instead it will be moved to connection pool.When application request for next connection using the same connection string, ADO.NET return the context of the the open connection which is available in the pool.If second request from application comes in before the first request closed/disposes the connection , ADO.NET create a fresh new connection and assigned to the second request.

The behavior of connection pooling is controlled by the connection string parameters. Below are the list of parameters that controls the behavior of connection pooling.

Connection Timeout : Control the wait period in seconds when a new connection is requested,if this period expires, an exception will be thrown. Default value for connection timeout is 15 seconds.
Max Pool Size: This specify the maximum number of connection in the pool.Default is 100.
Min Pool Size : Define the initial number of connections that will be added to the pool on opening/creating the first connection.Default is 1
Pooling : Controls the connection pooling on or off. Default is true.
Connection Lifetime : When a connection is returned to the pool, its creation time is compared with the current time, and the connection destroyed if that time span (in seconds) exceed the value specified by connection lifetime else added to the pool. This parameter does not control the lifetime of connection in the pool.It is basically decides whether the connection to be added to pool or not once the it got closed by the caller application.A lower value 1 may be equivalent to a state of pooling is off. A value zero cause pooled connection to have the maximum lifetime.

Connection Leakage

At times, connections are not closed/disposed explicitly, these connections will not go to the pool immediately. We can explicitly close the connection by using Close() or Dispose() methods of connection object or by using the using statement in C# to instantiate the connection object. It is highly recommended that we close or dispose the connection once it has served the purpose.

Connection leakage will happen in the scenarios where the application is not closing the connection once it is served the request. As it is not closed , these connections can not be used to serve other request from the application.and pooler forced to open new connection to serve the connection requests. Once the total number of connection reaches the Max Pool Size,new connection request wait for a period of Connection Timeout and throw below 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"

Pool Fragmentation

Pool fragmentation is a common problem in many applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance.

Pool Fragmentation due to Integrated Security

Connections are pooled according to the connection string plus the user identity. Therefore, if application use Windows Authentication and an integrated security to login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular application architecture that developers must weigh against security and auditing requirements.

Pool Fragmentation due to Many Databases used by same application

Many application may use a single database to authenticate the application login and then open a connection to a specific database based on the the user role / region. The connection to the authentication database is pooled and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connection to the database server.This is also a side effect of the application design.The simple way to get rid of this issue with out compromising the security is to connect to the same database always (may be master database) and run USE databasename statement to change the database context to desired database.

Clearing the Pool

ADO.NET have two methods to clear the pool: ClearAllPool() and ClearPool(). ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

How to Monitor the connection Pool ?

The connection pool can be monitored using the performance counters in the server where the ADO.NET is initiating the connections.While selecting the counter ,make sure to select the right instance based on your application name and process id which is there in the bracket. Process id of your application can easily get from the task manager. Find below a snapshot of perfmon counters.

The below code snippet will help you to understand the connection pooling in much better way. Do not comment on the slandered of the code snippet ! I am not an expert in writing vb/.net code

Imports System
Imports System.Data.SqlClient

Module Module1
    Private myConn As SqlConnection
    Private myCmd As SqlCommand
    Private myReader As SqlDataReader

    Private myConn1 As SqlConnection
    Private myCmd1 As SqlCommand
    Private myReader1 As SqlDataReader
 
    Private myConn2 As SqlConnection
    Private myCmd2 As SqlCommand
    Private myReader2 As SqlDataReader

    Private StrConnectionString_1 As String
    Private StrConnectionString_2 As String
    Private query As String
  
    Sub Main()


        'Two connction string which help us to create two different pool
        'The Application Name is mentioned as ConnectionPool_1 and ConnectionPool_2 to identify the connection in sql server
        StrConnectionString_1 = "Server=XX.XX.XX.XX;user id=" + "connectionpool" + ";password=" + "connectionpool" + ";database=master;packet size=4096;application name=ConnectionPool_1"
        StrConnectionString_2 = "Server=
XX.XX.XX.XX ;user id=" + "connectionpoo2" + ";password=" + "connectionpool" + ";database=master;packet size=4096;application name=ConnectionPool_2"


        query = "select * from sys.objects"


        'STEP :1
        'Opening a connection first connection string and excuting the query after it served closing the connection
        myConn = New SqlConnection(StrConnectionString_1)
        myCmd = myConn.CreateCommand
        myCmd.CommandText = query
        myConn.Open()
        myReader = myCmd.ExecuteReader()
        myReader.Close()
        myConn.Close()

        'Now look at the perfmon counters. Numberofactiveconnectionpoll will be 1 and Numberofpooledconenction will be 1
        'In sql server you can see connection is still open even after closing the connetion.You can verify this by querying the sys.dm_exec_connections

        'STEP :2
        'Opening a connection using the second connection string.This will force the pooler to open one more connection pool
        myConn1 = New SqlConnection(StrConnectionString_2)
        myCmd1 = myConn1.CreateCommand
        myCmd1.CommandText = query
        myConn1.Open()
        myReader1 = myCmd1.ExecuteReader()
        myReader1.Close()
        myConn1.Close()
        'Now look at the perfmon counters. Numberofactiveconnectionpoll will be 2 and Numberofpooledconenction will be 2
        'In sql server you can see two active connection one from ConnectionPool_1 and ConnectionPool_2

        'STEP :3
        'Opening a connection again using first connection string. This will be servered by the existing connection created as part of step 1
        myConn = New SqlConnection(StrConnectionString_1)
        myCmd = myConn.CreateCommand
        myCmd.CommandText = query
        myConn.Open()
        myReader = myCmd.ExecuteReader()

        'Now look at the perfmon counters. Numberofactiveconnectionpoll will be 2 and Numberofpooledconenction will be 2
        'In sql server you can still see only two active connections. one from ConnectionPool_1 and ConnectionPool_2
        'Please note that the connection is not closed 


        'STEP :4
        'Opening a connection again using first connection string. This will be forsed to open a new connection as the connection is not closed in Step3 (connection leakage)

        myConn2 = New SqlConnection(StrConnectionString_1)
        myCmd2 = myConn2.CreateCommand
        myCmd2.CommandText = query
        myConn2.Open()
        myReader = myCmd2.ExecuteReader()
        myConn2.Close()

        'Now look at the perfmon counters. Numberofactiveconnectionpoll will be 2 and Numberofpooledconenction will be 3
        'In sql server you can see three active connections. two from ConnectionPool_1 and one from ConnectionPool_2
     

        'Closing the connection created as part of Step 3
        myConn.Close()
        'Now look at the perfmon counters. Numberofactiveconnectionpolll will be 2 and Numberofpooledconenction will be 3
        'In sql server you can see three active connections. two from ConnectionPool_1 and one from ConnectionPool_2


        'clearing the pool
        SqlConnection.ClearAllPools()
        'Now look at the perfmon counters. Numberofactiveconnectionpoll will be 0 and Numberofpooledconenction will be 0. Number of inactiveconnectionpoll will be 2
        'In sql server you can't see any connection from ConnectionPool_1 or ConnectionPool_2


    End SubEnd Module