. Net connection pool

Most ADO. NET data providers use connection pools 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 the Windows System 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 very small (less than 12) A valid query can be completed in less than one second and the connection is closed. 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 applications are "dragon" in tests and become "worms" in product formation. 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 over 100 connections from the pool, ADO. net will queue application connection requests until there is idle connection. 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 need to 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 will not close sqlconnection for you and clean it. Listing 1 I will discuss later shows how to open the connection and generate a datareader to return the row set from a simple query to exert 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.. Net does 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 connections from. NET applications. 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 an application as you used the sqlclient ADO. NET data provider.

Indicates datareader

Isolated connections and overflow pools are serious issues, which are common according to the number of newsgroups discussing these issues. These problems are most likely caused by datareader. To test datareader behavior, I have compiled a Windows system 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 using the executereader method of the sqlcommand object to execute queries 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. Are these developers' views completely incorrect? 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? What is the read method of datareader? If false is returned, the connection is disabled automatically. However, if you bind to a complex binding control (for example, DataGrid), the control will close datareader and connection? The prerequisite is 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 sample 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 more connections 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 (aka the pooler) 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 enough for a query with the same speed as the query. 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 testing for labeling c what happens when you reach the end of the row set passed back from the data provider through datareader.

Currently, I use the code in Mark D to specify whether to manually close the connection or to allow some other operations (such as data binding) to complete this task. Frankly speaking, disabling a connection manually is usually the safest, so you are sure the connection will not be isolated.

If the code runs successfully in this step, it indicates that 110 connections have been successfully opened and closed. However, if something goes wrong, 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. Please note that if you do not set the commandbehavior. closeconnection option, your operation will eventually fail? This is true even when the control is bound. 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.

The sample application I used for this test includes a routine that uses dataadapter to return rows. Unless you manually manage the connection, dataadapter correctly opens and closes the sqlconnection object. Therefore, it is unlikely that you will 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 all subsequent attempts to open additional connections. If a connection becomes available before the time specified 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 all connections are not released in time, the connection request will cause an invalidoperationexception.

At present, you must decide the measures to take. I do not recommend that you tell the user that you have used all connections. Some applications will inform the user system that they are busy helping other customers and suggest users access the service 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. Currently, 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.

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 system or intermediate layer system that runs IIS, web applications, or web services. Conversely, the SQL server performance counter is located on the SQL Server System? Instead of the client.

Use Performance Monitor to monitor the pool. If you use Microsoft Management Console (MMC) Windows System 2000 system monitor management unit, you can select from the performance object drop-down list ". net CLR data "to represent sqlclient counters with images, as shown in figure 1. Please note that you can monitor all processes by selecting the _ global _ counter instance, or can you 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, performance monitors do not disclose these counters unless they are required to monitor instances. 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 all the counters until ADO. Net creates these counters when opening the connection. Therefore, this provision is a bit difficult. When you use this method, an exception occurs because a valid counter instance is missing? Capture exceptions.

You can also use the SQL server performance counter "user connections" to monitor the number of opened connections. This 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 ones. net CLR data sqlclient counter (I will discuss it later), because I can obtain the information I need without worrying about the instance.

Use code to monitor performance counters. When you need to monitor the connection pool programmatically, can you write code to monitor the 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 expects you to provide a category in the performance monitor? Duplicate performance object? Select an appropriate counter from the counters registered to the system. To access the sqlclient counter, set this category to ". Net CLR data ".

Use the performancecounter control. 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 all the performance counter categories, counter names, and specific instances during design? Except for the instances 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 created at least one pool, so you need to make sure that there is no counter instance when ADO. net exception preparation, 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 Management Unit and then ending Visual Studio. NET. These steps are described ,. . NET data provider will delete the connection pool correctly 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 settings 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 very difficult to determine that the pool is almost full or very difficult to use. This will become another convenient feature of ADO. Net in future versions.

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

Finally, remember that the method used by ado.net is different from that of 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 all connection objects) is disabled before it stops.

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