Prevents pool overflow that can overwhelm applications

Most ADO. NET data providesProgramUse a connection pool to Improve the Performance of applications built around Microsoft's disconnected. net structure. The application first opens a connection (or obtains a connection handle from the connection pool), then runs one or more queries, processes the row set, and finally releases the connection back to the connection pool. Without a connection pool, these applications will spend a lot of extra time opening and closing connections.

When you use ADO. net connection pool to manage the connection between Web-based applications and Client/Server Web service applications, your customers usually get faster connections and better overall performance. However, what happens when your applications or web sites suddenly flood into a large number of customers who want to connect at the same time? Will your application sink or swim "? Like a lifeguard, You need to carefully monitor the connection pool to maintain its good performance and prevent connection pool overflow. We will first discuss the possible causes of connection pool overflow, and then discuss how to writeCodeOr use Windows performance monitor to monitor the connection pool.

As I published in May 2003, "Discovery Ming in. as discussed in the net connection pool (instantdoc ID 38356), you need to know a lot about scalability and performance when using the connection pool. Remember, you need to monitor and manage two basic factors: the number of connections managed by each pool and the number of connection pools. In an effective production system, the number of pools is usually very small (1 to 10), and the total number of connections in use is also small (less than 12) A valid query can be completed in less than one 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 enough to handle the entire load. To make your application run effectively, you must keep connected resources under your control and monitor the status of the pool, you will receive a warning before the monitoring pool overflows and your customers start to complain (or leave your website.

Why is connection pool overflow?

People who participate in e-mail discussion groups often complain about how the application is a "dragon" in the test and become a "worm" when it is formed as a product. Sometimes they report that the application stops or hangs when about 100 clients are connected. Remember that the default number of connections in a pool is 100. If you try to open more than 100 connections from the pool, ADO. Net queues application connection requests until there are idle connections. Applications (and their users) consider this situation as a delay in accessing the Web page or as a deadlock in the application. Let's first discuss how this problem occurs.

In ADO. net, the sqlclient. NET data provider provides you with two methods to open and manage connections. First, you can use the datareader object when you need to manually manage the connection. In this way, your code constructs a sqlconnection object, sets the connectionstring attribute, and then uses the Open Method to open the connection. After the code completes datareader, you must disable sqlconnection before the sqlconnection object stops working. To process a row set, you can pass the datareader to another routine in the application, but you still need to ensure that the datareader and its connection are closed. If you do not close sqlconnection, the code will "leak" the connection for each operation, so the connection pool accumulates the connection and eventually overflows. Unlike in ADO and Visual Basic (VB) 6.0, the. NET garbage collector does not close sqlconnection and clean it for you. Listing 1, which I will discuss later, shows how to open a connection and generate a datareader to return a row set from a simple query to put pressure on the connection pool.

You may also encounter problems when using the dataadapter object. The dataadapter fill and update Methods automatically enable the connection of the dataadapter object and disable the connection after the data I/O operation is complete. However, if the connection is enabled when the fill or update method is executed, ADO. NET will not close sqlconnection after the method is executed. This is another opportunity for connection "leakage.

In addition, you can use com-based ADO to create a connection from A. NET application. Ado combines these connections into a pool in the same way as ADO. net, but it does not provide a way to monitor the connection pool from the application as you use the sqlclient ADO. NET data provider.

InstructionsDatareader

Isolated connections and overflow pools are critical issues. They are common based on the number of newsgroups discussing these issues. These problems are most likely caused by datareader. To test the datareader behavior, I have compiled a Windows form (winforms) sample application that highlights the commandbehavior. closeconnection option. (You can enter instantdoc ID 39031 on the http://www.sqlmag.com to download this application ). You can set this option when you use the executereader method of the sqlcommand object to execute a query and return datareader. My test application shows that if datareader (or sqlconnection) is not explicitly closed, the connection pool will overflow even if this option is used. When the number of connections requested by the Code exceeds the capacity of the Connection Pool, this application will cause an exception.

Some developers insist that if you set commandbehavior. closeconnection, datareader and its associated connections will be automatically closed when datareader completes Data Reading. These developers are not totally correct-this option works only when you use complex binding controls in ASP. NET web applications. Loop the entire datareader result set to the end of its row set (that is, when dr. Read-datareader's read method-returns false) is not enough to trigger the connection to automatically close. However, if you bind to a complex binding control (for example, DataGrid), the control will disable datareader and connection-provided that you have set the commandbehavior. closeconnection option.

If you execute a query by using another execute method (such as executescalar, executenonquery, and executexmlreader), you need to open the sqlconnection object. More importantly, close the object at the end of the query. If you forget to close the connection, the isolated connection will quickly accumulate.

Number of monitored connections

To test the isolated connection pool and the connection pool with overflow, I wrote a web form example application. This application uses the same method as the one you normally use to return data from a query. (You can download the winforms version of this Code on the http://www.sqlmag.com .)

I used the code in Listing 1 to open and close the connection to the web form application. The routine in Annotation A creates, opens, and executes queries for 110 new sqlconnection objects-10 connections more than the default pool size. You must close and discard all these connections before leaving the routine. Otherwise, the sqlconnection object will be isolated together with the associated pool connection. Ado. Net pool mechanism (akaPooler) Closes the database connection, but does not close the pool connection. I set the connection pool size to 10 so that the program fails faster-if the program fails. Generally, 10 connections are sufficient for a query that is as fast as the query speed. Many developers run busy web sites that use less than five connections to process hundreds of thousands of clicks each day.

Mark the routine in A to create the sqlconnection object and sqlcommand object, set commandtext, and open the connection. Then, mark the code in B to determine whether commandbehavior. closeconnection is used when datareader is executed. This depends on which checkbox controls are selected on the web form.

In the Code labeled C, I specify whether to bind the datareader row set to the DataGrid, or whether to loop through the entire row set. Code test with annotation C: What happens when you reach the end of the row set passed back from the data provider through datareader.

Now, I use the code in Mark D to specify whether to manually close the connection or let another operation (for example, data binding) to complete this task. Frankly speaking, manually disabling a connection is usually the safest, so you can be sure that the connection will not be isolated.

If the code runs successfully in this step, it indicates that I have successfully opened and closed 110 connections. However, if a problem occurs, the exception handler in the Code marked E will capture the exception (usually timeout) as invalidoperationexception, which is the response method of ADO. net when the connection pool is full.

Table 1 summarizes the methods by which the routine runs successfully or fails. Note that if you do not set the commandbehavior. closeconnection option, your operation will eventually fail-even if you use the bound control. Even if you use this option, the process will still fail if you do not use complicated binding controls or do not manually close sqldataadapter or sqlconnection.

After I finish running these sample applications, I have generated more than 1000 pool connections-all connections are isolated. Although the "SQL Server user connection" count is 0, about 40 connection pools are left. The isolated pool will not disappear until I reboot the system.

Examples of applications I used for this test include routines that use dataadapter to return rows. Unless you manually manage the connection, dataadapter correctly opens and closes the sqlconnection object. Therefore, you are unlikely to encounter an isolated pool connection. However, if your application uses both datareader and dataadapter, you may find that if a connection is associated with an unclosed datareader, dataadapter cannot run queries on the connection.

Determine when the connection pool reaches the maximum number of connections

Just as I was in "Discovery Ming in. as discussed in the net connection pool article, when the connection pool reaches the maximum number of connections you specified through the "Max pool size connectionstring" option, ADO. net will block any subsequent attempts to open additional connections. If a connection becomes available before the time specified by you in the connectiontimeout option ,.. NET data provider will pass a pointer to the connection to your application to return the control to the application. However, if no connection is released in time, the connection request will cause an invalidoperationexception.

Now you must determine the measures to take. I do not recommend that you tell the user that you have used up all connections. Some applications will inform the user system that they are busy helping other customers and suggest users access them later. Other applications play an animation, notifying users that the system is not deadlocked, but is busy processing their requests. At the same time, your code will try again. In all circumstances, you should record these faults to help diagnose the crux of the problem and record that you have exhausted resources.

Monitoring connection pool

You have enabled and disabled a connection. Now you want to know if the connection is still in the enabled status. You can use several methods to determine how many connections are still open and what operations are being performed:

•	Run sp_who or sp_who2. These system stored procedures return information from the sysprocess system table, which displays the status and related information of all worker processes. Generally, you will see a server process ID (spid) for each connection ). If you name your connection by using the application name parameter in the connection string, you can easily find a working connection.
•	Use the SQL Server event PROBE WITH THE sqlprofiler tsql_replay template to track opened connections. If you are familiar with the event probe, this method is easier than using sp_who for polling.
•	Use Performance Monitor to monitor pools and connections. I will discuss this method later.
•	Monitor the performance counters in the code. You can use a routine to extract counters or use the new. Net performancecounter control to monitor the status of the connection pool and the number of established connections. Both methods are included in the sample application that you can download from the http://www.sqlmag.com.

Now we will discuss how to find the connection pool counters and how to use these monitoring methods.

Where is the connection pool counter?To monitor the connection pool counters, you must monitor the systems in which ADO. Net creates and adds these counters. If you connect from a remote system, ADO. Net does not always create a pool on Microsoft IIS server or SQL Server; it creates a pool on the system where ADO. Net code runs. This system can be a remote Windows or intermediate layer system that runs IIS, web applications, or web services. On the contrary, the SQL server performance counter is located on the SQL server system, rather than on the client.

Use Performance Monitor to monitor the pool.If you use the Microsoft Management Console (MMC) Windows 2000 system monitor management unit, you can select ". net CLR data "to graphically represent sqlclient counters, as shown in figure 1. Note that you can select the _ global _ counter instance to monitor all processes, 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 the selected performance object. However, the performance monitor does not disclose these counters unless there are instances that require them to be monitored. For example, Figure 1 shows the. net clr data performance object, but does not list specific instances. This means that you must create at least one connection so that the _ global _ instance appears together with the specific instance of each process. This behavior is a problem for your code; you will not be able to use the performancecounter control to return any counters until ADO. Net creates these counters when a connection is opened. Therefore, this provision is a bit difficult. When you use this method, an exception is thrown because a valid counter instance is missing-capture exceptions must be prepared.

You can also use the SQL server performance counter "user connections" to monitor the number of opened connections. This counter is listed in the performance object drop-down list.SQL Server: General Statistics. I like to monitor the "user connections" value and some selected ones. net CLR data sqlclient counter (I will discuss it 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 the connection pool 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 the code to execute monitoring seems a daunting task. However, I have provided snapshots of the counter routines from the internal work of the sqlclient provider (as one of the downloadable programs provided in this article ).

You can write code to check the five counters displayed in table 2. By using these five counters, You can monitor the connection pool in real time .. Net. You are expected to provide a category in the Performance Monitor-replicatedPerformance object-Select an appropriate counter from the counters registered to the system. To access the sqlclient counter, set this category to ". Net CLR data ".

UsePerformancecounterControl.You may find it easier to add performancecounter to your application form at design time to access performance counters Than to write code manually. To use the performancecounter control, select a performancecounter from the "Visual Studio. NET toolkit components" menu, drag it to your application form, and set 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 performance counter category, counter name, and specific instance at design time-except for the instance you want to run. This means that you must use the method shown in Figure 2 to capture the appropriate instances of the pool that the application is using. To avoid this problem, I chose _ global _ instance. Once again, this method assumes that an application has already created at least one pool, so you need to make sure that there is no counter instance when ADO. net exception preparation, just as it will also cause exceptions when there is no pool connection.

Note the inaccurate pool count.Because the sqlclient. NET data provider has an. NET Framework 1.1 unsolved error, the performance counter will incorrectly indicate 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 when the process for creating the connection pool ends. Obviously, this inaccuracy reduces the effectiveness of performance counters in the monitoring pool, so I hope Microsoft will solve this problem in the future.

Content not displayed on the counter

One problem you may face is that you cannot view the configuration of each pool from the counter or sqlclient attribute. The connectionstring of each sqlconnection object stores the keys set for these pools. Because you cannot rely on the default settings, it is difficult to determine that the pool is almost full or difficult to use. This will become another convenient feature of ADO. Net in future versions.

However, if you know the connectionstring parameter values of each connection pool, you can easily set a timer to check the specific pool you created and report the usage using the code in Listing 1.Percentage. The monitoring application then sends you an alarm so that you can resolve the problem and prevent overflow.

Finally, remember that ADO. net uses different methods than com-based ADO. Visual Basic. Net completely changes the way to discard objects, and does not ensure that the connection object is disabled when it is stopped. Make sure that the sqlconnection object (or any connection object) is disabled before it stops.

The connection pool is a very powerful function that can improve the performance of applications. However, if you are not a good lifeguard, your connection pool will become a hazard rather than an advantage. I hope the methods discussed in this article will help you effectively monitor the connection pool and meet your needs.