Ensure Proper SQL Server Connection Pooling

By: Michael K. Campbell

Coming to SQL Server from both a database developer and DBA background, I maintain that while tuning SQL Server’s ability to handle your application workloads is a viable way to increase performance, another way to bolster performance is to consider architectural and coding practices within your applications.

This is especially true of connection pooling – which Microsoft describes as follows:

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

The problem, however, with connection pooling is that it can be complex to correctly configure and define – making it non-trivial to implement correctly in some environments.

Connection Pooling Defined
Happily, however, Microsoft has provided some great guidance around how to properly understand and configure Connection Pooling – including the following resources:

An Overview of Connection Pooling in ADO.NET (link)
Cited above, this resource provides a decent overview of what connection pooling is, why it’s important, and how to leverage it properly. It also bypasses a lot of the FUD and bad advice around connection pooling that has sadly crept up over the years – no doubt due to the non-trivial nature of some of the configuration semantics required to get pooling to work correctly under more complex scenarios.

Connection String Details that Define Pooling Considerations (link)
Focusing primarily on how to construct connection strings for use within ADO.NET applications, this resource also helps define the specific components used in connection strings that can be used to regulate and define pool sizes and parameters (near the bottom of the page).

Development and Coding Considerations to avoid ‘leaking’ Connections (link)
While this guidance is decidedly older than the other two sources cited, the section on pooling resources defines best practices for explicitly calling Dispose or Close on expensive resources – including SQL Server database connections. (Most of the problems that I encounter with improper connection pooling are typically due to ‘leaks’ caused by thrown exceptions or improper handling of the need to close connections in order to return them to the pool. That, or developers opening connections prematurely and keeping them open WELL after data has already been collected.)
This resource also covers advice on how to avoid caching or blocking pooled resources – and is a must-read for developers who aren’t using some sort of framework or proven code that manages connection pooling for them.

Detecting Potential Problems with Connection Pooling
Given how hard it can be to get connection pooling working perfectly in more complex environments (or in cases where developers may not have understood the basics or necessity of connection pooling), I always try to check for proper or improper connection pooling when performing SQL Server audits for my consulting clients.

The problem, however, is that the easiest way to rule out or validate problems with connection pooling requires long-term profiling or access to the application servers connecting to SQL Server. As such, I typically run a query similar to the following in order to ‘gut check’ whether or not there might be problems with connection pooling:

... To view more http://sqlserverperformance.idera.com/io-performance/ensure-proper-sql-server-connection-pooling/