Prevent the inundated application of the pool overflow
William vaughn
Most ADO.NET data providers use the connection pool to improve the performance of the application built around the .NET structure that is disconnected by Microsoft. The application first opens a connection (or get a connection handle from the connection pool), then runs one or more queries, then process the routine, and finally release the connection back to the pool. If there is no connection, these applications will spend many additional times to open and close connections.
When you use the ADO.NET connection pool to manage web-based applications and client / server web service applications, your customers usually get faster connection and better overall performance. However, what happens when you suddenly poured in a large number of customers who wish to connect at the same time on your application or Web site? Your app will "sink" or "swim"? Like a lifeguard, you need to carefully monitor the connecting pool to maintain its good performance and prevent the connection pool from overflow. We first explore the reason why the pool may overflow, then discuss how to write code or use the Windows Performance Monitor to monitor the connection pool.
As discussed in "Swimming In the .NET Connection Pool" in May 2003, you need to know many more information about scalability and performance when you use the connection pool. Keep in mind that you need to monitor and manage two basic factors: the number of connections to each pool and the number of connection pools. In an effective production system, the number of pools is usually rare (1 to 10), and the total number of connections in use is small (less than 12) effective queries only in less than one second. Complete, and disconnect. Therefore, even hundreds of customers access your Web site at the same time, relatively few connections are often sufficient to handle the entire load. In order to make your application run efficiently, you must make the connection resource under your own control, and to monitor the status of the pool, so that the monitoring pool overflows and your customers start complaining (or leave your website) You will receive some warning.
Why do connect pool overflows?
People who participate in the email discussion group often complain how the application is "Dragon" in the test and become "insects" when the product is formed. Sometimes they will report that when they are connected to about 100 clients, the application will stop or hang. Remember, the default connection in a pool is 100. If you try to open more than 100 connections from the pool, ADO.NET will wait for the application to wait until there is an idle connection. Applications (and their users) treat this as a latency that enters the web page or is considered a deadlock. Let us first discuss how this problem is produced.
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 method, your code will construct a SQLConnection object, set the Connectionstring property, and then use the Open method to open the connection. When the code completes DataReader, turn off the SQLConnection before the SQLConnection object is stopped. To handle routines, you can pass DataReader to another routine in your application, but still need to make sure DataReader and its connections are closed. If you don't close the SQLConnection, the code will "leak" each operation connection, so the connection pool is accumulated to the connection, and finally overflows. Unlike the situation in ADO and Visual Basic (VB) 6.0, .NET Garbage Recycler does not turn off SQLConnection for you and cleans up. Listing 1 will be discussed later, show how to open the connection and generate DataReader to return a rowset from a simple query to apply pressure to the connection pool. You may also encounter problems when using the DataAdapter object. The DataAdapter Fill and Update methods automatically open the connection of the DataAdapter object and turn off the connection after the data I / O operation is completed. However, if the connection is already open when performing a Fill or Update method, then ADO.NET does not turn off the SQLConnection after the method is executed. This is another chance to connect "leak".
In addition, you can also create a connection from the .NET application using COM-based ADO. The ADO uses the same way as ADO.NET into a pool, but it is not possible to provide a way to monitor the connection pool from the application as you have used the SqlClient ADO.NET data provider.
DataReader
Isolated connection and overflow pools are serious problems. They are very common based on the quantity of newsgroups discussed in these issues. These problems most likely caused by DataReader. In order to test the behavior of DataReader, I wrote a Windows Form (WinForms) sample application that highlights the Commandbehavior.closeConnection option. (You can enter instantdoc ID 39031 on http://www.sqlmag.com to download this application). You can set this option when using the ExecuteReader method of the SQLCommand object to perform queries and return to DataReader. My test application shows that if the 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, the application will lead to an exception.
Some developers persist that if you set up the Commandbehavior.CloseConnection option, DataReader and its associated connections are automatically turned off when DataReader completes the data read. The views of these developers are incomplete - only when you use a complex binding control in the ASP.NET web application, this option works in this way. The entire DataReader result is concentrated to the end of its rowset (that is, when Dr.Read - DataReader's READ method - returns FALSE) is not enough to trigger the automatic shutdown of the connection. However, if you are bound to a complex binding control (for example, DataGrid), the control will close DataReader and connection - Prerequisites are you set up the commandbehavior.closeConnection option. If you perform a query by using another ExecuteE method (for example, ExecuteScalar, Execute Reader), you will need to be responsible for opening SQLConnection objects, and more importantly, turning off the object at the end of the query. If you have forgotten to close, the isolated connection will accumulate quickly.
Monitor connection
In order to test the isolated connection and an overflow connection pool, I wrote a sample application for a web form. The method used by this application is usually used in the same way as you return data from queries. (You can download the WinForms version of this code on http://www.sqlmag.com.)
I use the code in Listing 1 to open and close the connection to the Web Form application. The routines in the label A are created for 110 new SqlConnection objects, open, and execute queries - more than 10 connections than the default pool. You must close and discard all of these connections before leaving the routine. If this is not done, the SqlConnection object will be isolated along with the associated pool. Aka The Pooler off the database connection but does not turn off the pool connection. I set the connection pool size to 10 so that the program fails faster - if the program fails. Typically, 10 connections are enough for a running speed like this query. Many developers have a busy Web site that uses less than five connections to handle hundreds of thousands of clicks per day.
The routines labeled A Create a SqlConnection object and a SQLCommand object, set CommandText, and open the connection. Then, the code in the label B determines whether to use Commandbehavior.CloseConnection when performing DataReader, depending on which CheckBox controls are selected on the web form.
In the code labeled C, I specify whether to bind the DataReader routine to the DataGrid, or whether to loop throughout the line. Take a Code Test of the Calvation When you arrive at the end of the rowset that passed from the DataReader from the data provider, it will happen.
Now, I use the code in the label D to specify whether to handle the connection or let some other actions (for example, data binding) to do this. Frankly, closing the connection in handmade is usually the safest, so you can affirm that the connection will not be isolated.
If the code is successfully running to this step, I have successfully opened and closing 110 connections. However, if there is a problem, the exception handler in the code that is marked E will capture exception (usually timeout) as an InvalidOperationException, which is a way to respond to ADO.NET when the connection pool is full. Table 1 summarizes each option to enable routines to run or fail. Note that if you don't set a Commandbehavior.CloseConnection option, your operation will eventually fail - even if the binding control is used. Even if you use this option, if you don't use a complex binding control, or if you do not manually turn off SqlDataAdapter or SqlConnection, the process will still fail.
When I ended the run of these sample applications, I have generated more than 1,000 pool connections - all connections are in an isolated state. Although the "SQL Server user connection" count is 0, it leaves approximately 40 connection pools. The isolated pool will not disappear before I re-boot the system.
The sample application I used for this test includes routines that use DataAdapter to return row. Unless you manage the connection, DataAdapter will correctly open and close the SqlConnection object, so you are less likely to meet isolated pool connections. However, if your application uses DataReader and DataAdapter simultaneously, you may find that if a connection is associated with an unstably closed DataReader, DataAPter cannot run a query for the connection.
Determine when the connection pool reaches the maximum number of connections
As I discussed in the article "Swimming In The .NET Connection Pool", when the connection pool reaches the maximum number of connections you specified by the Max Pool Size Connectionstring option, ADO.NET will block any attempts that subsequently open additional connections. . If a connection is available before you are available before the time specified by the "ConnectionTimeout option, the .NET data provider will pass a pointer to the connection to your application to return the control to the application. However, if not Release any connection in time, the connection request will trigger an InvalidOperationException exception.
Now you have to decide the measures to take, I don't recommend you to tell the user that you have used all connections. Some applications will notify the user that the system is busy helping other customers and suggests that users will be accessed later. Other applications play a movie that inform the user that the system has not yet dead, but is busy with their request. At the same time, your code re-attempts. In all cases, you should record these faults to help diagnose the problem of the problem, and record you have exhausted resources.
Monitor connection pool
You have turned on and off, and now you want to know if the connection is still open. You can use several ways to determine how many connections are still open, and what kind of operation 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 working processes and their information. Often, you will see that each connection has a server process ID (SPID). If you are named your connection by using the Application Name parameter in the connection string, you will easily find the work connection.
• Track the open connection with the SQL Server event probe with the SQLProfiler TSQL_Replay Template. If you are familiar with the event probe, this method is easier than polling by using sp_who. • Use performance monitors to monitor pools and connections. I will discuss this method later.
• Monitor Performance Counters in your code. You can use the routine to extract the counter or monitor the status of the connection pool and the number of established connections by using the new .NET PerformanceCounter control. Both methods include in the sample application you can download from http://www.sqlmag.com.
Now we will discuss how to find the connection pool counter, and how to use these monitoring methods.
Where is the connection pool counter? To monitor the connection pool counter, you must monitor the system in which ADO.NET creates and adds these counters. If you connect from a remote system, ADO.NET does not always create pools on the Microsoft IIS server or SQL Server; it creates pools on the system running in ADO.NET code. This system can be a remote Windows or intermediate layer system running an IIS, a web application, or a web service. Instead, the SQL Server Performance Counter is located on the SQL Server system - instead of the client.
Use performance monitors to monitor pools. If you use the Microsoft Administration Console (MMC) Windows 2000 System Monitor management unit, you can represent the SQLClient counter with graphics from the PERFORMANCE drop-down list, as shown in Figure 1. Note that you can monitor all processes by selecting the _Global_ counter instance, or you can view a particular instance - generate your own set of monitors per pool. Performance Monitor can list these counters and provide them as an instance of the selected performance object. However, performance monitors do not disclose these counters unless there is an instance that requires them to monitor. For example, Figure 1 shows the .NET CLR DATA performance object, but does not list a specific instance. This means you have to create at least one connection so that _Global_ instance appears together with each process specific instance. This behavior is a problem for your code; you will not be able to use PerformanceCounter controls to return any counter until ADO.NET creates these counters when the connection is opened. Therefore, this rule is really a bit of difficult. When you use this method, because of the lack of a valid counter instance, an exception is caused - At this point you want to capture an exception.
You can also monitor the number of open connections by using the SQL Server Performance Counter "User Connections". This counter is listed under the 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 (I will discuss this later), because I can get the information I need, but don't worry example.
Use code to monitor performance counters. When you need to monitor your connection pool in programming, you can write code to monitor performance counters managed by SqlClient - the counter provided by the MMC Windows NT Performance Monitor management unit is the same. The code to write to perform the monitor seems to be a fearful thing. But I have provided a snapshot of the routine of these counters from the internal work of the SQLClient provider (as one of the downloadable procedures provided herein).
You can write the code for checking the five counters displayed by Table 2. By using these five counters, you can monitor the connection pool in real time. .NET expects that you will provide a category-copyful Performance Object in the Performance Monitor - and select the appropriate counter from those registered to the system. To access the SqlClient counter, set this category to ".NET CLR DATA". Use the PerformanceCounter control. You may find that adding PerformanceCounter to add PerformanceCounter to access your application form to access performance counters easier than manually write code. To use the PerformanceCounter control, select a PerformanceCounter from the Visual Studio .Net Toolbox Component menu, drag it to your application form, then set the properties, as shown in Figure 2. These controls work in web forms and WinForms applications.
Because PerformanceCounter controls provide a convenient drop-down list, you can see any performance counter category, counter name, and specific instances when designing - except for instances you want to run. This means that you must use the method shown in Figure 2 to capture the appropriate instance of the pool that the application is using. In order to avoid this problem, I choose _Global_ instance. Again again, this method assumes that an application has created at least one pool, so the ADO.NET triggers an exception when there is no counter instance, just like it will cause an exception when there is no pool connection.
Pay attention to inaccurate pool counts. Because the SQLClient .NET data provider exists .NET Framework 1.1 has not been resolved, the performance counter is incorrectly indicating the pool "still exists" when the pool actually deletes. I can verify that the pool has no longer exists by ending the MMC performance monitor management unit and then ending Visual Studio .NET. These steps description, the .NET data provider will delete the connection pool when the process of creating the connection pool. Obviously, this inaccuracicity reduces the effectiveness of performance counters in monitoring pools, so I hope Microsoft will solve this problem in the future.
The counter does not display
One problem you may face is to see the configuration of each pool from the counter or SQLClient property. Connectionstring of each SqlConnection object holds the key sets of these pools. Because you can't rely on default settings, it is difficult to determine that the pool is almost full or difficult to use. This will become another convenient feature of the future version of ADO.NET.
However, suppose you know the values of each connection pool Connectionstring parameter, take advantage of the code in Listing 1, you can easily set a timer to check the specific pool you created and report the use percentage. Then, the monitoring application will make an alert to you so you can solve the problem and prevent overflow.
Finally, remember that the method used by ADO.NET is different from COM-based ADO. Visual Basic .NET completely changes the way to abandon the object and no longer make sure the Connection object is turned off when the action is stopped. Make sure the SqlConnection object (or any Connection object) is turned off before stopping.
The connection pool is a very powerful feature that improves the performance of the application. But if you are not an excellent lifeguard, your connection pool will become a hazard rather than a advantage. I hope this paper will help you monitor the connection pool and meet the needs of users.
