When are pools destroyed? Connection pools are destroyed when the process that creates them ends. Because of this, an ADO.NET Data Provider running under IIS or MTS creates connection pools and destroys them when the process or Application Domain (AppDomain) ends. Note that a bug exists in the performance counters that the SQL Server .NET Data Provider (System.Data.SqlClient) exposes. The bug falsely reports the existence of active pools. To make sure that what you're seeing isn't a "ghost" pool, stop the Performance Monitor application, take a drink of coffee, and check again. This bug isn't fixed in .NET Framework 1.1. When you're working with Windows Forms applications, pools drop off when the application or process ends. This behavior makes sense because the process creating the remote connection pool wouldn't necessarily end if IIS is acting as an agent for the Web application or Web service.
If you open another connection in code, the pool returns to life when you open its member connections. Don't confuse connection pools that the .NET Data Provider manages with the database connections that the pooler opens and closes. When your code closes a connection, the connection returns to the pool. After a period of time that the .NET Data Provider determines, the connection to the database closes if it's not reused.
Be aware that if you shut down the SQL Server system (or your database management system—DBMS) or it shuts down on its own, any existing SqlClient connection pools remain unless IIS or MTS shuts down, too. These remaining pools are called zombied pools. If you subsequently restart SQL Server, the SqlClient .NET Data Provider tries to use the zombied pools to reconnect to the server. The result is the System.Data.SqlClient.SqlException General network error. Check your network documentation. Fortunately, this exception destroys the zombied pools, and when the component restarts, the SqlClient .NET Data Provider creates a new pool. I heartily suggest you add an exception handler to deal with this contingency.
Effectively Using Connection Pools
So, what's the best way to use connection pools? Given the constraints that the .NET Data Provider implements, you might think that it's easy to overflow the pool or server with connections if you aren't careful what valves and switches you open and close. Remember that the limiting resources are the number of connections that a pool can handle and the number of connections available on the server. As I noted earlier, the default number of connections a pool can handle is 100; you can set a higher number, but when the number of connections within the pool reaches this value, the .NET Data Provider won't create more pooled connections. Ideally, your application should use a connection for a moment and release it back to the pool for other instances to share, so 100 connections per pool should be more than enough.
You can let the .NET Data Provider create any number of pools, again limited by the number of connections SQL Server allows according to your license or administrator settings. If some of your components are likely to compete for connections in a heavily used pool, it's a good idea to separate those components by creating two or more pools—one for each functional operation. One way to establish this separation is to set the application name in the ConnectionString to a unique value for each pool you want the .NET Data Provider to create. Watch out, though, because when a pool overflows (i.e., you exceed the set number of connections), your component performance grinds to a halt and your customers take their business to other sites.
Client/Server Pool Rules
Before you wade too deeply into connection pooling, you should reflect on what kind of application will benefit from connection management. Not all applications do. Client/server applications use pooled connections, but the specific instance of the client application is the only beneficiary. That's because each application runs in a unique process, so each client application gets its own pool. Multiple copies or instances of the same Windows Forms application don't share the same pool; however, when the application makes multiple connections (as applications often do), the .NET Data Provider manages all the connections in a common pool.
When you're working with client/server applications that need to manage the server-side DBMS more closely, the pool can be more of a hindrance than a help. In such cases, you might want the functionality of a like-new connection each time you use the Open method and an assurance that the connection actually closes when you use the Close method. Conversely, if the application repeatedly makes and breaks connections, the connection string remains unchanged, and you don't use transactions, the .NET Data Provider will create a pool for your connection. This means that the application will reuse your pooled connection whenever possible, and your application will run faster because the connection isn't being reopened and closed repeatedly.
Wading into the Web
Currently, the overwhelming focus at Microsoft is Web- and middle-tier architectures, in which a code snippet's lifetime is shorter than a rabbit's heartbeat and replicated just as quickly. These routines have to open a database connection, execute a query, and return a response very quickly—before the customer loses interest. Between one and n instances of the component simultaneously execute a process—dozens to thousands of times per second—with all the code snippets doing pretty much the same thing and using the same connection string.
If the component (such as a Web service) takes too long to finish using the connection, the .NET Data Provider might add more connections to the component's private pool. Also remember that each .NET assembly gets a unique PID. This means that each new assembly you run gets its own pool—regardless of the similarity of function or the fact that their ConnectionString values are identical.
Managing Lots of Swimmers
In any architecture, you need to carefully monitor two factors: the number of pools and the number of connections each pool manages. Fortunately, you can control both with code.
When your Web site is under heavy use, a good strategy for making sure you have enough connections is to open each connection just before you need it and close it as soon as you can—the just-in-time (JIT) strategy. ADO.NET can help in this because it opens connections automatically when you use the DataSet Fill or Update method. If you're using a DataReader, you have to open and close the connection yourself. Even if you use the CommandBehavior.CloseConnection option, you still have to close the DataReader to get the associated connection to close. Unlike VB 6.0, none of the .NET languages can guarantee your connection will close when a Connection object (such as SqlConnection) falls out of scope. If the connection is still open, you can't reuse it. If the Connection object is still open when it falls out of scope, it's probably lost forever. For example, the code segment that Listing 1 shows will "leak" a connection each time the procedure executes because the Connection isn't closed before the function that creates the Connection object is completed. This leak happens because the code can no longer reference the Connection object (which owns the pooled connection) after it falls from scope, and the .NET garbage collector won't necessarily clean up these orphaned objects. Although the code in Listing 1 works in VB 6.0 and COM-based ADO, the code doesn't work in ADO.NET.
How your application behaves when all available connections are in use is crucial to the effective performance of your Web site. A helpful tip is to remember that you can increase the value of the Connection Timeout connection-string argument or the Connection object's ConnectionTimeout property. You use one or both of these values to set the length of time ADO.NET waits for a connection to be released from the pool and made available to your code. If you set the value too high, the browser (or client) might time out and throw the System.InvalidOperationException before you get connected. If you set the value too low, your error handler will have to know how to retry the connection—possibly after asking the customer to be patient while your application deals with other requests.
Prev. page
1
2
[3]
4
next page 
