Dimension processing may take longer to complete after you apply SQL Server 2008 R2 Cumulative Update 4 for Analysis Services


Symptoms


After you install Microsoft SQL Server 2008 R2 Cumulative Update 4 for Analysis Services, it may take longer to process a dimension by using the ProcessUpdate option than it takes to run the same command on a build earlier than the fix. A profiler trace indicates that Analysis Services is spending additional time rebuilding aggregation indexes.

Cause


SQL Server 2008 R2 Cumulative Update 4 for Analysis Services included a fix for an issue that relates to errors in the handling of aggregation indexes during the ProcessUpdate part of dimensions. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2276495 FIX: "File system error" occurs when you try to run a process update operation on a dimension in Microsoft SQL Server 2008 Analysis Services and in Microsoft SQL Server 2008 R2 Analysis Services
In the issue that is resolved in the fix that is described in this article, indexes for aggregations were not being rebuilt correctly. The fix makes sure that indexes are built when they are needed. However, the method by which Analysis Services determines whether indexes have to be built schedules all aggregation indexes to be built if at least one index was not built. If some indexes were not built because of the AggIndexBuildThreshold setting, many aggregation indexes may be rebuilt unnecessarily.

Workaround


To work around this problem, change the Analysis Services server setting AggIndexBuildThreshold from the default value of 65,536 to 0, and then restart Analysis Services. This causes Analysis Services to build indexes for all aggregations, and the service does not try to build any indexes if dimension data has not changed.

Or, disable aggregation indexes completely by setting AggIndexBuildThreshold to a very large value such as 2,147,483,647. (AggIndexBuildThreshold is a signed 32-bit integer quantity.) Doing this eliminates the aggregation index building time from all processing but may also increase some query times. AggIndexBuildThreshold will not affect index building for partition data. It affects only indexes on aggregations.

Important When you install Cumulative Update 5 for SQL Server 2008 Analysis Services R2 Service Pack 1 or for SQL Server 2008 Analysis Services R2 Service Pack 2, the AggIndexBuildEnabled configuration setting is added to the Msmdsrv.ini file. By default, the value for the AggIndexBuildEnabled property is 0.

When the AggIndexBuildEnabled property is set to 0, aggregate index processing is disabled on partition index processing. Then, the problem that is described in this article no longer applies. To enable aggregate index processing, set the AggIndexBuildEnabled property to 1.