Hi,

we have migrate our cubes from our 32 bit olap 2000 machine to x64 bit 2005.
But after migrating seems 2005 perform worse performance in many cases that
2000. On our new server we have 8CPU and 64 GB of memory, i gave 20 GB to
sql server and leave all SSAS memory options default because MS does not
recomend change memory settings.
Our cube files takes about 35 GB, but database Estimated Size from cube
properties shows only 206 MB. Cube is partitioned.
Looking at perfom, I see that Total queries from file is about 2000, Total
queries from cache filtered 500000, Total queries from cache direct 30000.
So, only few percent of queries answered from files, all others - from
cache. In task manager msmdsrv takes 2 GB of memory. But in perfom MSAS
2005: Cache shows only 700 MB of memory and is the same as Memory
AggcacheKB. Is it OK?
Also, in the same MSAS 2005: Misses/sec is about 50 percent of Lookups/sec.
Is it normal? So, every second query pass cache? Why?
Also, interesting case with counter MSAS 2005: Proc aggregations memory size
bytes. I used this perfom in SQL 2000 to see is there any job creating
aggregates, especially it was usefull when I did incremental dimensions
update. In SQL 2005 this counter shows nonzero only when partitions process
are in "aggregate" state. But when I do incremental update for dimensions
this counter always shows 0. Is it normal behaviour?
One more thing - do I need to specify "slice" property for the partition? I
read abouty this option I need to set only for ROLAP partitions. I tried to
set slice property, but the same result. I missed way to known how many
partitions is partcipating into olap query.

Please, is there any suggestions how to optimize SSAS 2005 cubes.

Ramunas Balukonis

insure that your dimensions are correctly optimized and you use user
hierarchy.
use the rigid relationship, and keep the flexible option only as the last
option.

set the aggregationusage to full for your most requested attributes.
In my case this step really improve the performance by setting up a better
aggregation design.
The slicer parameter is recommended but optionnal for your partitions

can you describe in which cases the performance is bad?
how many time the query takes? (in AS2000 & 2005)
do you use calculated members?
do you have distinct count measures? if yes, does these DCount are stored in
a dedicated measuregroup? (1 by dcount measure)


"Ramunas Balukonis" <ramblk2 (AT) hotmail (DOT) com> wrote

When you say "migrate", do you mean you used Migration wizard, or you
actually re-designed system? There are so many changes in SSAS 2005 and in
my opinion you do need to adjust your design to take advatage of them.
Have you went through each dimension and adjusted attribute relationship?
How many dimensions and how big are some of them?







"Ramunas Balukonis" <ramblk2 (AT) hotmail (DOT) com> wrote

Jerome,
I'll try recomendations about rigid and especally AggregationUssage (I
forgot about this). flexible/rigid are alomst the same as changing dimension
type on AS 2000?
I use lots of calc members, and some of them performs worse performance in
AS 2005, especially with SUM function.
Today I installed SQL server SP2 CTP on our DEV machine and performance
dramatically grew up! Also, warning about unnatural hierarchies is great in
SP2 CTP! But who known, when SP2 will be realeased? This is clue to our
problems.

Ramunas Balukonis


"Jeje" <willgart (AT) hotmail (DOT) com> wrote

SP2 will arrive shortly, its a prerequisite for Office 2007 and office 2007
is here!!!


"Ramunas Balukonis" <ramblk2 (AT) hotmail (DOT) com> wrote

Correction: SP2 is not precisely a pre-requisite for Office 2007 -- but
without SP2 some of the new Excel 2007 features against AS will be disabled.

SP2 should become available around the first quarter of 2007.

Thanks,
Akshai

--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Jeje" <willgart (AT) hotmail (DOT) com> wrote

Akshai, what are exactly these features that are disabled without AS
SP2?
Thank you

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Akshai Mirchandani [MS] ha scritto: