Connection Pools

How connection pooling works:

using (SqlConnection connection = new SqlConnection(
“Integrated Security=SSPI;Initial Catalog=Northwind”))
{
connection.Open();
// Pool A is created.
}

using (SqlConnection connection = new SqlConnection(
“Integrated Security=SSPI;Initial Catalog=pubs”))
{
connection.Open();
// Pool B is created because the connection strings differ.
}

using (SqlConnection connection = new SqlConnection(
“Integrated Security=SSPI;Initial Catalog=Northwind”))
{
connection.Open();
// The connection string matches pool A.
}

SQL creates a connection pool by default for each unique connection made (based on the entire connection string not the server details) with a minimum and maximum number of connections. If you connect with any string that differs from the existing pool connection (even blank spaces in a similar connection string count) a new pool is created with default parameters (0 min – 100 max). To avoid accidentaly creating connection pools you should cache the connection string on first opening your applications and use the cached string for all connections. This way you avoid accidentaly creating new connection pools. If you want more granular control over the pool, then you can set the pool size in the connection string as below

Dim strConn As String =
“Server=PS002;Database=Northwind;Integrated Security=True;Min Pool Size=10;Max Pool Size=200”

Creating the ten-connection pool causes a performance hit to the first client that opens a connection from web servers, but will not affect lan connections. I am not aware that you can redimension the pool once created as this would require a change to the connection string and therefore generate a new pool. So setting a different Max Pool Size on the first and third connection in the code example will not shrink or increase the pool between the connections but simply create two pools.

Advertisements

%d bloggers like this: