Microsoft KB Archive/319982

= FIX: Performance Improvements for OLE DB Session Pooling =

Article ID: 319982

Article Last Modified on 4/7/2006

-

APPLIES TO


 * Microsoft OLE DB 2.5
 * Microsoft OLE DB 2.6
 * Microsoft OLE DB 2.7
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q319982



SYMPTOMS
Web servers and COM+ applications may experience blocking and 100 percent CPU usage when they use OLE DB session pooling. Two significant changes were made to the Microsoft OLE DB session pooling code to help alleviate blocking and the 100 percent CPU usage that is occasionally seen in busy Web servers and Microsoft COM+ applications.



CAUSE
Information about the two primary reasons for the blocking and for the 100 percent CPU useage follows.

ReaderWriterLock Blocking
OLE DB uses a ReaderWriterLock implementation to protect the hash table that stores session pooling information. This logic permits multiple threads to read the hash table simultaneously, and then gives priority to threads that must write to the hash table. Conversely, threads that must write to the hash table must wait until there are no threads currently reading the hash table before modifications can be made. Additionally, all of the new (incoming) ReaderLock requests must wait until the WriterLock completes.

New database connections put a ReaderLock on the session pooling hash table as they try to obtain a connection from the pool. This lock can be held for the duration of a connection attempt to the backend database server. If the connection attempt experiences a problem, the connecting thread blocks all later WriterLock threads and ReaderLock threads. This block can cause the threads to enter a sleep state that generates 100 percent CPU usage.

Changes to the length of time that the locks are held permit the waiting WriterLock and ReaderLock threads to continue, even though connecting threads experience problems.

CoFreeUnusedLibraries and DllCanUnloadNow Blocking
COM+ Hotfix Rollup Package 18 (which is included in Windows 2000 Service Pack 3) introduced a change where the CoFreeUnusedLibraries function is called from single-threaded apartment (STA) threads. This generates increased calls to oledb32!DllCanUnloadNow which, in turn, causes Oledb32.dll to try to unload. Because of this, the OLE DB session pooling code tries to tear down the established session pools and to release resources. Because connections are almost always still in use, the attempt to free resources fails, and then COMSVCS sleeps for 15 seconds before it tries one more time. This also causes a 100 percent CPU usage, while the other threads in the process try to gain locks and resources that are held by the thread that is sleeping.

This problem does not occur with versions of Comsvcs.dll that are earlier than COM+ Hotfix Rollup Package 18.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

MDAC 2.5 Service Pack 2
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date          Version       Size        File Name -  16-Jul-2002   2.52.8916.0   1,401,094   Oledb32.dll 22-Jul-2002                            Q319982_MDAC25_x86.exe

MDAC 2.6 Service Pack 2
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Version       Size        File name -  30-Jul-2002  2.62.8916.0   1,138,688   Oledb32.dll 02-Aug-2002                           Q319982_MDAC26_x86.exe

MDAC 2.7
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Version       Size        File name -  13-Aug-2002  2.70.9001.15  413,696     Oledb32.dll 16-Aug-2002                           Q320696_MDAC27_x86_en.exe

WORKAROUND
IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

To work around this problem, use any of the following methods to turn off session pooling:  Set the OLEDB_SERVICES value for the provider to 0xfffffffc in the registry. Add OLE DB Services = -2 to the connection strings that the application uses.  If you use pure OLE DB code, add code that is similar to the following code to the initialization properties: // OLE DB Services InitProperties[0].dwPropertyID = DBPROP_INIT_OLEDBSERVICES; InitProperties[0].vValue.vt = VT_I4; InitProperties[0].vValue.lVal = DBPROPVAL_OS_ENABLEALL & ~ DBPROPVAL_OS_RESOURCEPOOLING; 

NOTE: When you turn off OLE DB session pooling, you incur a significant performance penalty when you use an OLE DB provider in an active Web server or in a COM+ component under high stress. Microsoft recommends that you use OLE DB session pooling in any application scenario where OLE DB sessions are rapidly opened and closed for performance reasons.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

<div class="moreinformation_section">

MORE INFORMATION
The OLE DB DllCanUnloadNow problem was initially released in earlier hotfixes that are outlined in the articles in the &quot;References&quot; section of this article. These same fixes are included in the versions that are discussed in this article, with modifications to the OLE DB ReaderLock code and to the OLE DB WriteLock code, so that you can obtain the benefit of both fixes in a single DLL.

This fix is currently available only for MDAC 2.5 Service Pack 2 (SP2), 2.6 SP2, and MDAC 2.7. It will not be backported to any earlier versions or releases. If you experience blocking issues that would benefit from this fix, Microsoft strongly recommends that you upgrade to these versions before you apply the hotfix.

<div class="references_section">