Connection Pooling

When we open a new connection it takes time this way it affects application performance. So by default .Net use connection pooling, while needed it get the database connection from there and while finishing the work it back place it to the pool.

We no need to worry about that SQl Server provide this facility through connection pool manager which provide the same facility to us.

String ConString = "integrated security=SSPI;SERVER=YOUR_SERVER;Pooling=true;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;Connection Lifetime=10";

Min Pool Size :- When we start application pool manager 5 connection opened. if needed more it can go up to max size. By default its value is 0.

Max Pool Size :- How many maximum connection can reside in pool it. By default value is 100.

Connect Timeout :- If we request a new connection and there is no connection available in the connection than it creates a new connection and add it in pool, if number of pools has reached Max than Pool Size than it waits for 2 second to release any connection, if there is available a connection within that time period it use else through an exception “System.InvalidOperationException Message: Timeout expired”. By default value is 15 Seconds.

Pooling :- 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. Recognized values are true, false, yes, and no.

Connection Lifetime :- When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.
A value of zero (0) causes pooled connections to have the maximum connection timeout. By default value is zero.

Incr Pool Size :- Controls the number of connections that are established when all the connections are used.By default value is five.

Decr Pool Size :- Controls the number of connections that are closed when an excessive amount of established connections are unused. By default value is one.

Enlist :- When true,the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values are true, false, yes, and no. Set Enlist = "false" to ensure that connection is not context specific.By default value is true.

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.