What is Connection Pooling in SQL SERVER?
Connection Pooling is a technique to allow multiple
clients to make use of a cached set of reusable connection objects providing
access to a database.
Opening and Closing database connections is an expensive
process and hence connection pools improve the performance of execution of
commands on a database for which we maintain connection objects in the pool. It
empowers reuse of the same connection object to serve a number of client
requests. Every time a client request is received, the pool is searched for an
available connection object and it's highly likely that it gets a free connection
object. Otherwise, either the incoming requests are queued or a new connection
object is created and added to the pool. As soon as a request finishes using a
connection object, the object is given back to the pool from where it's
assigned to one of the queued requests. Since most of the requests are served
using existing connection objects only so the connection pooling approach
brings down the average time required for the users to wait for establishing
the connection to the database.
normally Connection Pooling used in a web-based
application where the application server handles the accountability of
creating connection objects, adding them to the pool, assigning them to the incoming requests, taking the used connection objects back and returning them
back to the pool. When a dynamic web page of the web-based application
explicitly creates a connection to the database and closes it after use then
the application server internally gives a connection object from the pool
itself on the execution of the statement which tries to create a connection and
on execution of the statement which tries to close the connection, the
application server simply returns the connection back to pool.
Connection Pool are configurable means the
maximum/minimum connections and maximum idle connections can be configured by
the server administrator. On start up the server creates a fixed number of
connection objects and adds them to the pool. Once all of these connection
objects are exhausted by serving those many client requests then any extra
request causes a new connection object to be created, added to the pool, and
then to be assigned to server that extra request. This continues till the
number of connection objects doesn't reach the configured maximum number of connection
objects in the pool. The server keep on checking the number of idle connection
objects as well and if it finds that there are more number of idle connection
objects than the configured value of that parameter then the server simply
closes the extra number of idle connections, which are subsequently garbage
collected.
