Most ADO.NET data providers use connection pooling to improve the performance of applications built around Microsoft's disconnected .NET architecture. An application opens a connection (or gets a connection handle from the pool), runs one or more queries, processes the rowset, and releases the connection back to the pool. Without connection pooling, these applications would spend a lot of additional time opening and closing connections.
When you use ADO.NET connection pooling to manage connections for your Web-based applications and client/server Web service applications, your customers will usually get faster connections and better overall performance. But what happens when your application or Web site is suddenly flooded with customers who all want to connect at the same time? Will your applications sink or swim? Like a lifeguard, you need to monitor your connection pools carefully to maintain good performance and to prevent your pools from overflowing. Let's explore the reasons a connection pool might overflow, then see how you can write code or use Windows Performance Monitor to monitor your pools.
As I discussed in "Swimming in the .NET Connection Pool," May 2003, InstantDoc ID 38356, you need to know about many scalability and performance details when you use connection pooling. Remember that you need to monitor and manage two essential factors: the number of connections each pool manages and the number of connection pools. In an efficient production system, typically the number of pools is low (1 to 10) and the total number of connections in use is also low (fewer than 12). An efficient query takes less than a second to complete and disconnect. So even if hundreds of customers are accessing your Web site simultaneously, relatively few connections can often handle the entire load. To make your applications run efficiently, you must keep connection resources under control and monitor your pools' status so that you'll have some warning before they overflow and your customers start to complain—or go elsewhere.
Why Does a Connection Pool Overflow?
Email discussion group participants often complain about how applications seem to work in testing but fail in production. Sometimes they report that their applications stop or bog down when about 100 clients get connected. Remember that the default number of connections in a pool is 100. If you try to open more than 100 connections from a pool, ADO.NET queues your application's connection request until a connection is free. The application (and its users) sees this as a delay in getting to the Web page or as an application lock-up. Let's look at how this problem arises.
In ADO.NET, the SqlClient .NET Data Provider gives you two techniques for opening and managing connections. First, when you need to manage the connection manually, you can use the DataReader object. With this method, your code constructs a SqlConnection object, sets the ConnectionString property, and uses the Open method to open a connection. After the code is finished with the DataReader, you close the SqlConnection before the SqlConnection object falls out of scope. To process the rowset, you can pass the DataReader to another routine in your application, but you still need to make sure that the DataReader and its connection are closed. If you don't close the SqlConnection, your code "leaks" a connection with each operation, so the pool accumulates connections and eventually overflows. Unlike in ADO and Visual Basic (VB) 6.0, the .NET garbage collector won't close the SqlConnection and clean up for you. Listing 1, which I walk through later, shows how I opened a connection and generated a DataReader to return the rowset from a simple query to stress the connection pool.
You can also run into problems when you use the DataAdapter object. The DataAdapter Fill and Update methods automatically open the DataAdapter object's connection and close it after the data I/O operation is complete. However, if the connection is already open when the Fill or Update method is executed, ADO.NET doesn't close the SqlConnection after the method completes. This is another opportunity to leak a connection.
In addition, you can also use COM-based ADO to create a connection from a .NET application. ADO pools these connections in the same way that ADO.NET does but doesn't give you a way to monitor the pool from your application as you can when you use the SqlClient ADO.NET Data Provider.
Indicting the DataReader
Orphaned connections and overflowing pools are serious problems, and judging by the number of newsgroup discussions about them, they're fairly common. The most likely culprit is the DataReader. To test the behavior of the DataReader, I wrote a sample Windows Forms (WinForms) application concentrating on the CommandBehavior.CloseConnection option. (You can download this application by entering InstantDoc ID 39031 at http://www.sqlmag.com.) You can set this option when you use the SqlCommand object's ExecuteReader method to execute the query and return a DataReader. My test application shows that even when you use this option, if you don't explicitly close the DataReader (or SqlConnection), the pool overflows. The application then throws an exception when the code requests more connections than the pool will hold.
Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially right—but the option works this way only when you're using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when Dr.Read—the DataReader's Read method—returns false) isn't enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection—but only if you've set the CommandBehavior.CloseConnection option.
If you execute a query by using another Execute method (e.g., ExecuteScalar, ExecuteNonQuery, ExecuteXMLReader), you are responsible for opening the SqlConnection object and, more importantly, closing it when the query finishes. If you miss a close, orphaned connections quickly accumulate.
Prev. page
[1]
2
3
4
next page 
