Re: Performance - Best Practices
- From: "Jeje" <willgart@xxxxxxxxxxx>
- Date: Wed, 19 Jul 2006 18:13:39 -0400
do you use AS2000 or 2005?
do you use x64 or x32 server?
have you used the usage based optimization wizard to add aggregations?
do you have calculated members? calculated sets?
We have a server with 16Gb, Windows x64, SQL 2005 x64, 2 * Dual Core Opteron
for the moment only 2 years of history, in all my partitions in all cubes I
have 250 millions of rows by year (biggest fact contain 50 millions / year,
and the cube in front of this fact provide standard SUMS and DCount!)
the response time is excellent, the first access is allways slow (cold
cache), but I can drill from years to days in seconds, and add filters and
the response time still good.
I'm playing with 2 dcount measures + a ratio between these 2 measures +
ratio between 2 sums.
The memory is shared between SQL, AS and reporting services on this server,
and AS don't use more then 2Gb today (but when we'll be in production with
more historical data the memory usage will be higher)
in your case, try to disable the hyperthreading, sometimes this feature
produce bad results.
"Todd" <toddkitta@xxxxxxxxx> wrote in message
news:1153342986.762069.317490@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello,
I am having some pretty major performance issues with a cube I've
built. My goal for this post is to give specifics related to the data
and Analysis Services hardware to try and get some feedback from the
community on whether or not I am having valid expectations
performance-wise.
Hardware:
-Two dual-core Xeon 2.8GHz processors
-3GB RAM
Data:
The data is retail transaction data which is at the line item level.
The most recent years have 50-60 million rows and I am partitioning by
year. In addition, each transaction has related discount and tender
(payment) data. The tender data has approximately 15-20 million rows
per year while the discount data has approximately 10-15 million rows
per year.
In my cube, each of these tables have their own separate fact group. I
am pulling data from all three fact groups to create quite a few
calculated members which build on each other to finally arrive at
calculations such as GAAP Sales, sales for a particular product type,
etc.
I believe I have my hierarchies and relationships set up correctly in
my dimensions. In addition I have aggregations designed on each
partition at about the 40% level.
Am I having reasonable expectations that data should be able to come
back fairly quickly? I have some reports which take 10-15 minutes that
bring back quite a lot of data for the whole company across the last
two years. However, even some simple slicing and dicing in the cube
browser can be slow. What is interesting to me is that even if I drop
my time dimension on the rows axis before adding any measures or other
dimensions, it can take 20 seconds or more to respond. All the while
when reports are running or when I am browsing the cube myself, the CPU
(all 8 in Task Manager [dual dual-core processors]) are spiking at
100%.
While I believe I can get some good responses from MS folks and MVPs
for my own benefit, I think any thoughtful posts will benefit the
community as a whole by setting performance expectations and pointing
out potential pitfalls. I have found that there are not a ton of good
resources out there on performance, so I appreciate any tips anyone can
offer.
Thanks!
Todd
.
- Follow-Ups:
- Re: Performance - Best Practices
- From: Todd
- Re: Performance - Best Practices
- References:
- Performance - Best Practices
- From: Todd
- Performance - Best Practices
- Prev by Date: Re: SSAS2005 error while attempting to processing parallel
- Next by Date: Re: Performance - Best Practices
- Previous by thread: Performance - Best Practices
- Next by thread: Re: Performance - Best Practices
- Index(es):
Relevant Pages
|
