Most people have heard of Connection Pooling and most applications make use of it, but not everyone understands what it is or how it works. To help shed some light on the topic, this article will present an in depth look at connection pooling and how it is implemented. After an explanation of connection pooling, this article will look at how it is enabled and configured in non traditional ASP (non .NET) applications and then at connection pooling in ASP.NET applications, pointing out similarities and differences between the two.

Connecting to a database is an expensive process, especially in terms of server resources such as memory. Connection pooling is a process that allows applications to re-use existing database connections instead of creating new ones for each attempted data access. A straightforward data access procedure would consist of creating a connection object, opening the connection to the data source, performing some sort of operation on a set of data, and then closing the connection. Let's assume that this type of operation is occurring from an ASP page on a high volume site. This rather simple operation would create a lot of server overhead from all the database connections that are being established. This scenario is what led to the concept of connection pooling. When an application uses connection pooling, a connection that is seemingly closed is not really closed; it is returned to the connection pool for re-use. By the same token, when an application "opens" a connection, it might not be establishing a new connection but instead re-using an existing connection from an established connection pool.

Since connecting to a database is a very expensive process, connection pooling can result in significant performance gains by promoting re-use of connections. Connection pooling allows connections to be shared among multiple components in a single application (.DLL's, the same MTS server package, IIS). A connection pool will be established for each application process, and all of the components in the application can draw connections from the application's pool. This enables stand-alone components in the same process to interact with each other without ever being aware of one another. Although there are some very obvious benefits to using connection pooling, there are a couple of scenarios in which you might want to avoid using connection pooling in your applications. You might want to consider avoiding connection pooling in your applications if they perform any of the following actions:

• Changing the default database
• Using the SET statement to change options(SET ROWCOUNT, SET SHOWPLAN, etc.)
• Creating temporary tables, or
• Creating stored procedures

The reason for avoiding these operations is that the next application that uses a connection from the pool will inherit the settings, tables, or procedures that have been established in the pool instead of the new settings, tables, or stored procedures you'd be expecting.

How Does it Work?

So how does connection-pooling work? From a low-level perspective, connection pooling is enabled by making a call to the SQLSetEnvAttr function (which is part of the odbc32.dll library) to set the SQL_ATTR_CONNECTION_POOLING attribute to either SQL_CP_ONE_PER_DRIVER or

• SQL_CP_ONE_PER_HENV. (The differences between the two settings are:)
• SQL_CP_ONE_PER_DRIVER - (a connection pool is supported for each driver)
• SQL_CP_ONE_PER_HENV - (a connection pool is supported for each environment).

Once a connection pool is established, the size of the pool grows based on requested resources. The pool grows dynamically and the size of the pool is limited only by memory constraints of the server. The length of time an inactive connection stays in the pool is a configurable property of the ODBC driver and can be set from the system registry or the ODBC Administration (see below). Note that odbc32.dll would not be directly accessible from ASP.NET. To make use of functions in the odbc32.dll, your .NET applications would have to use the DLLImportAttribute class (from the System.Runtime.InteropServices namespace) to make a function call to the unmanaged system API.

Enabling and Configuring Connection Pooling in non .NET applications

The ODBC driver manager controls ODBC connection pooling. To support connection pooling, the driver must be fully thread safe and connections must not have thread affinity. To be thread safe means that the driver can handle a call on any thread at any time. To not have thread affinity means that the driver is able to connect on one thread, perform some action on another, and disconnect on a third. To view and set connection pooling settings you have two options, you can use the ODBC Data Source Administrator ( Start -> Settings -> Control Panel -> Data Sources(ODBC)) or the Windows Registry. Let's look at the ODBC Data Source Administrator first.

Figure 1 - ODBC Data Source Administrator. The Connection Pooling tab is where the connection pooling behaviors of the driver can be controlled.

Figure 1 is a picture of the Connection Pooling tab of the ODBC Data Source Administrator. On this tab you can see a list of the drivers and something called Pool Time. Pool Time is the time an unused connection will stay in the ODBC Connection Pool before being destroyed. Drivers that have a value of 0 for this setting are not pooled (denoted by <not pooled>). The Pool Time setting corresponds to the CPTimeout value in the registry (we'll get to that in a moment). To set the Pool Time property, double click on the name of the driver. This will bring up a screen similar to Figure 2.

Figure 2 - Set Connection Pooling Attributes. From this dialog box, you can set the pool time property, the property that determines how long a connection stays in the pool before being destroyed. Choosing "Don't pool connections to this driver " will set the pool time property to 0.

To alter how long a connection stays in the pool simply change the value in the dialog box. If you do not want to pool connections to this driver select the "Don't pool connections to this driver " option or set the pool time property to 0.

ODBC drivers that can participate in connection pooling are smart. They can determine whether or not the database is available by checking the value of the SQL_ATTR_CONNECTION_DEAD attribute. SQL_ATTR_CONNECTION DEAD will return either

• SQL_CD_TRUE - the connection has been lost
• SQL_CD_FALSE - the connection is still active

If SQL_CD_FALSE is returned, the connection is active and it will hand out to an application. If however SQL_CD_TRUE is returned, the connection has been lost, and the driver will wait a certain amount of time before attempting to reconnect to the database. The Retry Wait Time specifies how long (in seconds) the driver will wait before attempting to re-connect to the database (in this example, the driver will wait 2 minutes before attempting to re-connect to the data source). Unlike the Pool Time property, which is unique for each driver, the Retry Wait Time is a global setting for all the drivers. The Perfmon section allows you to add counters for measuring connection-pooling performance to the Windows Performance Monitor. By default, the counters are disabled so if you want to monitor connection pooling in your applications you'd have to enable the counters first.

As mentioned briefly above, the Pool Time and the Retry Wait Time properties can also be set in the system registry. The Pool Time property corresponds to the CPTimeout value in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\[Driver Name]

The Retry Wait Time corresponds to the Retry Wait value in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling

Figure 3 - Controlling Connection Pooling Properties through the Registry. The CPTimeout value, which corresponds to the Pool Time property in the ODBC Administrator, can be found at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\[Driver Name]. The Retry Wait Time value is found at

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling

Connection Pooling vs. Resource Pooling in non .NET applications

Thus far we have discussed ODBC connection pooling, but if you are like the majority of ASP developers, your ASP pages don't benefit from ODBC Connection Pooling. This is because ADO is built on top of OLE DB and by default OLE DB does not support ODBC connection pooling; instead it supports something called resource pooling. Note that an ADO/OLE DB application can make use of ODBC connection pooling by using the ODBC API and making a function call to SQLSetEnvAttr. Although your ASP pages cannot access the ODBC API, you could create a COM component to encapsulate all of your application's data access operations and enable connection pooling inside the component (see code download for example of this). For an example on how to enable ODBC connection pooling from a Visual Basic ADO application see the link below.

http://support.microsoft.com/support/kb/articles/Q237/8/44.ASP?WROXEMPTOKEN=525328ZnJ2mwoqkbWtFrpx3GHY

Resource pooling and connection pooling are very similar, and in most cases the names "connection pooling" and "resource pooling" are often interchanged. There are some subtle differences though. The first difference between connection pooling and resource pooling is the amount of time a connection stays in the pool. If you will recall from above, a developer can set the CPTimeout property to control how long a connection stays in the pool with ODBC connection pooling. The amount of time connections stay in an OLE DB resource pool is not configurable and is set for 60 seconds. There is also a slight difference in how resource pooling is enabled. Enabling OLE DB resource pooling can be accomplished in 2 ways, either through the registry (similar to connection pooling) or by passing a value in the connection string. To enable OLE DB resource pooling through the registry, you have to set the OLEDB_SERVICES value to 0xffffffff. If you were using the SQL Server OLE DB Provider (SQLOLEDB), this value could be found at the following location in the registry:

HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}

The other way to enable resource pooling is by adding "OLE DB Services=-1 ;" to the connection string your application uses to establish a connection. This setting on the connection string will override any value set in the registry.

For more information on enabling session pooling in OLE DB applications, see the following link:

http://support.microsoft.com/support/kb/articles/Q228/8/43.ASP?WROXEMPTOKEN=525328ZnJ2mwoqkbWtFrpx3GHY

Note that ADO enables OLE DB resource pooling by default without having to adjust the registry or add anything to your connection string. So if your applications are using ADO, they are enjoying the benefits of OLE DB resource pooling. Also, Component Services (or MTS if you are using NT) maintains it's own resource pool. If you've registered your component in Component Services, it is enjoying the benefits of resource pooling.

.NET Connection Pooling

Now that we've fully explored how connection pooling worked in the days before .NET, we can now discuss connection pooling in .NET. For our discussion of .NET connection pooling, we'll be looking at pooling in both the SQL Server, .NET data provider and the OLE DB .NET data provider. Before we get into that though, let's have a quick look at connection scenario from a typical ASP page.

set objCN = Server.CreateObject("ADODB.Connection") 
cnString =
"Server=Power2000;Provider=SQLOLEDB;Database=DotNet;UID=sa;PWD=satest;"
objCN.Open cnString

The above code probably looks familiar to most people. There is one important thing to note here, the properties and values of the connection string. Let's say on a different ASP page you had the following code

set objCN = Server.CreateObject("ADODB.Connection") 
cnString =
"Server=Power2000;Provider=SQLOLEDB;Database=DotNet;UID=testuser;PWD=testuser;"
objCN.Open cnString

What's important to note here is that both of these connections would establish their own connection pools and they could not share connections from the same pool. This is because the connection strings are different (notice the different username and password). Each connection pool is associated with one distinct connection string. For a connection to draw from the pool, the connection string must match exactly what is in the pool. If no match is found, and pooling is enabled, a new pool will be created.

Connection Pooling in the SQL Server .Net Data Provider

.NET connection pooling is also based on distinct connection strings and pools will be established for each unique connection string. Unlike connection pooling in traditional ADO/ASP environments, .NET connection pooling can be largely controlled from the connection string. Let's look at a typical connection string for a .NET application that uses the SQL Server .NET data provider.

<%@Import Namespace="System.Data" %>
%@Import Namespace="System.Data.SqlClient" %

Dim objCn as new SQLConnection

cnString = "Server=Power2000;Database=H1Contractors;UID=sa;PWD=H12225;pooling=true;max & -
pool size=100;connection lifetime=60;"

objCn.ConnectionString = cnString
objCn.Open()

You'll notice right away there are some new keywords that can be associated with the connection string. The most obvious is the pooling keyword. The pooling keyword allows you to enable/disable connection pooling directly from the connection string. The default value is true. You can also set the maximum and minimum number of connections in a pool by using the max pool size and min pool size keywords. The default max pool size is 100 connections and the default min pool size is 0 connections. The connection lifetime keyword allows you to specify how long (in seconds) a connection stays in the pool (similar to the CPTimeout property we discussed earlier). The default connection lifetime value is 0(which would effectively disable connection pooling because the instant a connection is returned to the pool it would be destroyed. See the below link for a list of all the properties associated with connection strings in .NET.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp&WROXEMPTOKEN=525328ZnJ2mwoqkbWtFrpx3GHY

Connection Pooling in the OLE DB .NET Data Provider

Let's take a look at connection pooling when using the OLE DB .NET Data Provider.

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDB" %>

Dim objCn as new OledbConnection

cnString = "Server=Power2000;Database=H1Contractors;Provider=SQLOLEDB;UID=sa;& -
PWD=H12225;pooling=true;max pool size=100"

objCn.ConnectionString = cnString
objCn.Open()

You will notice this is very similar to what we saw for the SQL Server Provider. One thing to note is that the Provider keyword is required. Another thing to note is that you can use an ODBC DSN with the OLE DB .NET provider.

cnString = "DSN=myDSN;Database=H1Contractors;Provider=SQLOLEDB;UID=sa;PWD=H12225;pooling=true;max pool size=100"

Since the SQL Server .NET provider uses its own protocol to communicate with SQL Server, it does not add an ODBC layer and therefore does not support the use of a DSN in a connection string.

Testing Connection Pooling in an Application

To test connection pooling in ASP applications, I built the following pages:

• ADOConnectionTest_1Connection1Pool.asp - This is a standard ASP page that opens 1 connection to the specified database, queries the database, and returns data.
• ADOConnectionTest_2Connections1Pool.asp - This ASP page creates 2 connection objects and connects to the database with each connection. Both connection objects use the same connection string settings to open the database connection. This has the effect of each connection object sharing connections from the same pool.
• ADOConnectionTest_2Connections2Pools.asp - This ASP page creates 2 connection objects and connects to the database with each connection. Each connection object uses it's own connection string(the connection strings are different) to open the database connection. This has the effect of creating 2 connection pools on the page.

To test connection pooling in ASP.NET applications, I built the following pages:

• ADOConnectionTest_PoolingEnabled.aspx - This ASP.NET page creates one connection object to the db, queries the db, and returns data. Using the "pooling" attribute of the connection string, connection pooling is enabled for this page.
• ADOConnectionTest_PoolingDisabled.aspx - This ASP.NET page creates one connection object to the db, queries the db, and returns data. By setting the "pooling" attribute of the connection string to false, connection pooling is disabled for this page.

Once I built these sample pages, I used the Microsoft Web Application Stress Tool (WAS) to simulate multiple requests to these pages. For those of you unfamiliar with the WAS, it is a simulation tool designed to act like multiple browsers requesting pages from a web application. A complete explanation of the WAS is beyond the scope of this article, for more info see the following link:

http://webtool.rte.microsoft.com/?WROXEMPTOKEN=525328ZnJ2mwoqkbWtFrpx3GHY

To test the different pages, I created a couple of different test scripts to target the different pages. Each of the tests was 3 minutes in duration with a stress level (threads) of 100 and a stress multiplier(sockets per thread) of 1.

Figure 4 - Web Application Stress Tool test settings. The tests I conducted were for 3-minute intervals with a stress level of 100 and a stress multiplier of 1. All of the rest of the settings I left at the default values.

Tests collected the Logins/sec counter of the SQLServer:General Statistics object at one-second intervals. As the name implies, the Logins/sec counter is a measure of the number of attempted logins to SQL Server per second. Below you can see the results from the tests.

Figure 5 - Comparison of the Logins/sec counter for different ASP application settings. As you can see from above, there wasn't much difference in the number of logins when there was one connection on the page and when there were two connections on the page drawing from one pool. However, if you compare the number of Logins/sec from the page with two connections and one pool to the page with two connections and two pools, you can see a large difference in the average number of Logins/sec.

Figure 6 - Comparison of the Logins/sec counter for different ASP.NET application settings. On both of these pages, one connection object was established to connect to the database. The "No Pooling" page had the "pooling" attribute of the connectionstring set to false, thereby disabling connection pooling. The page that had pooling enabled had a much lower average number of Logins/sec than its counterpart that had pooling disabled.

The results of the tests clearly show that connection pooling is working. There was a much higher number of average Logins/sec for the pages in which connection pooling was either disabled or multiple connection pools were created versus the pages in which connection pooling was enabled or only one pool was created.

Note that these tests were only to demonstrate the differences in the number of Logins/sec to SQL Server when connection pooling was enabled or disabled amongst applications of the same type (ASP to ASP and ASP.NET to ASP.NET). These tests were in no way an attempt to compare performance between connection pooling in ASP applications to connection pooling in ASP.NET applications.

Conclusion

In this article we've taken a look at connection pooling and resource pooling. We've seen the advantages connection pooling can bring to an application and the different ways to go about implementing connection pooling in an application. We also explored the ways in which different property settings can affect how connection pooling in your application behaves. Finally, we looked at both the SQL Server .NET data provider and the OLE DB .NET Data provider and saw how easy it is to enable/disable connection pooling in a .NET application.