The issue is about processing huge ammounts of data in parallel processing.

We've tried to process two (or more) partitions simultaneously, for
incremental update of 1 or more cubes, but this does'n give us any
performance improvement - as the documentation says. It is not the SQL
Server response to AS queries that is drawing us back but the computation
(creation) and the merging of newly created partitions.

We've read all the documentation we can find, and make all the optimisations
that were recommended there.
All that is happening on an 8 processor HP machine with plenty of disk space
and memory.
Furthermore adding a RAM (Solid) drive on which we've located the cubes and
the swap doesn't give any performance improvement eighter. We've expected
the processors to jump to higher levels of usage but it seems that, Analysis
Server just serializes the processing, spending the same ammount of time,
and does not get any improvement in performance from the increase of I/O
speed and parallel processing.
More than that, even if the memory settings are well done, AS does not take
advantage of it (does not use it to the limits) and swaps a tremendous
ammount of pages to disk.

Also AS has the trend to degrade it's performance during time, and often
enough hangs (I'm tired of that!!!!!!) without any particular reason, and
without logging any errors, performing the same tasks that 5 minutes ago
worked fine. We even need to restart the AS service programatically, from
within the application because of those hangs...

Is there anybody else wich worked with large ammount of data and with
parallel processing of cubes (partitions) to can change some ideas?

--
Horatiu Ripa
Software Development Manager
Business Logic Systems LTD
21 Victor Babes str., 1st floor, 3400 Cluj-Napoca, Romania
Phone/Fax: +40 264 590703
Web: www.businesslogic.co.uk

This email (email message and any attachments) is strictly confidential,
possibly privileged and is intended solely for the person or organization to
whom it is addressed. If you are not the intended recipient, you must not
copy, distribute or take any action in reliance on it. If you have received
this email in error, please inform the sender immediately before deleting
it. Business Logic Systems Ltd accepts no responsibility for any advice,
opinion, conclusion or other information contained in this email or arising
from its disclosure.

First, can you give us an overall picture of your sizing:
1) # of cubes, # of dimensions (with member counts), etc.
2) your hardware -- we know it is 8CPUs, how much memory, type of I/O
subsystem, etc.
3) your AS memory settings (high and low memory limits, process buffer size,
etc.) -- You can get these by bring up Analysis Manager, right-click the
server name,and select "Properties..." -- tell us what the values are in the
General, Environment, Processing and Logging tabs.
4) and finally after you restart your server (or the AS service), what does
the virtual memory for the msmdsrv process stablize at?

Second, when you are processing, what does your system look like? Are all 8
CPUs had high utilization? What is the memory usage of the msmdsrv process
like? What is the I/O characteristics like against the volume containing the
AS data folder and the volume containing the AS temp folder.

Lastly, in particular, you said "More than that, even if the memory settings
are well done, AS does not take advantage of it (does not use it to the
limits) and swaps a tremendous amount of pages to disk." -- can you explain
what you measured and how you came to that conclusion?

If you haven't already looked at it, you might find the information provided
in this white paper useful:
http://www.microsoft.com/technet/pro...e/AnSvcsPG.asp

Thanks,
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Horatiu Ripa" <horatiu.ripa (AT) businesslogic (DOT) co.uk> wrote

Parallel Processing does work. I have been using it
myself. My processing times are:

1 partition (alone), 1.5 million rows: 10 minutes
4 partitions (in-parallel), 1.5 mil rows each: 12 minutes

All the 4-CPUs are being used at about 50-55%

Then I move on to process another 32 of the partitions...

Perhaps your response to the Microsoft rep. will help us
help you.

Regards,

Sunil.

We've used "Microsoft SQL Server 2000 AS prformance guide" even if it does
not provide a "guide" just places where you can modify params.

1.
- MOLAP
- 3 cubes with 3-4 measures (numeric values) in each cube.
- 10 shared dimensions - 9 small as count (7 less than 10, 2 less than
1000), 1 large (btw. 1 to 5 millions)
- Star simple schema, no views to access SQL data in populating partition
and/or dimensions, no snowflake on dims

The connection to AS is opened once for the same incremental update - let's
say that we process 3 partition, one for each cube, in parallel. Than a
connection is kept alive for each cube, and the future increments are done
on the same connection for the same cube.

2.
HP DL760 G2
- Proc : 8 Xeon 2000 MHz
- RAM : 8 GB SDRAM 133 MHz
- HDD : 2 * Ultra3 10000 RPM 18.2 GB
(SmartArray 5i with RAID-0)
- LAN-Control : Generic 10/100 TX
- LAN-Data : Gigabit NC7770
- FibreChannel : Emulex FTRJ-8519-1-2.5

Win2k with SP4
SQL 2000 + AS with SP3

3.
boot.ini configured with /3GB

HighMemoryLimit ~ 2.6GB
LowMemoryLimit ~ 1.5GB

InitWorkerThreads = 16
PoolProcessThreads = 60
PoolWorkerThreads = 40
WorkerThreads = 20
ProcessThreads = 20
ProcessReadAheadSize = 8MB
ProcessReadSegmentSize = 500MB

The files for cubes; temp files were set on a different drive than the
OS+software ones
The swap was also directed to another distinct drive

We've used a TI solid drive where we've put the cubes, temp files and swap.
No improvement.

The log was directed into a file locally and set to log to many events.
The repository was put on the SQL

Note:
We've tried with a lot of other values for those settings and the peformance
does not seem to be improved. All we've achieved is decrease of performance
or AS failures; (VLDM - as an example - failed systematically)

3.
The memory used by msmdsrv "stabilizes" (except for the memory leak that
after few hours/days of processing data, eventually makes AS to hang) after
restart at about 1.5GB but only 700MB on physical RAM with a lot of page
faults deltas (about 2-300). Don't ask me why....
No, the CPU's are used only at about 5-10%


Basically, in a cycle, all 3 cubes are incremental updated. Let's say that
the dimension remains unchanged an the data pushed in the cubes is about
500.000 records in each processing cycle for each of the cubes.

We've tried a lot of combination of config settings, a lot of combination of
parallel processing (parallel processing of multiple partition for one cube,
parallel processing of one partition for more cubes, parallel processing
with more partitions for each cube in the same time). The performance that
we've obtained was never better that a simple serialization of partition
processing.

And Microsoft says (quote):
Number of processors Partitions to process in parallel
Four Two or three
Eight Four or five
Sixteen Four to six, measured on SQL Server 2000 (64-bit)



Can anyone "guide" me? As I already sayed MS tells you "where" to modify
some setting and doesn't give anyone a damn clue on the values that you can
put there in terms of performance. Anyway I think that we've tested about 2
or 300 combinations of configuration values.

--
Horatiu Ripa

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

asp

Hope you got a chance to read my posting in this thread.

Some more questions remain.

1. How many fact records per partition?
2. How long is slow? Minutes/hours?
3. How is the connection between AS2000 and where is the
source data (star schema) in SQL2000?
4. Did you optimize your cubes?

Regards,

Sunil.

1. We've tested this with several no. of records (btw. 150000 to 3000000).
There is an enhancement when creating larger partition but no difference
btw. parallel and serial processing of them.
2. It depends. It works in minutes, depending on no. of records/partition.
An average speed is about 5000-6000 records/sec. But it degrades in time.
3. Very good (fiber) or they can be even on the same machine - having 1 or 2
processors for SQL and all SQL server stuff on other drive. Again, not
querying the SQL (according to logs) is the drawback but the rest of
processing.
4. Yes the cubes are optimized. We've done all the optimization possible,
from minimizing the data types to the smallest type that can fit to having a
star schema that queries only tables (not views). We've followed all the
steps in optimization that MS recommends.


The main issue here is not if everything is optimized, or if we can achieve
a higher speed by making some additional optimizations. The issue is:
Why the parallel processing of partitions does not work faster than the
serial processing?
Why processing two partitions (in parallel) for the same cube having 1500000
records each does not work faster than processing one with 3000000? Also why
processing in parallel two partitions for the same cube having 1500000
records each takes the same ammount of time that processing the same two
partition one after the other?

Note: The logs show us that the processing of partitions is really parallel,
at least when starting.

--
Horatiu Ripa
Software Development Manager
Business Logic Systems LTD
21 Victor Babes str., 1st floor, 3400 Cluj-Napoca, Romania
Phone/Fax: +40 264 590703
Web: www.businesslogic.co.uk

This email (email message and any attachments) is strictly confidential,
possibly privileged and is intended solely for the person or organization to
whom it is addressed. If you are not the intended recipient, you must not
copy, distribute or take any action in reliance on it. If you have received
this email in error, please inform the sender immediately before deleting
it. Business Logic Systems Ltd accepts no responsibility for any advice,
opinion, conclusion or other information contained in this email or arising
from its disclosure.

"Sunil Kadimdiwan" <sunil_kadimdiwan (AT) hotmail (DOT) com> wrote

I always had luck with parallel processing. Are you using
ParallelProcess utility?

My suggestion at this point, contact MS Tech support
(there are few very good people in that group) or ofcourse
an experienced consultant.

Good luck.

Sunil.

Nope. We have not ParallelProcess utility. Is there a way to obtain it free,
cos' I don't feel like spending company money for something that maybe will
not help us?

--
Horatiu Ripa

It is on the SQL Server 2000 Resource Kit.
If someone in your company has a TechNet subscription, they can find it on
the Resource Kit CD that is distributed with TechNet. I believe that it also
might be in MSDN (I am not 100% sure) -- if all else fails, go to your local
bookstore or see if an associate might already have a copy.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Horatiu Ripa" <horatiu.ripa (AT) businesslogic (DOT) co.uk> wrote

ParallelProcess utility is found on SQLServer 2000
Resource Kit. To me it was worth it, otherwise I would
have spent days (if not weeks) writing a DSO tool which
does this task of parallel processing. Your price can vary
on where you buy it.

Hope this helps. Good luck with this implementation.

Sunil.