Re: SSAS 2005 perform worse performance that 2000
- From: "Ramunas Balukonis" <ramblk2@xxxxxxxxxxx>
- Date: Mon, 13 Nov 2006 16:29:24 +0200
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@xxxxxxxxxxx> wrote in message
news:EDD87352-4AEC-46C4-B99D-70A3EE5B79D6@xxxxxxxxxxxxxxxx
insure that your dimensions are correctly optimized and you use userin
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
a dedicated measuregroup? (1 by dcount measure)to
"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
Totalsql 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,
30000.queries from cache filtered 500000, Total queries from cache direct
dimensionsSo, 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
partition?this counter always shows 0. Is it normal behaviour?
One more thing - do I need to specify "slice" property for the
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:
- References:
- SSAS 2005 perform worse performance that 2000
- From: Ramunas Balukonis
- Re: SSAS 2005 perform worse performance that 2000
- From: Jeje
- SSAS 2005 perform worse performance that 2000
- Prev by Date: Re: Accessing lower level members
- Next by Date: Re: Accessing lower level members
- Previous by thread: Re: SSAS 2005 perform worse performance that 2000
- Next by thread: Re: SSAS 2005 perform worse performance that 2000
- Index(es):
Relevant Pages
|
