Re: SSAS 2005 perform worse performance that 2000
- From: "Jeje" <willgart@xxxxxxxxxxx>
- Date: Sat, 11 Nov 2006 22:39:53 -0500
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@xxxxxxxxxxx> wrote in message news:1163056194.603024@xxxxxxxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: SSAS 2005 perform worse performance that 2000
- From: Ramunas Balukonis
- Re: SSAS 2005 perform worse performance that 2000
- References:
- SSAS 2005 perform worse performance that 2000
- From: Ramunas Balukonis
- SSAS 2005 perform worse performance that 2000
- Prev by Date: Re: Help with calculations
- Next by Date: Re: Connecting to SSAS from Network Service account
- Previous by thread: SSAS 2005 perform worse performance that 2000
- Next by thread: Re: SSAS 2005 perform worse performance that 2000
- Index(es):
Relevant Pages
|
