Re: Quick question - dimensions large relative to fact table size
- From: Deepak Puri <deepak_puri@xxxxxxxxxxxxxxx>
- Date: Tue, 05 Jul 2005 18:45:34 -0700
Hi Richard,
Haven't personally experienced VLDM performance, but some posts advise
avoiding it when possible. According to the Analysis Operations Guide
(below), the default VLDM threshold is 64 MB per dimension. If you can
also increase memory on the server to 4 GB, and use the / 3GB switch for
AS, that will improve memory headroom. Ultimately, you may wish to
upgrade to 64-bit, to Yukon, or to both:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.
mspx
>>
...
Memory
Processes (such as Analysis Services) running in Windows 2000 Server or
Windows Server 2003 Standard Edition can address a maximum of 2
gigabytes (GB) of RAM in the main process space. If you are working with
large or complex cubes, Analysis Services may require more than 2 GB to
load dimensions into memory, process dimensions, load replica
dimensions, and still have sufficient memory for an effective query
results cache. To allow Analysis Services to address more than 2 GB of
RAM in a single process, you must install Windows 2000 Advanced Server;
Windows 2000 Datacenter Server; Windows Server 2003 Enterprise Edition;
or Windows Server 2003 Datacenter Edition.
Windows Server 2003 Enterprise Edition and Windows Server 2003
Datacenter Edition, are available in 32-bit and 64-bit versions. The
64-bit version supports the 64-bit version of Analysis Services. Because
Windows 2000 Advanced Server and Windows 2000 Datacenter Server are
32-bit operating systems, only the 32-bit version of Analysis Services
can be installed.
? The 64-bit version of Analysis Services can address all available
memory in the main process space without any special configuration (up
to 64 GB with the Enterprise Edition and up to 512 GB with the
Datacenter Edition).
? The 32-bit version of Analysis Services can address up to 3 GB of
memory in the main process space, if you enable Application Memory
Tuning. Unless you enable Application Memory Tuning, no process can
address more than 2 GB in the main process space. To enable Application
Memory Tuning on the Analysis Services computer, set the /3 GB switch in
the boot.ini file and then use Analysis Manager to set an appropriate
Memory conservation threshold value for Analysis Services. If you set
the /3GB switch in boot.ini, the computer on which Analysis Services is
running should have at least 4 GB of memory to ensure that the Windows
operating system has sufficient memory for system services. If you are
running other applications on the same computer, you must factor in
their memory requirements as well. For example, if the SQL Server
service and Analysis Services are installed on the same computer, SQL
Server can address memory above 4 GB because SQL Server supports Address
Windowing Extensions (AWE). In this case, you could install and use 8 GB
or more on the server. However, because Analysis Services does not
support AWE, Analysis Services cannot access more the 3 GB of memory in
the main process space unless the 64-bit version is used.
For more information on setting the /3GB switch, go to Microsoft
Knowledge Base (support.microsoft.com) and see the article "INF: How to
Enable Analysis Server To Use 3 GB of RAM.". For more information on
setting the Memory conservation threshold value, see "Configuring
Analysis Services" immediately following this section.
...
Very Large Dimension Memory (VLDM) Threshold
The 32-bit version of Analysis Services (the 64-bit version does not use
VLDM) attempts to prevent large dimensions from using all of the
available virtual memory address space by loading each very large
dimension at startup into its own process space with its own virtual
memory address space. A very large dimension is one that exceeds the
value of the VLDMThreshold setting in the registry. The default VLDM
threshold is 64 MB. While using a separate address space for each
dimension that exceeds the VLDM threshold does save virtual memory
address space for other uses in the main process, overall performance
slows when one or more dimensions exceed the VLDM threshold. Loading all
dimensions into the main process space (when possible) yields better
performance, but you must ensure that there is sufficient virtual memory
address space to perform the following:
? Load all dimensions into memory at startup.
? Load all dimensions being processed in parallel or in a single
transaction into memory during processing (these are called shadow
dimensions). Analysis Services uses the existing version of each
dimension to resolve user queries until the processing transaction
commits. To minimize the amount of memory needed for shadow dimensions,
process dimensions in separate transactions. If you select Process the
Database or Process All Dimensions in Analysis Manager, the dimensions
are processed in a single transaction and will require sufficient memory
to load each dimension in memory twice (once at startup and then again
during processing).
? Store replica dimensions as required. See "Replica Dimensions" later
in this paper.
? Perform all processing without using temporary files. See "Process
Buffer" later in this paper. However do not use VLDM just to allow a
larger process buffer. Processing is a one-time or, at worst, an
occasional activity. Using VLDM is a constant overhead for performance
and complexity (more processes, more context switching, and so on).
? Create and use a sufficiently large query results cache. See "Query
Results Cache" later in this paper.
>>
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***
.
- References:
- Re: Quick question - dimensions large relative to fact table size
- From: Richard A Rose
- Re: Quick question - dimensions large relative to fact table size
- Prev by Date: Re: replace
- Next by Date: "Fact Table Size Cannot be 0" --> this happens often
- Previous by thread: Re: Quick question - dimensions large relative to fact table size
- Next by thread: Cubes
- Index(es):
Relevant Pages
|
