SQL Connection Pooling.

First question that arise on ones mind is why do we need connection pooling at first place ? Answer to this would be clear if we look at what happens in a typical server.

To connect to a server, typically there are many time consuming steps.First, a physical channel such as named pipe must be established, initial handshake with server should occur, connection string needs to be parsed, connection must be authenticated and so on.

Think how badly performance would be hit if these steps needs to be done for every query you do with server. In most practical cases, most application would use only one or few different configurations for connections. Or in other words, during the entire life cycle of application, many identical connections will be repeatedly opened and closed. To minimize this cost of performance, ADO.Net came up with a much optimized technique called Connection Pooling.

The Pooler would maintain ownership of the physical connection. It manages by keeping alive a set of active connections for each given connection configurations.

So whenever a user calls open on a connection, pooler first looks in the available connection pool.If its available, it returns it to caller instead of creating a new connection. When application makes a call to the Close methods, pooler returns the connection to the pooled set instead of closing it.

This ensures that the number of times new connections are opened are reduced many a folds. Please note that only connections with same configurations can be pooled. But ADO.Net can keep several pools at same time for each configurations.

One important point to note in here is that SQLConnection object in .Net, by default as the Connection Pooling enabled. To disable it, you need to use "Pooling = false" in the connection string.