Performance degradation in December CTP of AS 2005 SP2

Has anyone had any similar experience with the December CTP of SQL Server 2005 and Analysis Services SP2 ?

We have a simple MDX query which returns 245 rows and 15 columns of data. When running this query through management studio under SP1 it takes 1 second, and when run through an XMLA query it takes 2 seconds.

We have now installed the december CTP of SP2 on an identical server and re-deployed / re-processed the identical cubes to this server. When running this first test query it takes consistently 6 seconds and 20 seconds when run through an XMLA query.

Does anyone have any hints on what could be different in the new CTP that could be behind this ? One thing that I have noticed is that running the MDX as an XMLA query produces tens of thousands of lines of messages under SP2 as opposed to the "Executing the query ... Execution complere" messages under SP1. Is there some server parameter that governs the trace / debug information created and could this be behind the performance degradation we are experiencing ?

Any ideas would be very gratefully recieved

It is very hard to tell anything sure about this.

A wild guess based on what Mosha has written in his Blog is that you have calculated members with a non-correct setting for non-empty behavior.

Edit: Have a look here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

Mosha have mentioned this several times but there are no clear guidelines for how to set this correctly.

If you do not have non_empty_behavior set for calculated members/measures or scripts, add it and see if it helps.

If you have non_empty_behavior set for a calculated members/measures or script, remove it and see if it helps.

Regards

Thomas Ivarsson

|||Can you share the query as well as related calculations?|||

Thanks for the replies and sorry for the change in username ... The problem was originally posted under a colleagues ID beforeI created my own.

The MDX Query pasted at the bottom of this message.

The query includes a calculation that I will try to move onto the cube so that I can benefit from the advice on non-empty behaviour and see what impact this has. However, the main concern is still the trying to understand the considerably worse performance under SP2.

Thanks again for any help.

WITH MEMBER [Time Intelligence].[Time Intelligence].[Used Methodology] AS

IIF ( NOT ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[2] ,[Time Intelligence].[Time Intelligence].&[1])) OR NOT ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[3] ,[Time Intelligence].[Time Intelligence].&[1])) , 2

, IIF ( ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[1] ,[Time Intelligence].[Time Intelligence].&[1])) , 3 , 1 ) )

SELECT

{ ([Time Intelligence].[Time Intelligence].&[1],[Display Instruments CC2])

, ([Time Intelligence].[Time Intelligence].[Used Methodology],[Display Instruments CC2])

} ON COLUMNS ,

[Display Countries] ON ROWS

FROM [IBLR Full IFS]

WHERE (

[Measures].[AmountsOutstanding cc2 display]

, [IBLR Currency].[Market].[Currency Total BK].&[1]

, [Quarter].[Year-Quarter].&[2006].&[20060930]

, [Rep Org].[Reporting Organisation].[DK]

, [MethodologyCollection].[Dim Pre Post].[Current Period Methodology]

, [IBLR Data Type].[Data Type BK].)

|||Can you try the query with connection string property "Cache Policy=9" to see if there is any difference in performance? Management Studio doesn't allow setting connection string property, you have to use MDX Sample from SQL Server 2000 or write your own C# program.|||

Thank you very much for the reply. I have implemented this as you suggested and tested the queries against SP1 and SP2 and the performance is now comparable (even slightly quicker under SP2, although drawing any conclusions from a single query taking 1.1 or 1.2 seconds is probably a little premature :-) ).

I have done a quick search on the online help and through google and have not been able to find any information on this setting, would you be able to provide a brief description of what this property ?

Thanks again for your help.

|||

In addition, I have found that inserting the following into the proprty list of the XMLA query has meant that the time running the XMLA version of the query through management studio has improved from 20 seconds to 1 second.

<DbpropMsmdCachePolicy>9</DbpropMsmdCachePolicy>

The online help (also the version of the help released with the December CTP) for this XMLA property indicates that the property is "Reserved for future use".

|||

This connection string property changes the default execution plan picked by AS in certain cases, for example when there is IIF in the calculation. AS picks the default execution plan as it is the best for most cases. Unfortunately in some cases, such as in your case, the execution plan picked by AS performs far worse than the alternative.

The default setting works best in most cases, therefore, users should only use "Cache Policy=9" when it has demonstrated significant performance boost for a given query. But setting "Cache Policy=9" is likely to cause performance degradation for many other queries. Since It is difficult to change server properties only for certain queries and it is risky to change the default setting, this server property is not widely publicized.

Ideally you should only turn this setting on for the problematic queries. If it is hard to do you have to verify that other queries are not adversely affected by the new setting. This is an area which is likely to see significant improvements in future versions of AS.