Pooling in the Microsoft Data Access Components
Authors: Leland Ahlbeck, Don Willits, and Acey J. Bunch
Updated by David Schwartz and Ming Cheung (updated November 2011)
This article discusses the benefits, features, and behavior of pooling within the context of the Microsoft® Data Access Components (MDAC) architecture, including technology-specific tips for OLE DB, Microsoft ActiveX® Data Objects (ADO), and Open Database Connectivity (ODBC) developers. (Both ODBC and OLE DB support pooling of database connections, although the exact behavior and features differ slightly.)
The article begins with a general discussion of what pooling is and the benefits it provides. Next, the following topics are discussed in detail, with "how-to" code examples:
ODBC connection pooling
OLE DB resource pooling
Troubleshooting MDAC pooling
Contents:
Introduction
ODBC Connection Pooling
Troubleshooting MDAC Pooling
Summary
Bibliography
Introduction
The Microsoft Data Access Components (MDAC) architecture provides a universal framework for exposing both traditional SQL-based database sources and non-SQL data stores such as documents or multidimensional sources. The architecture requires nothing of data except that it can be exposed in tabular form from an OLE DB data provider or ODBC data source. Microsoft ActiveX Data Objects (ADO), Remote Data Service (RDS), or even OLE DB itself can then expose that data to the consumer application.
Pooling enables an application to use a connection from a pool of connections that do not need to be reestablished for each use. Once a connection has been created and placed in a pool, an application can reuse that connection without performing the complete connection process.
Figure 1 shows the MDAC architecture and how pooling fits into it.
Figure 1. The MDAC Stack of Components and Pooling
.gif "The MDAC Stack of Components and Pooling")
For either ODBC or OLE DB, pooling creates and maintains a group of connections to a database or other data store and hands them out to consumers requesting a connection to that data store. As applications open connections to a given data store and subsequently release them, a pool of open connections is built with complete authentication information and connection properties. Then, if available, connection requests to the same data store (with the same user authentication and connection properties) are satisfied from the pool rather than by making the connection on demand. Connections are held open for a period of time after a consumer application has released them. They are released by the pooling mechanism when they time out.
The main benefit of pooling is performance. Making a connection to a database can be very time-consuming, depending on the speed of the network as well as on the proximity of the database server. When pooling is enabled, the request for a database connection can be satisfied from the pool rather than by (re)connecting to the server, (re)authenticating the connection information, and returning (again) a validated connection to the application.
Pooling is available in two forms for applications that use the Microsoft Data Access Components: ODBC offers connection pooling through the ODBC Data Source Administrator, and OLE DB core components provide resource pooling as well as additional services such as shaping and the client-side cursor.
You cannot use both ODBC connection pooling and OLE DB resource pooling for a given connection to the database. You should choose which form of pooling you want to use and stick with it exclusively within a given application. However, you can disable OLE DB resource pooling and use connection pooling if you are using the OLE DB Provider for ODBC.
ODBC connection pooling was first introduced with ODBC version 3.0. With the release of MDAC 2.0 (which included OLE DB 2.0 and ODBC 3.51), OLE DB resource pooling was introduced and was available to any provider that supported COM aggregation and the OLEDB_SERVICES registry key. However, by default, resource pooling for the OLE DB Provider for ODBC was disabled, so any ADO or OLE DB data consumer using this provider actually ended up using ODBC connection pooling. This changed with the release of MDAC 2.1, and the OLE DB Provider for ODBC now uses resource pooling by default. If you prefer to use connection pooling, you can still disable resource pooling for the OLE DB Provider for ODBC.
In this article, user refers to someone who makes a connection with a specific set of credentials used to access the database. In this case, user does not refer to the logon credentials—such as the Microsoft Windows® domain account and the password used to log on to Microsoft Windows—used to access an operating system. The logon credentials for the MDAC user include both the connection string for ADO and any properties or attributes set prior to connection. However, pooling in MDAC also recognizes attributes you set in ODBC prior to the connection as well as properties set in OLE DB or ADO beyond the actual connection string used.
In addition, setting the value of dynamic ADO properties can affect user authentication. Consider the PROMPT property, which corresponds to the OLE DB property DBPROP_INIT_PROMPT and from there maps to the DriverCompletion argument of the ODBC SQLDriverConnect function. Changing the value of this property between two different connection objects that otherwise share an identical connection string will create two different user authentications.
Benefits of Pooling
Any application that makes frequent calls to the data store for connections can benefit from pooling. Figure 2 shows how both a typical Web-based and a three-tier application use pooling. Notice that multiple connections to the database are open. Further, notice that a given connection is either in a pool for OLE DB providers or in a pool for ODBC drivers, but not both. When a user releases a connection, it is returned to the pool rather than being released. The connections are freed when the database activity and the load on the Web server drops.
Figure 2. Benefits of Pooling
.gif "Benefits of Pooling")
In developing an application that will run in a Web-based or multi-tier environment, pooling becomes very important. Making connections to the database can be one of the application's most time-consuming activities. Maintaining connections to the database in the resource state of the Web server can create scalability problems because all users are forced through the same connection object (not to mention that Web servers are almost by definition "stateless"). Opening a new connection on every page of a Web server is bad because it's slow. MDAC pooling provides a way to get the best of both scenarios: a limited number of connections (just enough to match your system's current load) without introducing a scalability bottleneck.
ODBC Connection Pooling
When an application makes its database connections by using ODBC database drivers, the connections are cached through the ODBC connection pool. When the first request for a connection to a database is made, the connection passes through the pooling components and then user information and any properties configured prior to making the connection are cached. The connection request is next passed on to the back-end server, and a live connection is handed to the user to satisfy the connection request. The rest of the application will be unaffected by pooling.
When the application releases the connection, the pool returns S_OK to the user, simulating a successful disconnection from the database. However, the actual connection is not released by ODBC but instead waits in the pool. When the next request for a connection to the database that has the same data source and user information comes in, it is satisfied from the connection in the pool rather than by making a full connection to the database. However, if the connection has already timed out or if there are no connections in the pool matching the request, a new connection is opened. In this respect, connection pooling is transparent to the calling application; the exception to this is pool configuration, which will be covered in the section "Configuring ODBC Connection Pooling."
Figure 3. ODBC Connection Pooling and Web/Multi-Tier Applications
.gif "ODBC Connection Pooling and Web/Multi-Tier Applica")
To take advantage of connection pooling, a driver must be threadsafe. (If you are unsure about whether a driver is threadsafe, check with the manufacturer.) For an ODBC driver to be threadsafe, the underlying components it uses must be threadsafe as well. For example, an Oracle ODBC driver relies on SQL*Net and the Oracle Call Level Interface. Earlier versions of this driver were not threadsafe because either they or their underlying components were not threadsafe. However, the ODBC driver for Oracle shipping with ODBC 3.5 (MDAC version 1.5 and later) is threadsafe.
Finally, the behavior of ODBC connection pooling can be programmatically controlled by an ODBC-based application. That is, the scope of the pool can be configured to be one pool per environment handle or to be driverwide. (If you develop using the OLE DB Provider for ODBC, this cannot be configured. Only if you use the native ODBC API can you modify this behavior.) If configured by environment handle, multiple connections to different drivers can be created off that handle and all of these connections will share the same pool.
Configuring ODBC Connection Pooling
Connection pooling can be enabled or disabled from the following locations:
The ODBC Data Source Administrator— At the Data Source Administrator level, pooling can be enabled or disabled for the entire driver. This includes features such as whether pooling is on AND the time-out value. Unless you are writing a native ODBC application, you need be concerned only with the Data Source Administrator.
The registry— Before the Data Source Administrator offered support for connection pooling in ODBC 3.5 (MDAC 1.5), you needed to use the registry to configure ODBC connection pooling.
The calling application— From an application level (native ODBC application only), your application can make calls to the Data Source Administrator to tell it whether you want pooling and the type of pooling you want.
Within ODBC connection pooling, the following configurable setting can affect how pooling works. It can be set by editing the registry directly, but with ODBC 3.5 (MDAC 1.5) and later, you can use the ODBC Data Source Administrator, which is the preferred method.
- The CPTimeout value— This value indicates the amount of time that a connection remains unused in the pool before the connection is released. (We release the connections to avoid "connection creep," which will eventually lead to the server refusing any new connection requests when it reaches its limit on available connections.)
Connection pooling can be configured on a per-driver basis. For example, you can have pooling enabled for one driver and off for another driver. By setting the connection time-out value to 0 in the registry for a specific driver, pooling will be disabled for that driver. Conversely, setting CPTimeout to a nonzero value will turn on pooling for that driver.
Configuring Connection Pooling from the Data Source Administrator
Connection pooling can be configured from the Data Source Administrator (or ODBC Data Source Administrator) in Administrative Tools in Control Panel.
Alternatively, you can start the ODBC Data Source Administrator at the Run prompt. On the taskbar, click Start, click Run, and then type Odbcad32.
The tab for managing connection pooling is found in the ODBC Data Source Administrator dialog box in version ODBC 3.5 and later.
Configuring Connection Pooling from the Registry
For versions prior to version 3.5 of the ODBC core components, you need to modify the registry directly to control the connection pooling CPTimeout value. To do so, start the registry editor and locate the CPTimeout value for your driver in the appropriate registry key. For example, the CPTimeout value for SQL Server is located in the registry under HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/SQL Server. You can find the CPTimeout value for all drivers in the registry under the HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/Driver_Name key.
The default time-out value for connection pooling is 60 seconds, and this value should be adequate for most installations.
Caution
Using the registry editor incorrectly can cause serious, system-wide problems that may require you to reinstall your Windows operating system to correct them. Microsoft cannot guarantee that any problems resulting from changes you make to the registry by using a registry editor can be solved. Use this tool at your own risk.
Configuring Connection Pooling from Your Application
As discussed earlier, pooling can be limited to a scope of the environment handle or the driver. Your application controls this by calling SQLSetEnvAttr with either SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV.
In Code Example 1, the console application is being set up to pool along with the rest of the application on the machine by setting the pooling value to SQL_CP_ONE_PER_DRIVER. This code demonstrates how to enable ODBC connection pooling.
Note
Other options for connection pooling are: SQL_CP_OFF, which disables connection pooling; SQL_CP_ONE_PER_HENV, indicating one pool per environment handle; and SQL_CP_DEFAULT, which is currently mapped to SQL_CP_OFF.
Code Example 1: Enabling ODBC Connection Pooling
HENV henv = SQL_NULL_HENV;
HDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
RETCODE rc = SQL_SUCCESS;
rc = SQLSetEnvAttr( SQL_NULL_HENV,
SQL_ATTR_CONNECTION_POOLING,
SQL_CP_ONE_PER_DRIVER,
0 );
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HENV, &henv );
rc = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER );
rc = SQLAllocHandle ( SQL_HANDLE_DBC,
henv,
&hdbc );
for ( long i = 1; i<= nCount && ODBCSUCCESS( rc ); i++ )
{
rc = SQLConnect( hdbc,
(SQLCHAR*) lpszDSN, SQL_NTS,
(SQLCHAR*) lpszUid, SQL_NTS,
(SQLCHAR*) lpszPwd, SQL_NTS );
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
if ( hstmt ) SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
if ( hdbc ) SQLDisconnect( hdbc );
}
if ( hdbc ) SQLFreeHandle( SQL_HANDLE_DBC, hdbc );
if ( henv ) SQLFreeHandle( SQL_HANDLE_ENV, henv );
Enabling Connection Pooling
For Internet Information Services (IIS) version 3.0, pooling is set to off by default, so you need to manually turn on connection pooling. For IIS version 4.0 and later, pooling has been set to on by default. (IIS 3.0 uses ODBC 3.0, and IIS 4.0 uses ODBC 3.5.) If you are coding by using the ActiveX Data Objects (ADO) object model to the OLE DB Provider for ODBC (MSDASQL), connection pooling will be turned on for you. If you are not using ADO and are coding directly to the ODBC API, you will need to turn on pooling yourself. The section "Configuring ODBC Connection Pooling" earlier in this document provides details on how to accomplish this.
As mentioned in the "Introduction" section, when OLE DB resource pooling first appeared in MDAC 2.0, it was by default disabled for the OLE DB Provider for ODBC. Therefore any ADO or OLE DB data consumer using this provider implicitly used ODBC connection pooling. With the release of MDAC 2.1 (and later versions), this changed, and now the OLE DB Provider for ODBC uses OLE DB resource pooling by default. You can easily restore ODBC connection pooling by changing the registry setting of OLEDB_Services for the CLSID of the OLE DB Provider for ODBC. See Table 2, in section "Configuring OLE DB Resource Pooling" later in this document, for a list of values that can exist in the OLEDB_Services registry entry.
Determining the Number of Available Pools
Connection pooling allows more than a single pool. The number of pools is equal to the number of processors. This helps to avoid conflicts when multiple threads are in use on the server. With only one pool and 50 threads running, there would be locking conflicts on the pool as the pools were locked for updating. However, with multiple pools available, the request for a connection simply moves to the next pool if the first pool is locked. (Note that a given pool can have connections to different data sources for one or more different drivers.)
When a request for a new connection comes into the pool, the pooling logic must look through the available connections for an existing connection that matches the properties of the connection attributes used to request the connection. This is accomplished by finding a pool that is not locked, locking the pool, and looking at each available connection in the pool to determine whether there is a match between the pooled connection information and the requesting connection. If a match is found, the pooled connection will be handed to the application requesting the connection and the pool will be unlocked. If a connection with the proper connection information cannot be found, a new connection will be created.
Monitoring Connection Pooling
ODBC connection pooling activity can be monitored through Performance Monitor in Windows NT 4.0. Keep in mind that this tool should be used to check pooling on an occasional basis and not be left on indefinitely. It can and will have an adverse effect on system and pool performance, even if Performance Monitor isn't actually running. (For ODBC version 3.51, pool monitoring can be enabled from within the ODBC Data Source Administrator. After starting the Data Source Administrator either from Control Panel or by using Odbcad32.exe, you will find a Connection Pooling tab (for ODBC 3.5 and later). The Performance Monitor settings are for all drivers, not just the selected driver.) To enable pool monitoring, open the ODBC icon in Control Panel. In the ODBC Data Source Administrator dialog box, click the Connection Pooling tab. Under PerfMon, select the Enable option.
Note
Most of the functionality of Performance Monitor has been incorporated into System Monitor for Microsoft Windows 2000 and XP. References to Performance Monitor in this article may not reflect the equivalent behavior with System Monitor. If you are using Windows 2000 or XP, see your operating system documentation for more information.
Pooling Counters and What They Mean
To monitor connection pooling:
Start Performance Monitor (PerfMon.exe), either from the command prompt or by clicking Start on the taskbar and then clicking Run.
Click the Add counter button (+).
In the Add to Chart dialog box, you will now have an entry for ODBC Connection Pooling under the process list box. (If you don't, either pooling isn't enabled in the Data Source Administrator or you need to restart your machine because existing applications or services have ODBC loaded in memory, preventing any changes in the Data Source Administrator from taking immediate effect.) There will now be six counters that can be monitored by performance monitor.
The naming of the counters is based on the distinction of hard versus soft connections. Hard actions are taken by the connection pooling process. Soft actions are taken by the application that uses connection pooling. See Table 1, ODBC Connection Pooling Performance Monitor Counters, for a description of each counter.
The counter that you see the most activity on should be the ODBC Soft Connection Counter. This indicates the number of connections that are satisfied by the connection pool. In this case, a live connection comes from the pool rather than requiring your application to make a new connection to the database.
Table 1. ODBC Connection Pooling Performance Monitor Counters
Counter
Definition
ODBC Hard Connection Counter per Second
The number of actual connections per second that are made to the server. The first time your environment carries a heavy load, this counter will go up very quickly. After a few seconds, it will drop to zero. This is the normal situation when connection pooling is working. When the connections to the server have been established, they will be used and placed in the pool for reuse.
ODBC Hard Disconnect Counter per Second
The number of hard disconnects per second issued to the server. These are actual connections to the server that are being released by connection pooling. This value will increase from zero when you stop all clients on the system and the connections start to time out.
ODBC Soft Connection Counter per Second
The number of connections satisfied by the pool per second—in other words, connections from that pool that were handed to users. This counter indicates whether pooling is working. Depending on the load on your server, it is not uncommon for this to show 40–60 soft connections per second.
ODBC Soft Disconnection Counter per Second
The number of disconnects per second issued by the applications. When the application releases or disconnects, the connection is placed back in the pool.
ODBC Current Active Connection Counter
The number of connections in the pool that are currently in use.
ODBC Current Free Connection Counter
The current number of free connections available in the pool. These are live connections that are available for use.
Pools Currently Active
The number of pools currently active. This counter was added in Windows 8, for drivers that manage connections in the connection pool. For more information, see Driver-Aware Connection Pooling.
Pools Created
The number of pools active, including active and removed pools. This counter was added in Windows 8, for drivers that manage connections in the connection pool. For more information, see Driver-Aware Connection Pooling.
Connection Pooling and SQL Server Temporary Stored Procedures
Creating stored procedures to process prepared statements with connection pooling enabled can have unexpected results. Before elaborating on unexpected behavior with connection pooling and temporary stored procedures, it is important to first understand when temporary stored procedures are created and, more importantly, deleted.
Creating temporary stored procedures for prepared SQL statements is an option that is configurable from the ODBC Data Source Administrator. By default, this setting is on for the SQL Server 2.65 and 3.5 drivers. This means that when an SQL statement is prepared, a temporary stored procedure is created and compiled. When the prepared command is called from an OLE DB consumer or an ODBC application, the temporary stored procedure is executed, saving the overhead of parsing and compiling the SQL statement. If used properly, this feature can improve the performance of your application. If your SQL statement is going to be executed more than two times or contains parameters and will be called multiple times, the statement should be prepared. Remember that there is a price to be paid up front to prepare an SQL statement and that preparation is lost as soon as you disconnect from the database.
With connection pooling enabled, the issue becomes how to determine when these temporary stored procedures should be deleted. With the SQL Server 2.65 driver, they will be released when the connection is released. With the SQL Server 3.5 driver or a later version, you can choose whether to delete them when the connection is released or both when the connection is released and as appropriate while connected.
Unexpected behavior can occur with temporary stored procedures and connection pooling if you are using the default setting. In that case, you run the risk of running out of room in the SQL Server temporary database (tempdb), where temporary stored procedures are created and stored. When connection pooling is enabled, a connection is made to the database, but when the client is done with it and releases it, the connection goes back into the pool. When the connection goes back into the pool, the connection is not released at the server level and the stored procedures are not deleted from tempdb. Effectively, you create temporary stored procedures in tempdb that are no longer associated with a client and will never be called again.
When you are running the SQL Server 2.65 driver, it might be advisable to disable the creation of stored procedures while preparing a command when running with connection pooling. With the SQL Server 3.5 and newer drivers, when adding or configuring a DSN in the Data Source Administrator, you will have the option of creating temporary stored procedures. This option should be disabled or set to When you disconnect and as appropriate while you are connected when running connection pooling. Setting this option means that the ODBC SQL Server driver will drop the connection when the command that created the interface goes out of scope. If your client code is ADO, the stored procedure would be released when the ADODB.Recordset and command objects are closed. SQL Server supports prepared commands, so it will not create temporary stored procedures when you have requested that your command be prepared.
Changing the Database Context
If you change the database context to another while you are connected to the database and do not set it back to the original context, you may cause problems in the pool. Consider the case where you use a stored procedure to change the database context to tempdb and then disconnect at the application level. The connection, now pointing to tempdb rather than to your original database, is then returned to the pool. The next time a request for a connection to your original database is made, the pooled connection pointing to tempdb might be handed to it. To avoid this, always maintain the original database context before disconnecting.
OLE DB Resource Pooling
OLE DB resource pooling, also known as OLE DB session pooling, is handled by the OLE DB core components. To take advantage of resource pooling, an OLE DB consumer must invoke the data provider by using either the IDataInitialize or the IDBPromptInitialize interface. (ADO will attempt to do this automatically.) OLE DB resource pooling can be turned on for one provider and off for another.
Note
Performing CoCreateInstance on IDBInitialize is traditionally used in OLE DB to open a data source object. To use resource pooling, you perform CoCreateInstance on either IDataInitialize or IDBPromptInitialize. Both interfaces are part of the OLE DB service components and not your OLE DB data provider. You can use IDataInitialize and IDBPromptInitialize to retrieve an instance of IDBInitialize from your data provider so that the service components can be used. The two interfaces make it possible to use service component features such as pooling, transaction enlistment, and the Client Cursor Engine.
When the application creates an OLE DB data source object (via ADO or an OLE DB consumer), OLE DB services query the data provider for supported information and provide a proxy data source object to the application. To the consuming application, this proxy data source object looks like any other data source object, but in this case setting properties merely caches the information in the local proxy. When the application calls IDBInitialize::Initialize in OLE DB or opens a connection in ADO, the proxy data source object checks whether any connections already exist that match the specified connection information and are not in use. If so, rather than creating a new object, setting properties, and establishing a new connection to the database, the proxy data source object uses the existing initialized data source object. When the application releases the data source object, it is returned to the pool. Any data source object that is released by the application and not reused after 60 seconds is automatically released from the pool.
Figure 4 shows how OLE DB resource pooling works.
Figure 4. The Benefits of OLE DB Resource Pooling
.gif "The Benefits of OLE DB Resource Pooling")
Note
The term resource pooling is actually something of a misnomer. It implies that any kind of resource can be pooled, when in fact OLE DB resource pooling is just for the pooling of an OLE DB data source proxy object (DPO). Resource pooling does use the resource dispenser found with Microsoft Transaction Server, which is a more generic form of pool manager. More than a few OLE DB users have been confused by the term "resource pooling" into thinking that it pools more than it actually does.
Configuring OLE DB Resource Pooling
By default, service components are enabled for all Microsoft OLE DB providers if the provider is invoked by IDataInitialize or IDBPromptInitialize and if the provider is marked to work with pooling by using the OLEDB_Services registry key.
You can control pooling from your application in the following three ways:
You can disable pooling for an individual provider through the registry.
If you write directly to the OLE DB API, you can control pooling through the properties you set when connecting to the database.
You can control pooling from your connection string.
The time-out value is set to 60 seconds; this value cannot be configured in OLE DB resource pooling prior to the release of MDAC 2.5.
After the server has been determined to be unavailable, resource pooling blocks the pool. The next retry for a valid connection on the server occurs after one minute. If this attempt fails, the next retry occurs after two minutes, and again after five minutes. Thereafter, the retry occurs every five minutes until a valid connection is returned.
Configuring Resource Pooling from the Registry
To determine whether individual core components can be invoked to satisfy extended functionality requested by the consumer, OLE DB compares the properties specified by the consumer to those supported by the provider. Table 2 lists values that can exist in the OLEDB_Services registry entry, which should be created during installation of the provider.
Table 2. Setting OLE DB_Services Registry Entry Under Provider's CLSID
Default services enabled |
DWORD value |
All services (the default) |
0xffffffff |
All services except pooling |
0xfffffffe |
All services except pooling and auto-enlistment |
0xfffffffc |
All services except client cursor |
0xfffffffb |
All services except client cursor and pooling |
0xfffffffa |
No services |
0x00000000 |
No aggregation, all services disabled |
No OLEDB_Services registry entry |
With the release of MDAC 2.5, you can configure both connection time-out and the Retry Wait values by using the registry.
When set under a provider's CLSID entry in the registry, the SPTimeout value controls the length of time, in seconds, that unused sessions are held in the pool. SPTimeout should be entered as a DWORD value under HKEY_CLASSES_ROOT/CLSID/ClassID where ClassID is the CLSID of the OLE DB provider. The default SPTimeout value is 60 seconds.
The Retry Wait value controls the length of time, in seconds, to wait between connection attempts. It is entered as a DWORD value under HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/DataAccess/Session Pooling. The default SPTimeout value is 60 seconds.
Configuring Resource Pooling from Your Application
If you write directly to the OLE DB API, you can set the DBPROP_INIT_OLEDBSERVICES property to enable or disable various core components, including OLE DB resource pooling. There is no way to configure connection time-out or Retry Wait programmatically in your application except by manipulating the registry entries directly. It is best not to have any services or applications using OLE DB when you make these changes, or the effects might not show up right away.
Table 3 lists values and the services enabled or disabled by the value settings. Code Example 2 is an excerpt of code that uses resource pooling in an OLE DB consumer.
Table 3. Setting OLE DB Services by Using the DBPROP_INIT_OLEDBSERVICES Property
Services enabled |
Property value |
All services |
DBPROPVAL_OS_ENABLEALL |
All services except pooling |
(DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_RESOURCEPOOLING) |
All services except pooling and auto-enlistment |
(DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_RESOURCEPOOLING& ~DBPROPVAL_OS_TXNENLISTMENT) |
All services except client cursor |
(DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_CLIENTCURSOR) |
All services except client cursor and pooling |
(DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_RESOURCEPOOLING& ~DBPROPVAL_OS_CLIENTCURSOR) |
No services |
~DBPROPVAL_OS_ENABLEALL |
Code Example 2: OLE DB Consumer Code Using Pooling
long OLEDBConnect( char *lpszInitString,
bool bUsePooling, // Flag to set pooling
ULONG nCount )
{
IDataInitialize *pIDataInitialize = NULL;
IDBProperties *pDBProperties = NULL;
IDBInitialize *pIDBInit = NULL;
HRESULT hr = S_OK;
WCHAR wszInitString[1024];
AtoU( lpszInitString, &wszInitString[0], 1024 );
hr = CoCreateInstance(CLSID_MSDAINITIALIZE,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(void**)&pIDataInitialize);
for (ULONG i=0;i<nCount && SUCCEEDED( hr ); i++)
{
hr=pIDataInitialize->GetDataSource(NULL,
CLSCTX_INPROC_SERVER,
wszInitString,
IID_IDBInitialize,
(IUnknown**)&pIDBInit);
if (pIDBInit)
hr=pIDBInit->Initialize();
if ( bUsePooling == false )
if (pIDBInit)
pIDBInit->Uninitialize(); // Disables Pooling(!)
if (pIDBInit) pIDBInit->Release();
pIDBInit = NULL;
}
if (pIDataInitialize)
pIDataInitialize->Release();
return 0;
}
Note
One of the key points to note in the preceding code is that if you call IDBInitialize::Uninitialize, you will turn off pooling! To release a connection, use IDBInitialize::Release instead.
The OLE DB property DBPROP_INIT_OLEDBSERVICES maps directly to a connection string attribute, OLE DB Services, as shown in Table 4. Code Example 3 demonstrates this.
Table 4. Setting OLE DB Services by Using ADO Connection String Attributes
Services enabled |
Value in connection string |
All services (the default) |
"OLE DB Services = -1;" |
All services except pooling |
"OLE DB Services = -2;" |
All services except pooling and auto-enlistment |
"OLE DB Services = -4;" |
All services except client cursor |
"OLE DB Services = -5;" |
All services except client cursor and pooling |
"OLE DB Services = -6;" |
No services |
"OLE DB Services = 0;" |
Code Example 3: ADO Consumer Code Using Pooling
'This will take advantage of resource pooling.
Dim i As Integer
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
cnn.Open "DSN=LocalServer;UID=MyUserName;PWD=MyPassword; " & _
"OLE DB Services=-1"
For i = 1 To 100
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM Authors", cnn
Set rst = Nothing
Next i
cnn.Close
Set cnn = Nothing
Enabling OLE DB Resource Pooling
Resource pooling can be enabled in several ways:
Automatically, when a consumer accesses a provider from within Microsoft Transaction Server or Internet Information Services 4.0 or later.
For an OLE DB-based consumer, by using the IDataInitialize or IDBPromptInitialize interface. If you use IDataInitialize::CreateDBInstance, you can enable or disable services components by using the DBPROP_INIT_OLEDBSERVICES property.
For an ADO-based consumer, by keeping one open instance of a Connection object for each unique user and using the OLEDB_SERVICES connection string attribute to enable or disable pooling. By default, ADO attempts to use pooling, but if you do not keep at least one instance of a Connection object open for each user, there will not be a persistent pool available to your application. (However, Microsoft Transaction Server keeps pools persistent as long as the connections in them have not been released and have not eventually timed out.)
Note
Pooling will not be enabled if you call CoCreateInstance directly on the CLSID of the data provider.
Determining the Number of Available Pools
In OLE DB resource pooling, the following formula determines the number of pools (where N is the number of pools, P is the number of processors, and C is the number of distinct sets of connection attributes on the system):
N = (P + 1) * C
Using this formula, OLE DB resource pooling eliminates lock contentions on the pools.
Note
P+1 is a default value which can be overridden by setting the following registry key:
CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\Holders.
OLE DB resource pooling keeps a map of which pools contain which users. From there, it is possible to jump to the right pool and start looking for an available connection. With OLE DB resource pooling, a given pool contains connections only for a single provider with a specific set of connection attributes. In other words, each pool contains only connections to one set of credentials and one data store. As a result, a new pool will be created for each user if a user is connecting using Windows Authentication.
Writing Data Consumers That Work With OLE DB Services
When developing an application using either ADO or native OLE DB, the following tips will help you to enable resource pooling or to avoid inadvertently disabling it. These rules apply whether you use ADO, OLE DB consumer templates, or native OLE DB code.
Tips for ADO Users
The ADO Connection object implicitly uses IDataInitialize. However, this means your application needs to keep at least one instance of a Connection object instantiated for each unique user—at all times. Otherwise, the pool will be destroyed when the last Connection object for that string is closed. (The exception to this is if you use Microsoft Transaction Server. In this case, the pool is destroyed only if all of the connections in the pool have been closed by client applications and are allowed to time out.)
Note
If you open a Connection object, remember to close it. Do not rely on garbage collection to implicitly close your connections.
By avoiding connection creep (discussed in section "Connection Creep and Effective Server Tracing" later in this article), you can also help your application use pooling.
Tips for OLE DB Users
Use IDataInitialize or IDBPromptInitialize to create an instance of your data provider. As long as one instance of either interface exists, a pool is available to your application. Otherwise, the pool will be destroyed.
Do not request provider-specific interfaces prior to connecting.
Do not request prompting.
Do not use IDBInitialize::Uninitialize. Use IDBInitialize::Release instead. If you call Uninitialize, the connection you were using will be flushed from the pool. This is because the user can change the properties of the connection after calling Uninitialize.
Do not release your last instance of IDataInitialize or IDBPromptInitialize while pooling is needed by your application.
Use only one resource per connection.
By avoiding connection creep (discussed below), you also help your application use pooling.
Writing Data Providers That Work with OLE DB Services
As mentioned above, ADO attempts to use or enable services implicitly by referencing the OLEDB_Services registry value when a connection is established. However, some older providers may not be able to support services. Simply creating this registry entry is not enough to enable services for your application. The data provider must support certain features or services will not work. Attempting to use services with a data provider that does not offer this functionality can create unexpected behavior in your application. Following is a brief list of the functionality a data provider must support for your consumer application to utilize services:
The provider must support aggregation of all objects.
Providers must support the free-threaded model or, at a minimum, the rental-threaded model. Services will determine the data provider's thread model by using the DBPROP_DSOTHREADMODEL property.
If the provider has a global connection state that may change while the data source object is in an initialized state, it should support the DBPROP_RESETDATASOURCE property.
Providers that connect to a remote database and can detect whether that connection has been lost should support the DBPROP_CONNECTIONSTATUS property. This allows pooling to detect dead connections and to ensure that they are not returned to the pool.
Providers should support both pooling and transaction enlistment by using the DBPROP_INIT_OLEDBSERVICES property. The values used with this property also correspond to the OLEDB_Services registry key.
For more information, consult either the OLE DB Readme file that shipped with the Data Access 2.0 SDK or the OLE DB Services section in the documentation with version 2.1 and later of the SDK.
Troubleshooting MDAC Pooling
Occasionally, MDAC pooling might not work as expected. When this happens, you need to look at what your application is doing that could have a negative impact on pooling, especially if the application has less than comprehensive error handling. At this time, OLE DB does not offer a way to trace activity on OLE DB providers. This makes robust error handling even more important (see the discussion in section "Robust Error Handling" later in this article).
Connection Creep and Effective Server Tracing
Pooling trouble diagnosis often begins by using tools such as SQL Trace and SQL Server Performance Monitor to track connection creep, or an increase in the number of open connections on a database server. However, this does not equate to a failure in pooling. Identifying connection creep is only the beginning of an analysis, not its conclusion.
Common causes of connection creep include:
The ADO Connection and Recordset objects are not actually closed. If you don't explicitly close them, they won't be released into the pool. This is probably the single most frequent cause of connection creep.
The wrong cursor location is used with a Recordset object you intend to disconnect. A disconnected Recordset cannot use a server-side CursorLocation property. You must explicitly set CursorLocation to be client-side.
A disconnected Recordset has not actually been disconnected. If you don't set the ActiveConnection property of the Recordset to Nothing/NULL, it is not disconnected and the connection is not released into the pool.
CursorLocation is set before the Recordset is opened, not after. After the Recordset is open, CursorLocation does not and cannot change.
An ADO object you created (especially a Connection object) was not explicitly released. Explicitly releasing objects you create is just good programming practice. If you allocate memory, release it. Depending on the language you are using, letting a variable go out of scope may or may not result in it being released.
Note
If you have effective error trapping in your code, you can receive error messages that point to the real culprit (such as a failure in SQL Server's NetLib or Oracle's SQL*NET components).
Details of several of these problems are discussed in the sections that follow.
If You Explicitly Open It, Explicitly Close It
Pooling problems are frequently caused by an application that does not clean up its connections. Connections are placed in the pool at the time that the connection is closed and not before. To avoid this, always explicitly close an object you open. If you don't explicitly close it, chances are it won't ever be released to the pool.
Even if the language you use has effective and reliable garbage collection, an instance of an open ADO Connection or Recordset object going out of scope does not equate to the Close method of that object being implicitly invoked. You must close it explicitly.
Failing to close an open Connection or Recordset object is probably the single most frequent cause of connection creep and the single largest cause of incorrect diagnoses of pooling failure.
Disconnecting Recordset Objects
It is sometimes confusing in pooling to correctly disconnect a Recordset object. To release a connection into the pool, especially in three-tier environments, you must use a client-side, static cursor with BatchOptimistic locking. You must also release the Recordset ActiveConnection object. Otherwise, the Recordset object will not be disconnected and, more importantly, the connection will not be released into the pool. You cannot disconnect a Recordset object with a server-side cursor.
Code Example 4 demonstrates how to implement a disconnected Recordset object. The environment in which the code is used is not as significant as the actual steps to disconnect the Recordset object.
Code Example 4: Disconnected Recordset in ADO
Public Function ServiceTest(ConnectStr As String, _
SqlText As String) _
As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.ConnectionString = ConnectStr
cnn.CursorLocation = adUseClient
cnn.Open
Set rst.ActiveConnection = cnn
rst.Open SqlText, , adOpenKeyset, adLockBatchOptimistic, -1
Set rst.ActiveConnection = Nothing 'This disconnects the recordset.
cnn.Close
Set cnn = Nothing
'Recordset is now in a disconnected state.
rst.Close
Set rst = Nothing
Exit Function
End Function
If you do not intend to update any data in the cursor, it is more efficient to use a server-side (rather than a client-side) cursor. This is because the creation of the client-side object is resource-intensive and can impact performance.
Temporary Tables and Resource Pooling
If the connection is returned to the pool, the temporary table will persist until the connection is actually released—not just returned to the pool. If your application uses many temporary tables, this might create a resource problem on the server. If you use a stored procedure on the server to create the temporary table, the temporary table will be destroyed when the stored procedure goes out of scope. Temporary tables not created by stored procedures will be destroyed only after the connection is actually released from the pool.
Connection Status
The DBPROP_CONNECTIONSTATUS property reveals whether a stale connection exists for a given OLE DB provider in the pool. The pool returns a connection to the client even if the status of the connection is stale. For example, this can happen if the underlying server accessed by the connection is no longer available on the network.
Table 5 summarizes the DBPROPVAL_CS_* properties defined in OLE DB. These values can be used to determine the status of a connection.
Table 5. Valid Values for Connection Status Dynamic Property
Connection status |
Value |
Comment |
UNINITIALIZED |
0L |
Connection object has not had Open method called yet. |
INITIALIZED |
1L |
A valid connection. |
COMMUNICATIONFAILURE |
2L |
The connection is stale. Close and reopen the Connection object. |
Unintended Disabling of Resource Pooling
Resource pooling can be inadvertently disabled for your application. The persistence and behavior of resource pooling depend on several conditions that occur with a given set of user authentication criteria, such as the following:
A given resource pool is specific to the connection attributes of that set of authentication criteria.
Do not call IDBInitialize::Uninitialize, which implicitly disables pooling. Instead, use IDBInitialize::Release. While both IDBInitialize::Uninitialize and IDBInitialize::Release close the connection, calling Uninitialize will result in the pool being destroyed. (This is not an issue for ADO developers.)
The data source object must be created by using IDataInitialize or IDBPromptInitialize, and not by CoCreateInstance directly on the OLE DB data provider. For pools to exist for a given set of connection attributes, resource pooling requires at least one instance of an OLE DB data source object created per unique set of connection properties.
If using ADO, at least one ADO Connection object must remain open for each set of user authentication credentials. Although ADO cannot call Uninitialize, letting go of all ADO Connection objects for a given set of connection attributes accomplishes the same thing—that is, it releases the last instance of IDataInitialize.
Code Example 5 and Code Example 6 are examples of how you can inadvertently disable resource pooling.
Code Example 5: Disabling Pooling by Using IDBInitialize::Uninitialize
hr = CoCreateInstance(CLSID_MSDAINITIALIZE, NULL,
CLSCTX_INPROC_SERVER, IID_IDataInitialize,
(void**)&pIDataInitialize);
for (ULONG i=0;i<nCount && SUCCEEDED( hr ); i++)
{
pIDataInitialize->GetDataSource(NULL, CLSCTX_INPROC_SERVER,
wszInitString, IID_IDBInitialize,
(IUnknown**)&pIDBInit);
hr=pIDBInit->Initialize(); // Opens connection
pIDBInit->Uninitialize(); // Disables Pooling(!)
pIDBInit->Release();
}
Code Example 6: Disabling Pooling by Eliminating Instances of IDataInitialize
'This will not take advantage of resource pooling.
Dim i As Integer
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
For i = 1 To 100
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.Open "DSN=LocalServer;UID=MyUserName;PWD=MyPassword;" & _
"OLE DB Services=-1"
rst.Open "SELECT * FROM Authors", cnn
rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
Next i
Maintaining a Persistent Connection for Resource Pooling
This article has repeatedly emphasized the need for developers who use OLE DB resource pooling to keep at least one connection open for each set of user credentials used to access the data store. This is not to be confused with the mistake of opening up multiple and unnecessary Connection objects, or even multiple Recordset objects. It's also not to be confused with using just one Connection object for several hundred Active Server Pages (ASP pages).
ASP developers should open one connection per set of unique user credentials. However, an ASP developer can eliminate the benefits of pooling in this scenario in the following ways:
Instantiating dozens or even hundreds of instances of ADO objects at once.
For example, you can easily circumvent any performance gains found in pooling by using code such as the following:
Dim cnn(200) As ADODB.Connection Dim rst(200) As ADODB.RecordsetPresently, these two lines of code appear to be frequently and incorrectly used on individual ASP pages. The overhead of instantiating, opening, and manipulating this many objects will eliminate any benefit of pooling, not to mention that it will swell the pool to unnecessary size just to hold each of those connections. If you use this technique on multiple ASP pages, which in turn are hit by multiple users, the amount of memory needed just to hold all of those ADO objects in memory can soon reach into the gigabytes.
Using just one Connection object for multiple ASP pages.
If you don't want to circumvent the benefits of pooling, however, you can do this: Within a given ASP page, open the Connection object and one or more Recordset objects that you need. Then close and delete them.
This also applies for non-ASP developers who are developing an application with multiple threads. Do not try to share a single Connection object between all threads. Instead, use one thread, one Connection object, and one persistent connection in your main application to keep the pool alive.
For Microsoft Transaction Server developers, this is not an issue. Microsoft Transaction Server itself enables the pool, whether you implement a single Connection object or not. Because Microsoft Transaction Server is inherently stateless, trying to keep that persistent Connection object around, let alone actually use it, is redundant.
While pooling is a valuable tool that can give applications increased performance and scalability, it can also be sabotaged to the point of uselessness through coding techniques such as those just listed.
Robust Error Handling
When OLE DB encounters an error condition or if the underlying provider passes back informational messages, it will attempt to convey this information to your application. However, that does not mean the application is actually receiving it or logging it appropriately. Microsoft Visual Basic® Scripting Edition (VBScript) applications, in particular, have loose error handling.
OLE DB error handling extends Automation error handling by adding the ability of an error object to contain multiple error records.
Chapter 16, "Errors," in the Microsoft OLE DB Programmer's Reference has an excellent discussion of how to use these features. (The most up-to-date version is available in the MDAC SDK, and the OLE DB 2.0 version is available in print from Microsoft Press.) In the same reference, Appendix F, "Sample OLE DB Consumer Application," demonstrates practical code for the use of OLE DB error handling. In addition, the SQL Server documentation has an excellent code example that shows how to use the optional interfaces ISQLErrorInfo and ISQLServerErrorInfo.
Code Example 7 shows OLE DB error handling.
Code Example 7: Error Handling with OLE DB
// Goes to CLEANUP on Failure_
#define CHECK_HR(hr) \
if(FAILED(hr)) \
goto CLEANUP
// Goes to CLEANUP on Failure, and displays any ErrorInfo
#define XCHECK_HR(hr) \
{ \
if(FAILED(ErrHandler(hr, LONGSTRING(__FILE__), __LINE__))) \
goto CLEANUP; \
}
XCHECK_HR(hr = CoCreateInstance(CLSID_MSDAINITIALIZE,
NULL,
CLSCTX_INPROC_SERVER,
IID_IDataInitialize,
(void**)&pIDataInitialize));
CLEANUP:
if (pIDBInitialize != NULL ) pIDBInitialize->Release();
ADO and RDS generate an error in the native language you use to implement your application. In Visual Basic, this is an Err object. In Microsoft Visual C++®, this is usually an exception or a failed HRESULT.
Note
In VBScript, no event is fired. This makes error handling in VBScript especially challenging.
If you run in an environment that uses ASP, it is a good practice to define a standard error handler and use this error handler unilaterally throughout your code. That way, if you need to change your error handler, you need only make that change in one place—not in every page that uses error handling.
Code Example 8 shows ADO error handling.
Code Example 8: Error Handler in ADO
Dim cnn As ADODB.Connection
On Error GoTo ErrHandler
...
Exit Sub
ErrHandler:
'For this code, we do not have any Command or Recordset objects,
'but this error handler would close them down if they existed.
Call ErrHandler(cnn, Nothing, Nothing)
Top 10 Reasons Why OLE DB Resource Pooling Might Not Work
To summarize what this article has discussed, here is a list of the top 10 reasons why pooling might not be turned on. This text was first made available in the OLE DB Readme file.
The registry value OLEDB_Services must be present under the provider's HKEY_CLASSES_ROOT/<CLSID> key. OLEDB_Services is most commonly set to 0xffffffff, or to desired bits of DBPROPVAL_OS_*. If this does not exist or is set to 0x00000000, pooling will never occur. For more information, refer to "Setting Provider Service Defaults" in the OLE DB Services documentation.
The consumer can override the OLEDB_Services key and disable pooling by setting DBPROP_INIT_OLEDBSERVICES. For more information, refer to "Overriding Provider Service Defaults" in the OLE DB Services documentation.
The provider must be free-threaded. If a provider developer uses the OLE DB provider templates for Visual C++ or Visual Basic, the templates will not create free-threaded providers by default. In those cases, pooling will be disabled regardless of what the property or registry indicates.
Setting DBPROP_INIT_PROMPT and DBPROMP_INIT_HWND disables pooling of a data source object. These properties must be set to either VT_EMPTY or NOPROMPT.
Consumers cannot use QueryInterface for any interface unknown to service components prior to initialization. That is, applications that need to pool should not use QueryInterface, as COM suggests, to see what interfaces the provider supports. For example, a simple QI for IDBAsynchStatus, to determine whether the provider is Asynch or not, eliminates the data source object from the pool.
Calling IDBProperties::GetPropertyInfo prior to initialization disables pooling.
If IDBInitialize::UnInitialize is called, the released data source object will not be pooled.
Providers must correctly implement aggregation.
Do not use data source object notifications. If you use a QueryInterface for IConnectionPointContainer to advise a listener to the data source object, the object will never be pooled.
Note
These items are cumulative. That is, any item will disable pooling for that data source object, regardless of what the other values are.
Summary
OLE DB resource pooling is a key technique for achieving increased performance with MDAC. In ODBC, OLE DB, and ADO/RDS programming, pooling is a key piece of your application's architecture, even if you don't actually write ODBC or OLE DB code. Table 7 summarizes the key differences in behavior and functionality between the two pooling models.
Table 7. Comparison of ODBC Connection Pooling and OLE DB Resource Pooling
Feature |
Connection pooling |
Resource pooling |
Enabling pooling |
ODBC 3.5+: In the Data Source Administrator. Optionally, ODBC 3.0+ can set this in the registry. |
Implicitly available in ADO. Implicitly used by components running under Microsoft Transaction Server. Consumer uses IDataInitialize or IDBPromptInitialize. |
Disabling pooling |
By using registry setting. By using ODBC Data Source Administrator. By using SQLSetEnvAttr for ODBC API-based applications. |
By using registry setting. Implicitly disabled if there is not at least one persistent ADO Connection object (or OLE DB IDataInitialize interface) open for each unique set of user credentials in the application. If every connection has unique connection attributes or properties, n pools are created that contain one connection each. This does not disable pooling, but it does disable the benefits of pooling. Provider does not support COM aggregation or have OLEDB_SERVICES registry key. |
Configurable pool time-out |
Yes |
No |
Number of pools |
If P = number of processors: (P) Connections using different drivers and user credentials can be and are stored in the same pool. |
If P = number of processors and C = number of unique sets of user credentials (the connection string plus any properties you set before opening the connection): ((P + 1) * C) Each pool has a homogeneous set of connections with identical user credentials. |
Support required within the driver or provider |
Driver must be threadsafe. No other specific requirements. |
Provider must be threadsafe (implicit with OLE DB). Must support IDataInitialize and IDBPromptInitialize interfaces. Connection must be created through these interfaces. Provider supports COM aggregation. Provider supports OLEDB_Services registry key. |
This article has discussed how to enable, disable, and configure MDAC pooling and has presented practical tips for using pooling and for diagnosing issues that developers may encounter. The primary advantage of pooling is performance. The time it takes to open a connection is a significant resource sink in any data operation. Pooling enables developers who use MDAC to maximize performance, whether using ODBC connection pooling or OLE DB resource pooling.
Bibliography
Aaron Cohen & Mike Woodring, Win32 Multithreaded Programming, O'Reilly & Associates, Inc., 1998.
Microsoft, Microsoft OLE DB 2.0 Programmer's Reference and Data Access SDK, Microsoft Press, 1998.
Q193332 (FILE:MDACCON.EXE Using Connection Strings w/ ODBC/OLEDB/ADO/RDS) for details on connection strings for ODBC Drivers and OLE DB Providers.
Microsoft Data Access and Storage Web Site.
Keywords:
connection pooling (ODBC)
resource pooling (OLE DB)
session pooling (OLE DB)
MDAC pooling