Hi All,

We have a cube (of approximately 500 dimensions/attributes/hierarchies,
closer to 30 true dimensions), and ten measure groups.

This cube is running on AS 2005 Enterprise, on Windows 2003 Enterprise,
with /3GB in the boot.ini. Disk IO is not a problem - we have 24 disks
running side-by-side, with data being striped across all 24. The
server has 8GB physical RAM.

We frequently get 'not enough storage' problems, and generally sluggish
performance, when administering / modifying the cube through SSMS,
whether this be on the server itself or remotely. The most notable
place this happens is in the 'Design Aggregations Wizard', which will
frequently either return with 'not enough storage', or will take five
or so minutes just to get to the 'Initializing...' phase.

I'm interested in knowing whether we're alone in experiencing these
problems, or whether they're generally accepted by the current user
base (or perhaps, generally 'tolerated').

Any suggestions as to how to fix up any issues appreciated!

Regards,

Will.

do you use x32 or x64 server?
have you identify which disk is full? (if there is a full disk)
where is the source database? on the same server?

do you use partitions?

<ggroups (AT) crazy-pug (DOT) co.uk> wrote

It's an x32 server, two dual-cores, CPU is not a problem, although 3GB
memory limit could be.

None of the disks are even close to being full, so it's not that,
unfortunately.

The source DB is admittedly on the same server, but the memory / CPU
configuration together with the disk array shouldn't make this _that
much_ of an issue.

Each of the ten measures groups has one partition. This is a
potentially bad thing, right?

Jéjé wrote:

partitionning is good with large tables to improve the process performance
and the query performance.
regarding your hardware, for less then 1million of rows, don't loose to many
time on this
after, try to spread your largest tables into partitions.

where do you store the tempdb database?
how many memory is really consumed by SQL and AS?
have you activated the /3Gb switch in the boot.ini?

x64 servers really help the memory management and scalability.; can you
switch your server to this version?

<ggroups (AT) crazy-pug (DOT) co.uk> wrote

It's an x32 server, two dual-cores, CPU is not a problem, although 3GB
memory limit could be.

None of the disks are even close to being full, so it's not that,
unfortunately.

The source DB is admittedly on the same server, but the memory / CPU
configuration together with the disk array shouldn't make this _that
much_ of an issue.

Each of the ten measures groups has one partition. This is a
potentially bad thing, right?

Jéjé wrote:

I'll take a look at creating a couple of partitions - as most of the
data analysis will be for the current year, that'd be a good place to
start! Our largest fact table contains a couple of million
records, so nothing _that_ big.

Am not sure where the tempdb is - most likely on the same disk as SQL
Server and AS, although this disk is cheap-wrt-IO cost, so I'd hope
that's not going to be that much of an issue.

Reported memory consumption (from Task Manager) is 1.8GB for AS, 1.2GB
for SQL Server (conveniently adding to 3GB... Mmm... The /3GB flag
_is_ set, and /PAE is also enabled for the SQL Server.

We have had some success by setting the (advanced) AS settings for
LowMemory and MaxMemory to 50(%) and 65(%) - this seems to avert any
out of memory errors, and I've also found that the Design Aggregations
Wizard is noticeably more performant, although still slower than I
would've liked.

x64 would be a good move, but our cube is tiny compared to some, we
just feel we should be able to get by without piling in more hardware!

Thanks Jéjé!

Jéjé wrote:

Can you explain this a little more:
What does this 500 represent?

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.

<ggroups (AT) crazy-pug (DOT) co.uk> wrote

I'll take a look at creating a couple of partitions - as most of the
data analysis will be for the current year, that'd be a good place to
start! Our largest fact table contains a couple of million
records, so nothing _that_ big.

Am not sure where the tempdb is - most likely on the same disk as SQL
Server and AS, although this disk is cheap-wrt-IO cost, so I'd hope
that's not going to be that much of an issue.

Reported memory consumption (from Task Manager) is 1.8GB for AS, 1.2GB
for SQL Server (conveniently adding to 3GB... Mmm... The /3GB flag
_is_ set, and /PAE is also enabled for the SQL Server.

We have had some success by setting the (advanced) AS settings for
LowMemory and MaxMemory to 50(%) and 65(%) - this seems to avert any
out of memory errors, and I've also found that the Design Aggregations
Wizard is noticeably more performant, although still slower than I
would've liked.

x64 would be a good move, but our cube is tiny compared to some, we
just feel we should be able to get by without piling in more hardware!

Thanks Jéjé!

Jéjé wrote:

Approximate breakdown is:
30 dimensions.
270 hierarchies.
200 attributes.

There are additionally ten measure groups, each containing typically
between 1 and 5 measures.

Thanks Akshai.

Akshai Mirchandani [MS] wrote:

So you have 200 attributes spread over 30 dimensions with about 70 user
defined hierarchies and 200 attribute hierarchies?

Can you check the virtual memory settings on the machine? Quite probably you
need to increase the size available -- my guess is that AS starts using more
memory to do the aggregation design and the OS exceeds the allocated page
file space and fails with the storage error...

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.

<ggroups (AT) crazy-pug (DOT) co.uk> wrote

Approximate breakdown is:
30 dimensions.
270 hierarchies.
200 attributes.

There are additionally ten measure groups, each containing typically
between 1 and 5 measures.

Thanks Akshai.

Akshai Mirchandani [MS] wrote: