14.20 Modifying Pooling Behavior for SQL Server Connections

You want to examine the current behavior of a .NET data provider's connection pooling behavior.

Technique

Connection pooling is a caching mechanism used by the SQL Server .NET Data Provider to increase the performance of multiple connections created from a process. You control how the data provider uses connection pooling by setting parameters within the connection string. As new connections are made using the same connection string, the connection is placed within the same pool. Table 14.1 lists the available connection string parameters to control connection pooling.

Table 14.1. Connection Pooling Connection String Parameters

Parameter	Default	Behavior
Connection Lifetime		Controls how long a connection remains within a connection pool even after the process ends. A value of specifies the maximum lifetime.
Connection Reset	true	Resets the connection when it is removed from the pool, which causes an additional round-trip to the server.
Enlist	true	Enlists the connection into the current transaction context if one exists.
Max Pool Size	100	Total number of connections that can exist in a single pool.
Min Pool Size		Minimum number of connections that must be in a pool.
Pooling	true	Controls whether to use pooling for this connection.

Comments

Connection pooling is the process of returning a connection that is already open , triggered by a different connection attempt. Rather than create another connection to the same database, the process shares the connection among two objects. For instance, suppose you made an SqlConnection to the Northwind database using the code:

 SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind"; conn.Open(); 

While this connection is open, let's say that another thread opens another connection using the same connection string, and within a certain time period, eight more connections are made within a single connection pool. As the application continues, a few connections close. However, because connection pooling is enabled, the connections actually remain open within the connection pool and remain open for the amount of time specified in the Connection Lifetime parameter. Suppose then that the application creates yet another connection to the database. The .NET data provider realizes that three connections are open but are not active because the application requested that they get closed. Therefore, the active connections that weren't being used can now be passed back to the application, avoiding a trip to the database server for another connection attempt.

If you place this concept within the realm of an ASP.NET application, you can see why pooling can give your application a performance advantage. By configuring the connection string for connection pooling, you can use the performance monitor to ensure that your application is getting the best possible performance when creating and closing connections repeatedly. One particular connection string parameter worth mentioning is Min Pool Size . It specifies how many connections should remain in the connection pool during an application's lifetime. For instance, if your Web application is a high-traffic application, meaning several clients are always on at any point in time, you should consider setting the Min Pool Size parameter to a higher number. When your application runs for the first time and makes a single database connection, setting Min Pool Size to 10, for instance, automatically creates and places 10 connections in the pool, only 1 of which is currently active. This means that the next nine clients that use your application do not have to make a trip to the database server to create a connection. Rather, the connection is just handed to your application right out of the pool and ready to go.