How to set the memory conservation threshold for SQL Server 2000 Analysis Services 32-bit edition on a 64-bit version of Windows (917829)

SUMMARY

When you install Microsoft SQL Server 2000 Analysis Services 32-bit on a 64-bit version of Microsoft Windows, you can take advantage of the 4 gigabyte (GB) memory space that is available in the Windows on Windows 64 (WOW64) environment.

This configuration enables SQL Server 2000 Analysis Services to obtain more memory than the 3 GB memory limit that exists on a 32-bit version of Windows. However, because of an algorithm that the SQL Server 2000 Analysis Services connection manager uses, the maximum supported memory conservation threshold for SQL Server 2000 Analysis Services 32-bit is 3,817,748,706 (0xE38E38E2) bytes when SQL Server 2000 Analysis Services 32-bit runs on a 64-bit version of Windows.

If you specify a memory conservation threshold value that is larger than 3,817,748,706, you may experience one of the following symptoms:

The SQL Server 2000 Analysis Services service appears to stop responding when the service starts.
A client connection to the server that is running SQL Server 2000 Analysis Services ends with a time-out error.
Commit operations perform slowly on the server that is running SQL Server 2000 Analysis Services.

Note SQL Server 2000 Analysis Services and Microsoft SQL Server 2005 Analysis Services versions for IA-64 and x64 platforms do not have this limitation.

MORE INFORMATION

The HighMemoryLimit registry value sets the maximum memory conservation threshold for SQL Server 2000 Analysis Services 32-bit. The LowMemoryLimit registry value sets the minimum memory conservation threshold. These two registry values are located under the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion

When a client tries to connect to the server that is running SQL Server 2000 Analysis Services, the server checks whether the current memory usage has exceeded the HighMemoryLimit registry value by 12.5 percent. To calculate this value, use the following formula:

HighMemoryLimit + (HighMemoryLimit/8)

Note In this formula, HighMemoryLimit represents the value of the HighMemoryLimit registry value.

If the server detects that the memory usage exceeds the HighMemoryLimit registry value by 12.5 percent, the connection request is suspended. The suspension will not be released until one of the following events occurs:

The cleaner thread triggers an event that notifies the server that memory cleaning has completed.
Two minutes pass.

This check is a new feature in SQL Server 2000 Analysis Services Service Pack 3 (SP3). This feature is intended to prevent new connections from causing the server to run out of memory before the cleaner thread can reduce the server memory usage.

If the HighMemoryLimit registry value is set to a value that is larger than 3,817,748,706, the check will generate a result value that exceeds the maximum supported memory. Therefore, data overflow will occur, and the result value will be set to a relatively low value that is equal to or less than 512 MB. In this case, if the memory that the dimensions use is larger than this low result value, the server will suspend any new connection requests. The cleaner thread is not fired until the memory usage is larger than the midpoint between the HighMemoryLimit registry value and the LowMemoryLimit registry value. Therefore, no event is triggered to notify SQL Server that memory cleaning has occurred. In this case, the new connection is delayed for 2 minutes. Therefore, a client connection may experience a time-out. You may also experience slow security evaluation when the SQL Server 2000 Analysis Services service starts, and commit operations may process slowly.