FIX: Analysis Service Uses Large Amounts of Client Memory for Queries (316335)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q316335
BUG #: 12738 (plato7x)

SYMPTOMS

When you execute queries from an Analysis Services client computer, the RAM usage on the client may grow until all the available client memory is in use by the Analysis Services client application.

This problem occurs when the query contains many calculated members.

CAUSE

The Microsoft OLE DB provider for OLAP version 8.0 that is provided with Analysis Services 2000 contains a new formula cache feature. Before this update, the formula cache feature did not properly restrict memory usage based on the client cache size setting.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000 Analysis Services. 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

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.


Hotfix

Note The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix should have the following file attributes or later: 
  Date         Time          Version        Size         File name   
  --------------------------------------------------------------------

  03-JAN-2002  19:04       8.0.562.0     213,572 bytes   Msmdcb80.dll
  19-MAR-2002  13:57       8.0.0.601   4,043,328 bytes   Msmddo80.dll
  19-MAR-2002  13:56       8.0.601.0   1,008,192 bytes   Msmdgd80.dll
  19-MAR-2002  12:53       8.0.601.0   1,774,148 bytes   Msmdsrv.exe
  19-MAR-2002  13:57       8.0.601.0   2,024,016 bytes   Msolap80.dll
  18-APR-2001  00:23   2000.80.382.0     815,676 bytes   Sqldata.dll
  19-MAR-2002  13:56   2000.80.534.0     590,396 bytes   Sqlsort.dll
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Analysis Services Service Pack 3.

MORE INFORMATION

The Microsoft OLE DB provider for OLAP version 8.0 contains a new formula cache feature. The formula cache permits the provider to avoid re-evaluating calculations for each cell that requires the calculation. The formula cache stores the cell path and value of the cell for all cells that have a calculated member on one or more of the dimensions for the cell, which also includes calculated measures. If Analysis Services derives the cell from another calculated cell or member, the formula cache stores multiple entries.

Because it is possible for a calculated member to have a value even if all "real" members for the same set of coordinates are NULL, the formula cache must store the path information for each calculated measure even if it has a NULL value. If the formula cache does not store the NULL value members, Analysis Services has to re-evaluate those members each time a query that includes the member executes. Analysis Services handles non-calculated members in a query by the "regular" cache. Unlike the formula caching mechanism, the regular cache only stores rows that do not have NULL values because Analysis Services knows that if a member is missing from the "regular" cache that it does not have a value.

Analysis Services uses the client cache size connection string parameter to control the memory that is used for the client side cache. However, before the fix the memory in use by the formula cache was not included in the cache memory calculation. The updated version of the provider that is provided with this fix uses the combined size of the regular and formula cache when limiting memory use to the value specified by the client cache size parameter.

Note The default value for the client cache size is 25 percent of the physical RAM available on the client. For more information, see the "Client Cache Size Property" topic in SQL Server 2000 Analysis Services Books Online.

REFERENCES

SQL Server 2000 Analysis Services Books Online; topic: "Client Cache Size Property"
Modification Type:MinorLast Reviewed:10/5/2005
Keywords:kbSQLServ2000sp3fix kbbug kbfix KB316335
©2004 Microsoft Corporation. All rights reserved.