ADO.NET Connection Pooling

at a Glance


 

    Home

Table of Contents

  • ADO.NET Connection Pooling at a Glance
  • Connection Pool Creation
  • Connection Pool Deletion / Clearing Connection Pool
  • Controlling Connection Pool through Connection String
    • Sample Connection String with Pooling related keywords
  • Simple ways to View Connections in the pool created by ADO.NET
  • Common Issues/Exceptions/Errors with Connection Pooling
  • Points to Ponder
  • Other Useful Reads/References on Connection Pooling
  • Wrapping up

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 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 your any client application. 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 intern means that most of the time we want to connect to same database server, same database, and with 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 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 faster comparatively.

Let us see Connection Pooling Creation Mechanism in more detail.

Connection Pool Creation

Connection Pool and Connection String goes hands in hands. Every connection pool is associated with distinct connection string and that too, it is specific to the application. What in turn 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 exact match for the connection string, in the same app domain and process. If such 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, new connection is created and added to the pool. This way, new connections keeps on adding to the pool till Max Pool Size is reached, after that when ADO.NET gets request for further connection, it waits for Connection Timeout time and then errors out.

Now the next question arises is - How any connection is released to 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() method 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 becomes 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 very first time, and 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" for details of 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.

Name

Default

Description

Connection Lifetime

0

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.

A value of zero (0) causes pooled connections to have the maximum connection timeout.

Connection Timeout

15

Maximum Time (in Secs) to wait for a free connection from the tool

Enlist

'true'

When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values are true, false, yes, and no.

Set Enlist = "false" to ensure that connection is not context specific.

Max Pool Size

100

The maximum number of connections allowed in the pool.

Min Pool Size

0

The minimum number of connections allowed in the pool.

Pooling

'true'

When true, the SQLConnection object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.

Incr Pool Size

5

Controls the number of connections that are established when all the connections are used.

Decr Pool Size

1

Controls the number of connections that are closed when an excessive amount of established connections are unused.

* 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 is 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

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 a 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 connection in the pool is corrupted.

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

For Oracle : Open the 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 persists.

7. Now Reset the IIS. You can do that by execute 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 connection 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. 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, performs the desired database operation, but we do not close the connection explicitly. Internally it cannot be used as available valid connection from pool. The application would have to wait for GC to claim it, until then it is not marked as available from 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 that 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 MS SQL Server


This occurs when MS SQL Server 2000 encounter 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 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 MS 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 MS SQL Server,

a. In the connection string at the 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.

b. 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(s):

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

Other Useful Reads/References on Connection Pooling

1. http://samples.gotdotnet.com/QuickStart/howto/doc/adoplus/ connectionpooling.aspx

2. ADO.NET Connection Pooling Explained :::: http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

3. The .NET Connection Pool Lifeguard :::: http://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx

Wrapping up

In nutshell, Connection pooling can increase the performance of any application by using active connections of the pool for consecutive requests, rather than creating a new connection each time. By default, ADO.NET enables and uses Connection Pooling due to its positive impact. And at the same time, the developer who is the best judge of his/her application, can configure the connection pooling features, or can even switch it off, based on the applications need by simply using power keywords of connection string.

Please spare some time to rate and provide feedback about this article. Your couple of minutes can help in enhancing the quality of this article.