FIX: Performance Improvements for OLE DB Session Pooling (319982)

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

   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

   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.