Choosing Pool Sizes
Being able to establish a maximum threshold is very important for large-scale systems that manage the concurrent requests of many thousands of clients. You need to monitor connection pooling and the performance of your application to determine the optimum pool sizes for your system. The optimum size also depends on the hardware on which you are running SQL Server.
During development, you might want to reduce the default maximum pool size (currently 100) to help find connection leaks.
If you establish a minimum pool size, you will incur a small performance overhead when the pool is initially populated to bring it to that level, although the first few clients that connect will benefit. Note that the process of creating new connections is serialized, which means that your server will not be flooded with simultaneous requests when a pool is being initially populated.
.
More Information
When using SQL Server .NET Data Provider connection pooling, be aware of the following:
- Connections are pooled through an exact match algorithm on the connection string. The pooling mechanism is even sensitive to spaces between name-value pairs. For example, the following two connection strings will result in two separate pools because the second contains an extra space character.
· SqlConnection conn = new SqlConnection(
· "Integrated Security=SSPI;Database=Northwind");
· conn.Open(); // Pool A is created
·
· SqlConmection conn = new SqlConnection(
· "Integrated Security=SSPI ; Database=Northwind");
· conn.Open(); // Pool B is created (extra spaces in string)
The connection pool is divided into multiple transaction-specific pools and one pool for connections not currently enlisted in a transaction. For threads associated with a particular transaction context, a connection from the appropriate pool (containing connections enlisted with that transaction) is returned. This makes working with enlisted connections a transparent process.
Shashi Ray