This article describes how the Microsoft OLE DB provider for SQL Server (SQLOLEDB) that is included with Microsoft Data Access Components (MDAC), allocates memory for the IRowset object. The article also discusses the differences in the memory allocation scheme that is used by SQLOLEDB in MDAC 2.5 and in earlier versions of MDAC.

The SQLOLEDB provider is implemented in the Sqloledb.dll file. Before MDAC version 2.5, SQLOLEDB used hard-coded decision logic for reserving and allocating memory for an IRowset object.

The following are the steps in the memory allocation process that is used by the SQLOLEDB provider to allocate memory for an IRowset object in MDAC versions earlier than MDAC 2.5:

1. Try to reserve 7 MB of virtual memory.
2. If 7 MB of virtual memory cannot be reserved, try to reserve 2 MB of virtual memory.
3. If the attempt to reserve 2 MB of virtual memory fails, try to reserve 1 MB of virtual memory.
4. If all three attempts to reserve virtual memory fail, return an error that indicates that the computer memory does not have sufficient space.

In MDAC version 2.5 and later, the memory allocation scheme that is used by the SQLOLEDB provider has changed. The memory allocation is now performed by using smaller virtual memory blocks. The default memory block size is 1 MB, and the default limit for memory blocks is 500 memory blocks.

Memory allocation by the SQLOLEDB provider in MDAC versions earlier than MDAC 2.5

The Microsoft OLE DB provider for ODBC (MSDASQL) uses a registry setting to adjust memory allocations. However, in MDAC versions earlier than MDAC 2.5, the SQLOLEDB provider uses the memory allocation process that is mentioned earlier. This memory allocation cannot be externally adjusted by the users.

The memory allocation is performed by first reserving the virtual memory by using the VirtualAlloc function. Subsequently the virtual memory that is reserved is committed when it is required. However, when a process such as Microsoft SQL Server uses the SQLOLEDB provider, the virtual memory may be quickly fragmented, or the virtual address space may be exhausted. In this situation, the SQLOLEDB provider may return an error that indicates that the memory is not sufficient.

Memory Allocation by the SQLOLEDB provider in MDAC version 2.5 and later versions

In MDAC 2.5 and later, SQLOLEDB uses the memory block allocation scheme. The new memory block allocation scheme helps to reduce the virtual memory address footprint of the SQLOLEDB provider and helps to reduce the virtual memory address fragmentation by reusing memory blocks. The memory block allocation scheme also provides a set of registry keys that enables users to adjust the memory block size and the total number of memory blocks that can be allocated.

Memory Block Size

To modify the memory block size, locate, and then change the value of the following registry key:

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MDAC\ReservedMemorySize

The ReservedMemorySize registry key contains a value in bytes and determines the size of the memory block allocations.

The ReservedMemorySize registry key value must not be less than 64 KB, because the VirtualAlloc function call is still used to allocate the memory block. The granularity in the VirtualAlloc function is limited to 64 KB. The ReservedMemorySize registry key value must always be on a Microsoft Windows NT page boundary of 4 KB. The size of the memory block must be a minimum of 65535 bytes, or the memory block must be in multiples of 4096 bytes if it is greater than 65535 bytes. The reserved memory size can be calculated as in the following expression:

ReservedMemorySize = 65535 + (N * 4096)

where N is a whole number that is greater than or equal to zero (0).

Maximum Number of Memory Blocks

To modify the maximum number of memory blocks, locate, and then change the value of the following registry key:

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MDAC\MaxReservedBlocks
The MaxReservedBlocks registry key contains a value that indicates the total number of memory blocks that can be reserved. If the MaxReservedBlocks registry key does not exist in the Windows registry or, if the data type of the registry key is not REG_DWORD, the default number of reserved memory blocks is 500.

Note To make sure of optimal performance, you must modify and test these registry key values completely before you deploy the changes in a production environment.

Resolve memory allocation problems with COM objects in SQL Server

While creating a COM object in SQL Server, you may receive an error from the SQLOLEDB provider that indicates that the memory is not sufficient. To resolve the problem, use one of the following methods:

• Run the COM object as out-of-process For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  198891  (http://kbalertz.com/Feedback.aspx?kbNumber=198891/ ) INF: Enabling DLL-Based COM Object Execution Outside SQL Server
• Upgrade to MDAC version 2.5 or a later version When you upgrade to MDAC 2.5 or a later, the memory usage decreases by about 40%.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: