What is a Connection Pool?
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 ?
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
"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 due to Integrated Security
Pool Fragmentation due to Many Databases used by same application
Clearing the Pool
How to Monitor the connection Pool ?
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.SqlClientModule 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
Thank you for reading this post.
If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba