In this blog post, we will be sharing the Azure SQL Connection Pooling best practices. Why is it important you ask? It is because connection pooling is a resource hogger, or in order words, expensive.
In order to maintain top performance, connection resiliency, and scalability, you would need to follow the best practices. When a new connection is made, new memory allocations need to happen dynamically by the underlying OS. A highly transactional application typically opens and closes many connections dynamically. If the database system is not optimized correctly, it will result in reduced transactions per second. This will, in turn, increase the application latency.
This is where connection pooling comes to the rescue. It aids in reusing the existing connections and therefore results in lesser latency. It helps the underlying OS to stop allocating & deallocating memory resources dynamically.
Typically, to leverage connection pooling, it is recommended to design your application to take full advantage of it. It helps in keeping a fixed set of connections during startup which is eventually maintained.
In most cases, the connection pooling the needs to be configured on the application end. The application framework and the driver needs to support it. On several open source databases like PostgreSQL, it leverages a proxy connection pooler service like PgBouncer or Pgpool running between the application and connects to the database server.
In the case of ADO.NET, it uses an in-built optimization technique and is hence called connection pooling. In order to reduce the number of new connection, the in-built pooler service maintains the physical connection and keeps it alive based on the system configuration.
The pooler works like a broker service. Each time when the application opens a new connection, the pooler assigns an active connection. Upon termination from the apps end, the connection is returned to the existing pool. So, a new connection is neither opened nor closed.
For ADO.NET, the following is the expected behavior:
In the case of ADO.NET, the pooler service optimizes the pooled connections as they are opened and closed from the application end, unless explicitly disabled. So, the best practices are as follows:
In the case of SQL Azure, it will close an already established connection for specific transactions if:
We hope that the above explanation was helpful for you to understand connection pooling best practices for Azure SQL better. Questions and suggestions are welcome in the comment box below. Thanks for your visit!
Comments are closed.