Activating, Tuning, and Disabling the Connection Pool
When you're using the SqlClient .NET Data Provider to access SQL Server 7.0 and later, you need to create a new SqlConnection object and set its ConnectionString property. In the ConnectionString property, you choose the security setting you'll be using, point to the server, and set the communications protocol. Using this property is the only way to change the SqlClient .NET Data Provider's behavior and control how it interacts with the connection pool. By using the appropriate ConnectionString keywords, you can turn off pooling, change the size of the pool, and tune the pool's operations.
Understanding connection-pool options. Table 1, page 34, defines the SqlClient.SqlConnection.ConnectionString keywords that determine how the .NET Data Provider manages the connection pool for the specific connection you're opening. You can set these keywords on a connection-by-connection basis, which means that some Connection objects will be pooled and others won't be.
Turning off connection pooling. When you want more hands-on control of the server-side connection state or you want to debug a connection-pooling problem, using an unpooled connection makes sense, so it's nice to know that you can easily turn off connection pooling. If you're sure you want to turn off pooling, simply set the Pooling argument in the ConnectionString to False, and the .NET Data Provider won't pool the Connection object that you opened by using this option. The Open method will establish a new connection to the server (if one is available), and when you use the Close method, the .NET Data Provider will close the server connection immediately. The server can deny a connection-open request if you've exhausted the number of connections the sa allows or you exceed the number of connection licenses registered for the server. If you're turned away from the server for some reason, your code needs to display a message to tell your user to come back later or simply wait and retry.
Setting the maximum pool size. You use the Max Pool Size keyword to determine how many connections the pool will hold. By default, the .NET Data Provider permits 100 connections to accumulate in each pool. This doesn't mean that you start with 100 SqlConnection objects or 100 connections, only that you can't create more than 100 connections per pool. If you try to exceed the maximum limit you've set, your application will wait until you pass the ConnectionTimeout limit or until a connection becomes available in the pool, then raise an exception. Max Pool Size doesn't limit the number of pools you can create—that limit is determined by the number of SQL Server connections that your sa setting or license makes available (about 32,768 per cluster).
In some cases, you might want to leave one or more connected SqlConnection objects in a pool for an indefinite length of time. If so, set the Min Pool Size keyword to an appropriate value. Setting this value can help connection performance for an application that doesn't run often but can't wait for the .NET Data Provider to construct a new SqlConnection object and establish a connection. You can also use this technique as a way to reserve connections. For example, you might set a Min Pool Size value if you want your Internet application to be able to deal with a flood of queries that grab all the connections from the pool but still ensure that you have a private connection. Or suppose you're hosting several applications on the same server and one of those applications is getting hammered with queries; by using this technique, you can reserve some connections for each application.
After you close a connection in code, ADO.NET notifies the connection pooler to release the connection back to the pool, and after a time (between 4 and 8 minutes), a connection-pool routine closes the database connection. The Connection Lifetime ConnectionString keyword isn't what it appears to be. Microsoft included this option to handle a very special circumstance—when you're using clustered servers. Connection Lifetime helps release connections more quickly when several SQL Servers are working in a cluster. In nonclustered systems, the argument has no bearing on how long the connection remains in the pool.
As I mentioned, generally the connection-pool mechanism uses a random connection lifetime of between 4 and 8 minutes. After you close the ADO.NET Connection object, connections remain open for the randomly selected period of time after your application releases them. The .NET Data Provider calculates the pool lifetime starting from the time the pooled connection was created.
Jump In with Both Feet
The way that ADO.NET interfaces with the connection-pool mechanism is new—but connection pools have been around for several years now, so you should be fairly comfortable with how they work. However, if you're not using ADO.NET and the SqlClient .NET Data Provider, it's tough to manage connection pools. If you don't understand the pooling mechanism, there's a good chance you'll create applications that work for a while but fail under stress. Or you might create applications that exhibit substandard performance, but you can't determine why. With the information in this article, you can explore how best to manage your connections by using the built-in pooling mechanism.
What's missing from this article is information about how to monitor the pool. You need to know how many pools are being managed, how many connections are being held in each pool, and which applications or processes create these pools. I'll cover all this information and more in an upcoming article that discusses how to monitor the state of the pool by using existing utilities and your own code.