Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

 

The other day, I was working on a level based dimension hierarchy and the fact table with rather very few records.

I had applied all the dimensional modeling recommendations such as,

1. for each attribute,

• disable unary operator and customrullupformula if not needed.
• remove unwanted attributes
• isaggregatable property = true
• attributehierarchyenabled = false if not needed
• attributehierarchyvisible = false if you need to access the attribute but not visible in dimension browser
• define attribute relationship

2. In cube, all dimension to cube relationship is "Regular"

3. In cube partition, updated all the statistics (which is evident in above screenshot)

Testing process:

1. I took a set of leaf fact table level data set of five rows. In the profiler , the "Query Dimension" went on and on

Finally the query aborted and a "fatal error" message came:

Analysis Services had taken entire available RAM for this small query:

It seems there is a post SP2 hotfix which sounds like to adddress the above problem. A similar issue was reported in Analyiss Services 2005 forum from where I got the below information.

938077 (http://support.microsoft.com/kb/938077/)
FIX: The client application stops responding, and the Msmdsrv.exe process uses all the available memory after you perform the filtering operation and the browsing operation against an instance of SQL Server 2005 Analysis Services

In fact, there are a bunch of problem addressed in post SP2 hotfix.

Since, these hotfixes are not fully regression tested by Microsoft as yet, it is available through request only here.

Once, I apply the hotfix, would check if the above get resoled. I would update this blog then.