ADO.NET Connection Pooling at a Glance

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.And at the same time, the developer who is the best judge of his/her application, can configure the connection pooling.

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 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.

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 pool

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

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:

Create a Sample ASP.NET Web Application

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.

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.

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.

Now Reset the IIS. You can do that by executing the command iisreset on the Run Command.

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

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):

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.

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.

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.

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):

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.

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.

If you are working with .NET 1.1 and Microsoft SQL Server:

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.

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.

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:

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

Other Useful Reads/References on Connection Pooling

Wrapping Up

In a 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.

So what does this mean? It is apparent that that the presence of an actively opened connection made the test with multiple connection closing and opening finish a lot faster (2-3 times). The only possible explanation for me is that the connection pool is released each time there are no active connections. I have to make further investigations and read something like Pooling in the Microsoft Data Access Components. Or maybe hold a single opened connection just for the sake of keeping the pool alive. Pfu, this would be ugly, but still it is a good enough workaround! If anyone has a better idea, please share it.

In my project, using an MDB file, I experienced this, too.

You can easily check, whether the connection is pooled, by simply looking whether a correspoinding ".ldb" (lock) file is created in the folder where your ".mdb" database file is lockated.

Maybe I do misunderstand the real background, but it gives my app a lot more of speed to always keep the connection open.

/// <summary> /// http://immitev.blogspot.com/2005/01/where-is-ole-db-connection-pooling_28.html /// /// Quote: /// /// So what does this mean? It is apparent that that the presence of an /// actively opened connection made the test with multiple connection /// closing and opening finish a lot faster (2-3 times). The only possible /// explanation for me is that the connection pool is released each time /// there are no active connections. I have to make further investigations /// and read something like Pooling in the Microsoft Data Access Components. /// Or maybe hold a single opened connection just for the sake of keeping /// the pool alive. Pfu, this would be ugly, but still it is a good enough /// workaround! If anyone has a better idea, please share it. /// </summary> public sealed class ConnectionPoolGuardingConnection : IDisposable { #region Public methods. // ------------------------------------------------------------------

if ( _connection != null ) { // Caution: // Do not call Close or Dispose on an OleDbConnection, an // OleDbDataReader, or any other managed object in the // Finalize method of your class. In a finalizer, you should // only release unmanaged resources that your class owns // directly. If your class does not own any unmanaged // resources, do not include a Finalize method in your class // definition. For more information, see Garbage Collection.

I think the title of the article is somewhat misleading as ADO.NET does not have an inherent concept of pooling. None of the common ADO.NET classes supports any of the pooling stuff. You can see this in the System.Data.Common's DbConnection class which is intended to allow one to write code without knowing in advance which ADO.NET provider will be used.

If you get an ADO.NET provider from a 3rd party, most chances are that the "ADO.NET connection pooling" will be different. The Oracle provider that was developed with help from Microsoft re-implementes the pooling code. The base classes of the connection pooling, DbConnectionFactory, in the System.Data.ProviderBase is internal and is only derived by the 3 types of providers mentioned above.

This is in contrast with the way OLEDB and ODBC providers were getting pooling services from the platform.

Bottom line: the text in this article relates to the SQL Server, ODBC and OLEDB ADO.NET providers from Microsoft rather than to ADO.NET providers in general.

I'm not absolutely certain but I aggree and it makes sense.The basic reason is that i had extracted the code of System.Data with Reflector for another reason and form what i had seen in the connection part the above seems right.

I was very annoyed to learn that is how Microsoft approached the problem. It would have been much better to specify the base line, at least as explicit guidelines and then even provide a base implementation. The way it is done, 3rd parties are always at disadvantage compare with the Microsoft implementation. The only positive aspect here is that Reflector can at least show what Microsoft is doing so one can try to emulate their providers.

Personally, I much prefer the JDBC style of open specification with proper interfaces.

Short & Sweet. But don't you think if integrated security is used better to use connections with relatively small pool sizes rather than, going for user name and password to make the full use of pooling.

With Integrated Security there will be a connection pool for each user until and unless we disable it. I believe that Connection Pool would be more beneficial if it is at application (appdomain) level, so my preference would be to use username and password in the connection string. ThanksNS

I did want to point out that one of the cool things about ADO.NET is that the connection pooling works as part of the .NET Framework vs. relying on IIS or COM+ like classis ADO. This means that connection pooling is in use by default even in Windows Forms applications.

Also, in .NET 2.0, when there is a fatal connection error, the entire pool is flagged as invalid and will not return any of the existing connections in the pool back to the application. Instead, any requests for new connections will cause the creation of new connections in the pool. The old connections will be removed from the pool based on lifetime value.

i have created and ASP.NET application and connected in ORACLE,also i use TOAD to monitor behavior of the users during the work but in ASP.NET i can not locate which user work but instead of user i show w3wp.exe

Thats the same. You must be seeing "W3wp.exe" under program column when you use the query "Select * from v$session". These connection(s) gets created from your ASP.NET applicaton only.

Seems like you are using IIS 6.0 for hosting ASP.NET application and ODP.NET to connect to Oracle from your ASP.NET application.

Well, you can see actual user in the output only if you are using Integrated Security ( in the connection string). Mostly when we connect to Oracle from ASP.NET application we use username adn password. To me it seems like you are using username/password in your connection string, that is why you are not getting the actual user name. Most likely you must be getting "Network?Service" in the OSUSER column.

If you have more queries, please feel free to put them across. If you can provide the Connection String that you are using, that would be of great help during the discussion.

i create an application in C# with Windows Form and in oracle connectioni'm using ClientID properties to be able to show client_identifier during monitoring of session into v$session and it is perfect solution

but there's an problem if i used it from C# Asp.NET aplications ClientID doesn't workso from ASP.NET applications i show Session but i cannot show ClientID values it doesn't populate it

----------------------------------------------------------------------------------------------------------------------------C# Code in ASP.NET(this one doesn't work / grid is populated with info but ClientID doesn't display value who fill grid or run session) :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------string conn =ConfigurationSettings.AppSettings["connstr"].ToString();