Asp. NET and database connection pool

prevent pool overflow for submerged applications

Most Ado.net data providers use connection pooling to improve the performance of applications built around the. NET structure of a Microsoft disconnected connection. The application first opens a connection (or obtains a connection handle from the connection pool), runs one or more queries, processes the rowset, and finally releases the connection back to the connection pool. Without a connection pool, these applications will take a lot of extra time to open and close the connection.

When you use a ado.net connection pool to manage connections between web-based applications and client/server Web service applications, your customers typically get faster connectivity and better overall performance. But what happens when your application or Web site suddenly pours into a large number of customers who want to connect at the same time. Your application will "sink" or "swim". Like a lifeguard, you need to carefully monitor the connection pool to maintain its good performance and prevent the connection pool from overflowing. Let's start with the reason why the connection pool might overflow, and then discuss how to write code or use Windows Performance Monitor to monitor connection pooling.

As I discussed in the article swimming in the. NET Connection Pool (InstantDoc ID 38356) published in May 2003, when you use connection pooling, you need to know a lot more about scalability and performance. Keep in mind that you need to monitor and manage two basic factors: the number of connections per pool and the number of connection pools. In a valid production system, the number of pools is usually very small (1 to 10), and the total number of connections in use is very small (less than 12). A valid query can be completed in less than a second and disconnected. Therefore, even if hundreds of customers access your Web site at the same time, a relatively small number of connections are often sufficient to handle the entire load. In order for your application to run efficiently, you must have the connection resources under your control and monitor the state of the pool, so that you receive some kind of warning before the monitoring pool overflows and your customers start complaining (or leaving your Web site).

Why the connection pool overflow occurred.

People who participate in e-mail discussion groups often complain about how the application is "dragon" in the test and becomes "worm" when it is formed as a product. Sometimes, they will report that the application stops or hangs when about 100 clients are connected. Keep in mind 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 the application's connection requests until there is an idle connection. The application (and its users) treats this situation as a delay in entering a Web page or as an application deadlock. Let's start with a discussion of how this problem arises.

In Ado.net, the SqlClient. NET data Provider provides you with two ways to open and manage connections. First, you can use the DataReader object when you need to manually manage the connection. With this approach, your code constructs a SqlConnection object, sets the ConnectionString property, and then uses the open method to open the connection. After the code completes DataReader, you want to close the SqlConnection before the SqlConnection object stops functioning. To process a rowset, you can pass DataReader to another routine in your application, but you still need to ensure that DataReader and its connections are turned off. If you do not close SqlConnection, the code "leaks" the connection for each operation, and the connection pool accumulates, and the overflow occurs. Unlike in ADO and Visual Basic (VB) 6.0, the. NET * * * * * collector does not close SqlConnection and cleans you. Listing 1, which I'm going to discuss later, shows how to open the connection and build DataReader to bring the row set back from a simple query to exert pressure on the connection pool.

You may also experience problems using the DataAdapter object. DataAdapter Fill and Update methods can be opened automatically

Use less than five connections to handle hundreds of thousands of clicks per day.

The routines in callout A Create the SqlConnection object and the SqlCommand object, set the CommandText, and open the connection. The code in callout B then determines whether to use commandbehavior.closeconnection when executing DataReader, depending on which CheckBox controls the user has selected on the Web form.

In the code for callout C, I specify whether to bind the DATAREADER rowset to the DataGrid, or whether to loop through the entire rowset. Code tests that mark C what happens when you arrive at the end of a rowset that is passed back from the data provider through DataReader.

Now, I use the code in callout D to specify whether to close the connection manually or let some other action (for example, data binding) do the job. Frankly, closing the connection manually is usually the safest, so you can be sure that the connection will not be orphaned.

If the code successfully runs to this step, I have successfully opened and closed 110 connections. However, if there is a problem, the exception handler in the code for callout E catches the exception (usually Timeout) as a InvalidOperationException, which is the response of the connection pool when it is full ado.net.

Table 1 summarizes how each option makes a routine run successfully or fails. Note that if you do not set the commandbehavior.closeconnection option, your operation will eventually fail-even if you are using bound controls. Even if you use this option, the process still fails if you do not use a complex bound control, or if you do not manually close SqlDataAdapter or SqlConnection.

When I finished running these sample applications, I have generated more than 1000 pooled connections-all of the connections are orphaned. Although the SQL Server user connection count is 0, it leaves approximately 40 connection pools. The orphaned pool will not disappear until I reboot the system.

The sample application I used for this test includes routines that use DataAdapter to return rows. Unless you manage the connection manually, DataAdapter will open and close the SqlConnection object correctly, so you are unlikely to experience an orphaned pool connection. However, if your application uses both DataReader and DataAdapter, you may find that if a connection is associated with an DataReader that is not closed, DataAdapter cannot run the query against the connection.

determine when the connection pool reaches the maximum number of connections

As I discussed in the swimming in the. NET Connection Pool, when the connection pool reaches the maximum number of connections that you specify through the max Pool Size ConnectionString option, ado.net blocks any subsequent open amounts An attempt to connect outside. If a connection becomes available before the time that you specify in the ConnectionTimeout option, the. NET data provider passes a pointer to the connection to your application to return the control to the application. However, if no connection is released in a timely manner, the connection request throws a InvalidOperationException exception.

Now you must decide what action to take, and I do not recommend that you tell the user that you have exhausted all connections. Some applications notify users that the system is busy helping other customers, and that users are advised to visit them later. Other applications play a piece of animation, informing the user that the system is not yet deadlocked, but is busy processing their requests. At the same time, your code tries to retry the operation. In all cases, you should

Record these failures to help diagnose the crux of the problem and record that you have exhausted resources.

Monitoring connection Pooling

You have turned on and off a connection and now you want to know if the connection is still open. You can use several methods to determine how many connections are still open and what they are doing:

•

Run sp_who or Sp_who2. These system stored procedures return information from the Sysprocess system table, which displays the status of all worker processes and their related information. Typically, you will see that each connection has a server process ID (SPID). If you name your connection by using the application name parameter in the connection string, you will easily find a working connection.

•

Use SQL Server Profiler with the SQLProfiler tsql_replay template to track open connections. If you are familiar with Profiler, this method is easier than polling by using sp_who. Use Performance Monitor to monitor pools and connections. I'll discuss this method later. • Monitor performance counters in your code. You can use routines to extract counters or to monitor the status of connection pools and the number of connections that have been established by using the new. NET PerformanceCounter control. Both of these methods are included in the sample application that you can download from http://www.sqlmag.com. Now we'll discuss how to find the connection pool counters and how to use these monitoring methods. where is the connection pool counter. to monitor connection pool counters, you must monitor the systems in which ado.net creates and adds these counters. If you are connecting from a remote system, Ado.net does not always create a pool on a Microsoft IIS server or SQL Server; it creates a pool on a system where the Ado.net code runs. This system can be a remote Windows or middle-tier system running IIS, a Web application, or a Web service. Instead, the SQL Server performance counter is on the SQL Server system-not the client. use Performance Monitor to monitor the pool. If you use the Microsoft Management Console (MMC) Windows 2000 System Monitor snap-in, you can graphically represent SqlClient counters by selecting. NET CLR Data from the performance object Drop-down list, as As shown in Figure 1. Note that you can monitor all processes by selecting the _global_ counter instance, or you can view a specific instance-each pool generates its own set of monitors. Performance Monitor can list these counters and provide them as instances of selected performance objects. However, Performance Monitor does not expose these counters unless there is an instance that requires them to be monitored. For example, Figure 1 shows a. NET CLR Data performance object, but no specific instance is listed. This means that you must create at least one connection so that the _global_ instance appears along with a specific instance of each process. This behavior is a problem for your code, and you will not be able to use the PerformanceCounter control to return any of these counters until ado.net creates them when the connection is opened. So this rule is a bit of a dilemma. When you use this method, an exception is thrown because a valid counter instance is missing-you are ready to catch the exception at this point. You can also monitor the number of open connections by using the SQL Server performance counter user connections. The counter is listed under SQL server:general Statistics in the Performance object Drop-down list. I like to monitor the User connections value and some selected. NET CLR Data SqlClient counters (which I'll discuss later) because I can get the information I need without worrying about the instance. use code to monitor performance counters. when you need to monitor connection pooling programmatically, you can write code to monitor performance counters managed by SqlClient-these counters are the same as those provided by the MMC Windows NT Performance Monitor snap-in. Writing code to perform monitoring seems like a daunting thing to do. However, I have provided snapshots of routines that extract these counters from the internal work of the SqlClient provider (as one of the downloadable programs provided in this article). You can write code that examines the five counters shown in table 2. By leveraging these five counters, you can monitor connection pooling in real time.. NET expects you to provide a category-replicated performance object-in Performance Monitor and select the appropriate counters from those that are registered with the system. To access the SqlClient counter, set the category to. NET CLR Data. use the PerformanceCounter control. you may find it easier to add PerformanceCounter to your application form at design time than to write code to access performance counters. To use the PerformanceCounter control, select a PerformanceCounter from the Visual Studio. NET Toolbox Components menu, drag it to your application form, and then set the properties, as shown in Figure 2. These controls work in Web forms and WinForms applications. Because the PerformanceCounter control provides a convenient Drop-down list, you can see any kind of performance counter category, counter name, and specific instance at design time-except for the instance that you will be running. This means that you must use the method shown in Figure 2 to capture the appropriate instance of the pool that your application is using. To avoid this problem, I choose _global_ instance. Again, this method assumes that an application has created at least one pool, so you need to prepare ado.net to throw an exception when there is no counter instance, as it would throw an exception if no pool connection exists. be aware of inaccurate pool counts. because the. NET Framework 1.1 unresolved error exists in the SqlClient. NET data provider, the performance counter incorrectly indicates that the pool "still exists" when the pool is actually deleted. I can verify that the pool no longer exists by ending the MMC Performance Monitor snap-in and then ending Visual Studio. NET. These steps indicate that the. NET data provider correctly deletes the connection pool at the end of the process that created the connection pool. Obviously, this inaccuracy reduces the effectiveness of performance counters in the monitoring pool, so I hope Microsoft will solve this problem in the future. What the counter does not display One problem you may face is that you cannot see the configuration of each pool from the counter or the SqlClient property. The ConnectionString of each SqlConnection object holds the keys set by these pools. Because you cannot rely on the default settings, it is difficult to determine whether the pool is almost full or difficult to use. This will be another handy feature of future versions of Ado.net. However, assuming you know the values of the individual connection pool ConnectionString parameters, the code in Listing 1 makes it easy to set up a timer to check the specific pool you created and report a percent usage. The monitoring application then alerts you so that you can resolve the problem and prevent overflow. Finally, keep in mind that the method used by ado.net differs from COM based ADO. Visual Basic. NET completely changes the way you discard objects and no longer ensures that the Connection object is closed when it stops functioning. Make sure that the SqlConnection object (or any Connection object) is closed before it stops functioning. Connection pooling is a very powerful feature that can improve the performance of your application. But if you're not a great lifeguard, your connection pool can be a hazard rather than an advantage. I hope the approach discussed in this article will help you effectively monitor your connection pool and meet the needs of your users.