Hi,

We were doing a POC with 64bit - SQL Server Enterprise edition with Analysis
Services on it. We are trying to build fairly large cubes with 30 dimensions.
At least 10 of them are very small with 4 to 5 members. 15 of them are
moderate with 300 to 400 members and 5 of them are larger with around 2
million each.

As a first test, I processed a cube partition with the following server
settings and it took 31 minutes to processes.

Min Allocated memory - 15 GB, Read - a head buffer - 1 GB, Process Buffer -
3 GB.

Then I bumped all three as follows:

Min Allocated memory - 25 GB, Read - a head buffer - 3 GB, Process Buffer -
6 GB.

It still took exactly 31 minutes to process the partion and wrote exactly
155 segments like before while writing & aggregating data.

Question 1:

I expected the consolidation time to be much lesser as the DB2 time will be
unchanged. Can you please explain this and how to improve the aggregation
times with regards to tuning the server settings? I made most of the
dimension’s aggregation usage to ‘Top Level Only’.

Question 2:

One thing I noticed is the CPU time is always at 100% while it is performing
aggregations and writing data.

The server has 8 processors. I am just wondering if it is using 1 processor
100 % or all the 8 processors at 100%.

Question 3:

I actually read some of the white papers on performance & tuning. I learnt
that creating aggregations is a single-threaded process. What does this mean
how many threads / processors it will use while creating aggregations. I am
not sure how to find out the available # of threads on the machine. Also does
it use a separate thread for writing base cells to the MOLAP file?


Thanks in advance,

The "Analysis Services Performance Guide" MSDN paper discusses the
tuning of memory settings. Once the read-ahead buffer is adequate and
the process buffer can accommodate aggregations for a partition, there
may well be no performance gain merely by allocating more memory.

Based on the recommendations of this paper, you could try processing
partitions in parallel to cut time - of course, this assumes that you
appropriately partition the cube:

http://www.microsoft.com/technet/pro...luate/64btdwc4.
mspx
The 64-bit edition of Analysis Services may be beneficial for scenarios
in which parallel processing of partitions is needed to complete
processing within a short window. Recall that Analysis Services
allocates memory for process buffers during the aggregation phase, and
all of a partitions aggregates must fit in the process buffer while the
partition is processing before they are finally written to disk. If the
server needs more memory to store these aggregates, the server will use
temporary disk files. The use of temporary files is much slower than the
use of memory. The 64-bit edition of Analysis Services may allow your
application to complete aggregate processing in memory, which should
decrease the overall processing time for the partitions. You can monitor
temporary file usage by Analysis Services by looking at the performance
monitor counters Temp File bytes written/sec and Temp File rows
written/sec under the Analysis Services:Proc Aggs object.
...
Here's a link to the parallel processing utility:

http://www.microsoft.com/downloads/d...2eef773-6df7-4
688-8211-e02cf13cbdb4&DisplayLang=en


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Another resource which provides some technical information is at:
http://www.microsoft.com/technet/pro.../ansvcs64.mspx

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


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote