ADO.NET Connection Pooling at a Glance

ADO.NET Connection Pooling at a Glance

Establishing a connection with a database server is a hefty and high resource consuming process. If any application needs to fire any query against any database server, we need to first establish a connection with the server and then execute the query against that database server.

Not sure whether you felt like this or not; when you are writing any stored proc or a query, the query returns the results with better response time than the response time, when you execute that same query from any of your client applications. I believe, one of the reasons for such behavior is the overheads involved in getting the desired results from the database server to the client application; and one of such overheads is establishing the connection between the ADO.

Web applications frequently establish the database connection and close them as soon as they are done. Also notice how most of us write the database driven client applications. Usually, we have a configuration file specific to our application and keep the static information like Connection String in it. That in turn means that most of the time we want to connect to the same database server, same database, and with the same user name and password, for every small and big data.

ADO.NET with IIS uses a technique called connection pooling, which is very helpful in applications with such designs. What it does is, on first request to database, it serves the database call. Once it is done and when the client application requests for closing the connection, ADO.NET does not destroy the complete connection, rather it creates a connection pool and puts the released connection object in the pool and holds the reference to it. And next time when the request to execute any query/stored proc comes up, it bypasses the hefty process of establishing the connection and just picks up the connection from the connection pool and uses that for this database call. This way, it can return the results comparatively faster.

Let us see Connection Pooling Creation Mechanism in more detail.

Connection Pool Creation

Connection pool and connection string go hand in hand. Every connection pool is associated with a distinct connection string and that too, it is specific to the application. In turn, what it means is – a separate connection pool is maintained for every distinct process, app domain and connection string.

When any database request is made through ADO.NET, ADO.NET searches for the pool associated with the exact match for the connection string, in the same app domain and process. If such a pool is not found, ADO.NET creates a new one for it, however, if it is found, it tries to fetch the usable connection from that pool. If no usable free connection is found in the pool, a new connection is created and added to the pool. This way, new connections keep on adding to the pool till Max Pool Size is reached, after that when ADO.NET gets request for further connections, it waits for Connection Timeout time and then errors out.

Now the next question that arises is - How is any connection released to the pool to be available for such occasions? Once any connection has served and is closed/disposed, the connection goes to the connection pool and becomes usable. At times, connections are not closed/disposed explicitly, these connections do not go to the pool immediately. We can explicitly close the connection by using Close() or Dispose() methods of connection object or by using the using statement in C# to instantiate the connection object. It is highly recommended that we close or dispose (don‘t wait for GC or connection pooler to do it for you) the connection once it has served the purpose.

Connection Pool Deletion / Clearing Connection Pool

Connection pool is removed as soon as the associated app domain is unloaded. Once the app domain is unloaded, all the connections from the connection pool become invalid and are thus removed. Say for example, if you have an ASP.NET application, the connection pool gets created as soon as you hit the database for the very first time, and the connection pool is destroyed as soon as we do iisreset. We‘ll see it later with example. Note that connection pooling has to do with IIS Web Server and not with the Dev Environment, so do not expect the connection pool to be cleared automatically by closing your Visual Studio .NET dev environment.

ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.

Refer to the section "Simple Ways to View Connections in the Pool Created by ADO.NET"fordetails on how to determine the status of the pool.

Controlling Connection Pool through Connection String

Connection string plays a vital role in connection pooling. The handshake between ADO.NET and database server happens on the basis of this connection string only. Below is the table with important Connection pooling specific keywords of the connection strings with their description.

* Some table contents are extracted from Microsoft MSDN Library for reference.

Other than the above mentioned keywords, one important thing to note here. If you are using Integrated Security, then the connection pool is created for each user accessing the client system, whereas, when you use user id and password in the connection string, single connection pool is maintained across for the application. In the later case, each user can use the connections of the pool created and then released to the pool by other users. Thus using user id and password are recommended for better end user performance experience.

Sample Connection String with Pooling Related Keywords

The connection string with the pooling related keywords would look somewhat like this

Collapse | Copy Code

initial catalog=Northwind; Data Source=localhost; Connection Timeout=30; 
User Id=MYUSER; Password=PASSWORD; Min Pool Size=20; Max Pool Size=200; 
Incr Pool Size=10; Decr Pool Size=5;

Simple Ways to View Connections in the Pool Created by ADO.NET

We can keep a watch on the connections in the pool by determining the active connections in the database after closing the client application. This is database specific stuff, so to see the active connections in the database server we must have to use database specific queries. This is with the exception that connection pool is perfectly valid and none of the connections in the pool is corrupted.

For Microsoft SQL Server: Open the Query Analyser and execute the query : EXEC SP_WHO.

For Oracle : Open SQL Plus or any other editor like PL/SQL Developer or TOAD and execute the following query -- SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL.

All right, let us do it with SQL Server 2000:

1. Create a Sample ASP.NET Web Application
2. Open an instance of Query Analyzer and run the EXEC SP_WHO query. Note the loginname column, and look for MACHINENAME\ASPNET. If you have not run any other ASP.NET application, you will get no rows with loginname as MACHINENAME\ASPNET.
3. On Page load of default startup page, add a method that makes a database call. Say your connection string is "initial catalog=Northwind; Min Pool Size=20;Max Pool Size=500; data source=localhost; Connection Timeout=30; Integrated security=sspi".
4. Run your ASP.NET application
5. Now repeat Step 2 and observe that there are exactly 20 (Min Pool Size) connections in the results. Note that you made the database call only once.
6. Close the Web page of your Web application and repeat step 2. Observe that even after you closed the instance of the Web page, connections persist.
7. Now Reset the IIS. You can do that by executing the command iisreset on the Run Command.
8. Now Repeat Step 2 and observe that all the 20 connections are gone. This is because your app domain has got unloaded with IIS reset.

Common Issues/Exceptions/Errors with Connection Pooling

1. You receive the exception with the message: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached" in your .NET client application.

   This occurs when you try using more than Max Pool Size connections. By default, the max pool size is 100. If we try to obtain connections more than max pool size, then ADO.NET waits for Connection Timeout for the connection from the pool. If even after that connection is not available, we get the above exception.

   Solution(s):

   1. The very first step that we should do is – Ensure that every connection that is opened, is closed explicitly. At times what happens is, we open the connection, perform the desired database operation, but we do not close the connection explicitly. Internally it cannot be used as an available valid connection from the pool. The application would have to wait for GC to claim it, until then it is not marked as available from the pool. In such case, even though you are not using max pool size number of connection simultaneously, you may get this error. This is the most probable cause of this issue.
   2. Increase Max Pool Size value to a sufficient Max value. You can do so by including "Max Pool Size = N;" in the connection string, where N is the new Max Pool size.
   3. Set the pooling Off. Well, this indeed is not a good idea as connection pooling puts a positive performance effect, but it definitely is better than getting any such exceptions.

2. You receive the exception with the message: "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - Shared Memory Provider: )" in your ASP.NET application with Microsoft SQL Server.

   This occurs when Microsoft SQL Server 2000 encounters some issues and has to refresh all the connections and ADO.NET still expects the connection from the pool. Basically, it occurs when connection pool gets corrupted. What in turn happens is, ADO.NET thinks that the valid connection exists with the database server, but actually, due to database server getting restarted it has lost all the connections.

   Solution(s):

   1. If you are working with .NET and Oracle using ODP.NET v 9.2.0.4 or above, you can probably try adding "Validate Connection=true" in the connection string. Well, in couple of places, I noticed people saying use "validcon=true" works for them for prior versions on ODP.NET. See which works for you. With ODP.NET v 9.2.0.4, "validcon=true" errors out and "Validate Connection=true" works just fine.
   2. If you are working with .NET 2.0 and Microsoft SQL Server, You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.
   3. If you are working with .NET 1.1 and Microsoft SQL Server:
   1. In the connection string, at run time, append a blank space and try establishing the connection again. What in turn it would do is, a new connection pool would be created and will be used by your application, In the meantime the prior pool will get removed if it‘s not getting used.
   2. Do exception handling, and as soon as you get this error try connection afresh repeatedly in the loop. With time, ADO.NET and database server will automatically get in sync. Well, I am not totally convinced with either approach, but frankly speaking, I could not get any better workable solution for this so far.

3. Leaking Connections

   When we do not close/dispose the connection, GC collects them in its own time, such connections are considered as leaked from pooling point of view. There is a strange possibility that we reach max pool size value and at that given moment of time without actually using all of them, having couple of them leaked and waiting for GC to work upon them. This would actually lead to the exception mentioned above, even if we are not using max pool size number of connections.

   Solution:

   1. Ensure that we Close/Dispose the connections once its usage is over.

原文摘自:http://www.codeproject.com/Articles/17768/ADO-NET-Connection-Pooling-at-a-Glance