You experience a decrease in query performance after you perform an incremental process on a virtual dimension in SQL Server 2000 Analysis Services (896864)

SYMPTOMS

When you run a query against Microsoft SQL Server 2000 Analysis Services build 8.00.0909 or a later build, you may experience a decrease in query performance.

The problem occurs when the following conditions are true:

The query uses a virtual dimension or a changing dimension.
You run the query after you perform an incremental process on a virtual dimension.

Note You can examine the build version of SQL Server 2000 Analysis Services by verifying the file version of the C:\Program files\Microsoft Analysis Services\Bin\Msmdsrv.exe file.

CAUSE

SQL Server 2000 Analysis Services build 8.00.0909 and later builds contain changes to correct a problem where indexes and aggregations for virtual dimensions were not correctly updated after an incremental update on the virtual dimension. The solution that is introduced in build 8.00.0909 requires that the flexible aggregations and indexes be rebuilt.

Flexible aggregations are aggregations that apply to any non-all and non-leaf levels in virtual, parent-child, and changing dimensions. The flexible aggregations and indexes are rebuilt by using the lazy indexing process. This lazy indexing process runs on a background thread.

For large SQL Server 2000 Analysis Services implementations, this lazy indexing process can take a long time to finish. When the flexible aggregations and indexes are rebuilt, queries that would generally benefit from these aggregations must read data at the leaf levels of the involved dimensions. This behavior can cause a decrease in query performance.

RESOLUTION

To help administrators reduce the effect of missing flexible aggregations, SQL Server 2000 Analysis Services build 8.00.0950 and later builds contain the ability for administrators to control the order that partitions in a database are reindexed. Administrators can make sure that the most frequently queried partitions are reaggregated and reindexed first.

For more information about the partition prioritization in Analysis Services, click the following article number to view the article in the Microsoft Knowledge Base:

843262 FIX: You may notice that the query performance on an Analysis Services cube degrades temporarily after an incremental update processes the virtual dimensions

REFERENCES

For more information about the issue that is fixed in Analysis Services hotfix build 8.00.0909, click the following article number to view the article in the Microsoft Knowledge Base:

834490 FIX: MDX queries on a cube that contains a virtual dimension may not reflect changes after the incremental update on the virtual dimension