| Disclaimer The results described in this document are independent findings. They reflect my opinions only and have no bearing on any other party. The data presented here and its interpretation does not relate in any way to Microsoft(TM) or to any other software developed by Microsoft. All trademark names appearing in this document are used for editorial purposes only and to the benefit of the trademark owner, with no intention of infringing on that trademark. Introduction Basic and Microsoft NT LAN Manager (NTLM) Web server authentication can cause database connections to be made on a per-user basis, thereby negating the benefits of OLE DB database connection pooling. (a.k.a. session pooling).
NT 4.0 servers offer several different ways to manage database connections. The default is OLE DB session pooling; the other option is to use Open Database Connectivity (ODBC) connection pooling. OLE DB session pooling is preferred, as it was developed to help increase the throughput when compared to ODBC connection pooling. Microsoft also recommends using session pooling unless there is some other issue that prevents you from doing so. We conducted tests to find out what the "other issues" might be and the impacts. We removed the many user-driven scenarios in order to focus on just the system configuration, NT authentication in particular. Each ASP page request created a database connection and ran several queries, and finally displayed the results. We carefully monitored the database server processes and watched for scenarios where the system consumed more database server resources relative to other configurations. Our test results showed that the full database connection process was performed significantly more often on those Web servers using the OLE DB session pooling mechanism in combination with the NT Basic Authentication security mechanism.
We found that the older and wiser ODBC connection pooling mechanism performed well under this configuration. Note: Different users cannot access the same memory in the Windows NT security model. Make sure your ODBC driver handles this situation when connection pooling or you may run into the problems described in the following article in the Microsoft Knowledge Base: Q183345 (see http://support.microsoft.com/support/kb/articles/Q183/3/45.ASP).
Connection Pooling Overview A well-oiled connection pooling mechanism allows the database server to spend most of its resources processing queries instead of handling connections.
With ActiveX Data Objects (ADO) 2.1 or later all ODBC drivers by default are enrolled in session pooling when using the OLE DB Provider for ODBC (MSDASQL). This means that connections are automatically pooled in the Web environment, even if the ODBC connection pooling for the driver is turned off on the ODBC Administrator. Memory
Oracle and NT both use memory to manage database connections. When an NT-authenticated user requests to connect to Oracle, the specific user connection created remains in NT memory for 120 seconds, which also keeps the memory allocated on the Oracle side. If the specific connection is not reused within the 120 seconds, NT will release the connection from the pool, and in turn release the memory on the Oracle side. Pooling
When you make a connection request, the pool is searched and the available connection that matches your user authentication plus your connection string is retrieved. If an available connection is not found, a new connection is created using your user authentication information plus your connection string. The following lists of configurable items were identified as having potential impacts on database connection pooling performance. - Anonymous Web access, NT Basic Authentication Web access, NT Challenge Response Authentication Web access
- Single processor, multiple processors
- Single ID, multiple Ids
- Database machine TCP/IP server settings
- Web Server machine TCP/IP server settings
- Oracle for Windows NT Client configuration settings
- Oracle database configuration settings
- Internet Information Services (IIS) configuration settings
- Driver type and version
- ODBC Provider version and settings
- ODBC Administration applet settings
- Number of unique connection strings
The tests we ran tested and measured the effect of the following: - Anonymous Web access ( single ID IWAM_machine name )
- NT Basic Authentication Web access with single user ID
- NT Basic Authentication Web access with multiple user IDs
Background Test machine:
- Microsoft (R) Windows NT (TM) Workstation
- Version 4.0 (Build 1381: Service Pack 5) x86
- Processor list: GenuineIntel ~265 MHz
- Physical Memory (K) Total: 64 MB
Test Software:
- Microsoft Web Application Stress 1.1
- Client Version 1.1.254.1
Web Server machine:
- Microsoft (R) Windows NT (TM) Server
- Version 4.0 (Build 1381: Service Pack 6) x86
- Processor list: GenuineIntel ~648 MHz (single)
- Physical Memory (K) Total: 261,532
- ADO 2.6, Oracle for Windows NT Client
- Driver: msorcl32.dll Microsoft ODBC for Oracle 02.573.6526
- Provider: MSDASQL.DLL 02.60.6526.0
Database server:
- IBM RS/6000
- AIX
- 67MHz single processor
- Oracle 8.0.3
Testing:
We ran the following three tests using Microsoft's Web Stress Tool using the same scripts for each test. - OLE DB with Web server anonymous access
- OLE DB with Web server basic authentication access -- The scripts used one ID/PW combination to authenticate.
- OLE DB with Web server basic authentication access -- The scripts used a set of 10 ID/PW combinations to authenticate.
System Settings:
- For all tests ODBC connection pooling was turned off with the ODBC Administrator applet.
- Verified that OLE DB MSDASQL registry setting was set to hex value FFFFFFFF.
- Verified the connection string did not modify the OLE DB Services.
Test results:| # | Description | Requests | DB Connections |
|---|
| 1 | OLE DBAnonymous access | 1,338 | 38 | | 2 | OLE DB Basic authentication one ID/PW | 1,034 | 50 | | 3 | OLE DB Basic authentication 10 ID/PW combinations to authenticate. | 1,151 | 223 |
- # - Test Number
- Description - Test authentication conditions
- Requests - Number of URL requests served
Source: The Web Application Stress Reports - DB Connections - Number of full connections made to the database
Source: Script file to capture the cumulative number of logins from the database server
Conclusion Based on the test results it is apparent that OLE DB database connection pooling performs better in nonauthenticated environments. In test #1 1,338 requests were served in a nonauthenticated environment by 38-database connections. In test #3 1,151 requests. were served in the authenticated environment using 10 IDsand it took 223 database connections. The test results showed that the full database connection process was performed significantly more often on those Web servers using the OLE DB session pooling mechanism in combination with the NT Basic Authentication security mechanism. When Basic Authentication or NTLM are used it can cause connections to be made on a per-user basis, thereby negating any benefit of the OLE DB resource pooling mechanism. The more unique users that login and create a database connection,- the more pronounced the increase in database connections. On a multiprocessor system, this can cause connections to accumulate even more rapidly. By default, ADO uses OLE DB session pooling to maintain a pool of connections to the database. In some cases, you might want to use ODBC connection pooling instead of OLE DB Session pooling. Note: We found that the ODBC connection pooling mechanism performed well under the NT Basic Authentication environment. Be aware that different users cannot access the same memory in the Windows NT security model under certain conditions. Make sure your ODBC driver handles this situation when connection pooling or you may run into the problems described in the following Microsoft article: Q183345.
References For additional information about connection pooling, please see the following article(s) in the Microsoft Knowledge Base: Q169470 INF: Frequently Asked Questions About ODBC Connection Pooling For additional information about connection ADO/ASP scalability, please see the following article(s) in the Microsoft Knowledge Base: Q176056 INFO: ADO/ASP Scalability FAQ
Q164221 : INF0: How to Enable Connection Pooling in an ODBC Application
Q166083 : INF: How to Enable Connection Pooling in an OLE DB Application
Q197459: PRB: ADO 2.0 and Later Return Connection Messages to Browser
About the Author With more than 10 years of experience in software development for a variety of financial systems, Steven Witkop is responsible for the overall architecture and development of multitiered Web applications and systems.
During his career at Electronic Data Systems (EDS), Steven has gained expertise in the design, development, and deployment of mission-critical Internet systems and architectures using Microsoft and Sun technologies. Steven is a SUN Java Certified Developer. Steven earned a bachelor's in Information Mangagement from the Temple University in Philadelphia and a master's degree in Information Mangement from Walsh College in Troy, Michigan. He can be reached at steven.witkop@eds.com
| 
