Analysis Services uses the Very Large Dimension Manager (VLDM) to cache very
large dimensions. When a dimension's size is over VLDMThreshold (default
60mb), a VLDM process is launched to take care of this dimension. As a
result, each very large dimension will have its own process. The goal is to
distribute memory allocation among multiple processes so that the main MSAS
process does not exceed the 2G/3G limit too easiliy.

It seems to be a good design to start with, considering the memory limit we
have for 32-bit systems. However, my observation doesn't show intented
behavior. Here's what I have tried:

Physical memory: 2 GB. Page file size: 4GB
Three dimensions, each based on the same source table (900,000 members),
parent-child dimension with no extra member property, id-based keys, average
length of the name string is 20, the dimension files is about 320 MB for
each dimension.

1. Starting MSAS with no cube/dimension processed: MSAS uses 160 MB, no VLDM
processes
2. Process the first dimension: MSAS uses 620 MB, 1 VLDM process, each uses
380 MB
3. Restart MSAS services: MSAS uses 435 MB, 1 VLDM processes, each uses 380
MB
4. Process the second dimension: MSAS uses 900 MB, 2 VLDM processes, each
uses 380 MB
5. Restart MSAS services: MSAS uses 690 MB, 2 VLDM processes, each uses 380
MB
6. Process the third dimension: MSAS uses 1170 MB, 3 VLDM processes, each
uses 380 MB
7. Restart MSAS services: MSAS uses 960 MB, 3 VLDM processes, each uses 380
MB

The main problem is that the main process doesn't seem to benefit very much
from the VLDM concept. Each dimension seems to take 270 MB in MSAS process
and 380 MB in VLDM process. Can somebody describe what's stored in the MSAS
process and what in the VLDM process for very large dimensions? Is the above
behavior by design? If so, 2G/3G could easily be exceeded by any cube that
tries to track millions of customers. For that kind of cubes, is the 64-bit
solution the only recommendation?

Hi Yubo,

I found the following information in SQL Server 2000 Resource Kit on
Analysis Services memory management.
Analysis Services uses a sophisticated form of memory management on the
server, effectively balancing memory conservation against the processing
and querying load. Several memory cache areas are allocated for specific
purposes on the Analysis server, with an emphasis on separating processing
and querying operations, in terms of memory.

Memory usage for an Analysis server is broken out into the following areas:

Read-ahead buffer
The read-ahead buffer is used by Analysis Services when retrieving data.

Dimension memory
Used to cache dimension members and member properties, dimension memory is
allocated at startup and retained at all times, subject to dynamic size
changes when processing dimensions. With the exception of very large or
huge dimensions, all dimension members are loaded into memory when the
MSSQLServerOLAPService service is started or a new dimension is added, and
occupy part of the virtual address space of the process.

The total size of data for all members and member properties in all
dimensions is used to calculate the space required to support dimensions in
memory. A guideline for estimating this value is approximately 125 bytes
per member, plus the size of data for member properties.

A special tool, the Very Large Dimension Manager (VLDM), is used to support
very large and huge dimensions. A dimension whose size exceeds the VLDM
Threshold will be stored in a separate process address space. For large
dimensions this frees virtual address space in the main Analysis Services
process for other uses, at a small cost in the speed of accessing dimension
members. This is handled transparently; no administrative action is
necessary to use the VLDM.

ROLAP dimensions can be used if a dimension is so large (approximately 20
million members or more) that the members cannot be stored in the address
space of a process. In this case, the members are not read into memory;
they are retrieved as needed from the relational data source. However, a
ROLAP dimension may be used only in a ROLAP cube, and this type of storage
has a substantial performance impact. ROLAP dimensions are recommended only
when dimensions are so large that they cannot be handled in any other way.

Shadow dimension memory
Before processing a dimension, a copy (shadow dimension) is made of the
original dimension contents to allow users to continue to access cubes that
contain the dimension while the dimension is being updated. Queries are
directed to the shadow dimension during processing. When processing is
completed, queries are redirected to the updated dimension and the shadow
dimension memory is deallocated.

Process buffer
The process buffer is used to store temporary indexing and aggregation data
while processing cubes and dimensions on the Analysis server. Process
buffers are used during explicitly initiated cube processing and also
during lazy processing as a result of alterations to a changing dimension.
One process buffer is allocated for each partition being processed at any
given time. Because of the potentially high usage of process buffers, an
economic model strategy, discussed later in this chapter, is used to handle
allocation of process buffer memory. The process buffer is not used during
querying operations.

If the set of aggregations computed for a partition can be fully contained
in the process buffer, aggregations can be computed in memory without
access to disk. If there is insufficient memory to contain the full set of
aggregations, aggregations must be partially computed, stored to disk, and
re-read to merge with new partial aggregations. Because of the disk I/O and
merging operations, this repeated read-merge-write cycle slows processing
considerably.

Query results cache
The query results cache is used to cache cell data for cubes and partitions
queried by client applications, and is not used during processing
operations. Cube data cached within the query results cache is versioned to
maintain synchronization with client applications. An expiration scheme is
used to dispose of stale data.

The query results cache is allocated dynamically, and is allowed to fill
the available space remaining after subtracting the size of all other
memory uses from the value of the HighMemoryLimit registry setting.

Server memory is allocated in the following order:

Read-ahead buffer

Dimension memory

Shadow dimension memory (when processing dimensions)

Process buffer (when processing)

Query results cache

295443 INF: How To Enable Analysis Server to Use 3 GB of RAM
http://support.microsoft.com/?id=295443

http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sql2k_anservregsettings.asp

* Set the Minimum Allocated Memory property setting in Analysis Manager to
a value of 2047. Note, that if you plan to edit the Minimum Allocated
Memory property, you must change it before you edit the HighMemoryLimit
registry value.

* Increase the Memory conservation threshold setting to 2047 MB. This will
allow Analysis Services to take as much memory as it can.

* Read Ahead Buffer Size shouldn't be more than 100MB.

* Process Buffer Size 80% of the 3GB - approx 2.4 Gb

327396 Support WebCast: Performance Tuning Analysis Services
http://support.microsoft.com/?id=327396


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
e.de!news-lei1.dfn.de!news-fra1.dfn.de!npeer.de.kpn-eurorings.net!news-out.n
uthinbutnews.com!propagator2-sterling!In.nntp.be!vienna7.his.com!attws1!ip.a
tt.net!lamb.sas.com!newshost!not-for-mail

Also keep in mind that VLDM is out of process, which means that in order to
resolve queries there is going to be a lot of out of process communication
between the VLDM and the server. If you have millions of members in your
dimensions, you should strongly consider the 64-bit version of Analysis
Services. There is no VLDM in that release.

Sean


--
Sean Boon
SQL Server BI Product Unit

Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.

"Bill Cheng [MSFT]" <billchng (AT) online (DOT) microsoft.com> wrote

All these documentation still doesn't explain why the main process of MSAS
still takes so much memory just because those three large dimensions have
been processed. It only makes it clear that there's some major discrepancy.
Note that I have restarted MSAS to make sure processing memory and shadow
memory are freed. I'm sorry, Bill, but do you really think the documentation
has answered my questions?

I hope somebody with internal design/implementation knowledge can answer my
questions. Even the questions may not be answered completely, I still hope
this message gets forwarded to the right people. I'm sure it's quite an
investment for this VLDM approach, but I just don't see it's doing a very
good job. The documentation can say a lot about the ideal behavior, but only
the real tests can speak the truth.

- Yubo Fan

"Bill Cheng [MSFT]" <billchng (AT) online (DOT) microsoft.com> wrote

Hi Yubo,

I have consulted the developers. It seems that the memory for dimension in
main process is very big. However, they recommend using 64 bit in place of
playing with VLDM. VLDM was design as temporary feature to wait for 64 bit
platform. Now, when 64 bit platform available they strongly recommend to
use it and not bet on VLDM. VLDM is very complex feature and it has
performance impact.


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
d01.sul.t-online.de!t-online.de!newsfeed.online.be!216.170.153.135.MISMA TCH!
tdsnet-transit!newspeer.tds.net!nntp1.roc.gblx.net!nntp.g blx.net!nntp.gblx.n
et!vienna7.his.com!attws1!ip.att.net!lamb.sas.com! newshost!not-for-mail