ADO Connection Pooling at a Glance




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.
Connection Lifetime0When 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 byConnection Lifetime. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Connection Timeout15Maximum 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 aretrue, false, yes, and no. Set Enlist = "false" to ensure that connection is not context specific.
Max Pool Size100The maximum number of connections allowed in the pool.
Min Pool Size0The 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 Size5Controls the number of connections that are established when all the connections are used.
Decr Pool Size1Controls 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
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 withloginname 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.

Other Useful Reads/References on Connection Pooling

  1. ADO.NET Connection Pooling Explained
  2. The .NET Connection Pool Lifeguard