So we have some very busy olap servers.
Maximum # of threads: 4
Minimum allocated memory: 2047
Memory Conservation Threshold 2699
read-ahead buffer size: 100
Process Buffer Size: 1500
Log: every 10 queries

These machines have 4gb of physical memory.

the Boot.ini contains the 3gb flag-- what else do we need to configure to
allow OLAP to use > 2gb? Isn't AWE an option for Analysis Services??

the page file is only 2gb.. Shouldn't it be bigger?

The processlog is set to 1.5gb-- so out of the 4 physical gigs of memory,
OLAP gets to use 1.5; and the process buffer size uses the other 1.5 right?

I'm just concerned because someone is processing a cube that they say takes
3 days-- it just kindof boggles my mind..

-Aaron

Analysis Services is not AWE aware. I'll refer you to the following white
papers for additional information:

SSAS Operations Guide:
http://www.microsoft.com/technet/pro.../anservog.mspx
SSAS Performance Guide: (particularly the section on memory)
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Getting started with 64-bit:
http://www.microsoft.com/technet/pro.../ansvcs64.mspx
"INF: How to Enable Analysis Services to Use 3 GB of RAM in the Microsoft
Knowledge Base" at
http://support.microsoft.com/default...b;en-us;295443

Hope that helps.
--
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.

<aaron_kempf (AT) hotmail (DOT) com> wrote

As stated earlier performance is a large issue - as well performance when
processing as when querying.

When determining trouble you need to look at lots of things which are
descibed in the papers recommended by Dave.

But 3 days for processing sounds like way to much - sounds like it could be
an architechtural problem - they are not
computing 100% aggregatons in storage design - that would definetly be the
wrong way to go....


<aaron_kempf (AT) hotmail (DOT) com> wrote

So we have 4gb of physical memory, 1gb for the OS. Our pagefile is only
configured to be 2048 mb or something along those lines. Shouldn't we have
a total of 10gb of page file space, per this from Microsoft?
----------------------------------
Paging Files
By default, Windows uses a single paging file equal to approximately 1.5
times the amount of physical memory in your computer. However, because
Analysis Services makes extensive use of Windows paging files, you should
always add a second paging file equal to the amount of physical memory on
your computer. The SQL Server relational and multidimensional runtime
engines work with memory very differently. The SQL Server relational engine
directly maps and controls physical memory usage, while the Analysis
Services multidimensional engine relies on the Windows operating system to
allocate additional memory (physical or virtual) to the Analysis Services
address space as needed. As a result, when the Windows operating system
reduces the Analysis Services working set because other applications require
allocations of physical memory, Analysis Services may need to use the paging
file for its memory needs. You must ensure that the total paging file space
is more than that configured by default, so that Analysis Services has
sufficient virtual memory if the Windows operating system has insufficient
physical memory.
----------------------------
Also, i found this about the maximum page file size- -i dont know how big it
is in Win2k AS, but
http://www.jsiinc.com/SUBB/tip0900/rh0955.htm

Windows NT 4.0 limits the pagefile size to 4,095MB.
If you require more Pagefile space, create multiple logical drives,
preferably on different disks, and use the entire logical drive as a
pagefile.

So I assume that we should at least have a 4gb page file.. hopefully on a
RAID 0 disk.

Will this actually help in a real-world situation where OLAP takes 3 days to
process?





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

That quote is from the Analysis Services Operations Guide.
http://www.microsoft.com/technet/pro.../anservog.mspx
in the section which talks about OS optimizations. The section is mostly
concerned with the way that memory is being used between the msmdsrv process
and the OS. Unlike SQL Server RDBMS which controls its own physical memory
allocation, msmdsrv simply allocates virtual address space within the
process and relies on the OS to use the page file as a backing store when
pages are paged out of the working set. This means that it can be an
abnormally heavy user of pagefile, something which Windows doesn't always
account for. Thus we recommend increasing the page file to accomodate for
that. We wanted a simple formula which could be proactively used and setted
on that general advise. As you point out, it obviously isn't the right
number for all systems depending on the other applications running on the
same server.

However, I doubt if that will improve your 3 day processing situation.

If you read further on in the Operations Guide, you will see the section
titled "Schema Optimization". My guess is that, if you are taking days and
days to process a cube/partition, that the real problem is either 1)
designing excessive aggregations for the cube (see the section titled
"Aggregation Design Considerations and Guidelines" in the SSAS Performance
Guide at
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
), or more probably, 2) the large complexity of the SQL statement which is
being issued (if you have a large number of dimensions/levels) as outlined
in the "Schema Optimization" section.

Not to be too arrogant, I would suggest:
1) you turn on the system-wide processing log file (which is a server
property on the "Logging" tab) -- this best practice is also something
talked about in the Operations Guide, see the section titled "Processing Log
File"
2) from the log you should be able to see the SQL statement issued -- the
goal is to reduce the complexity of that statement
3) run the optimize schema tool in the cube editor -- it will evaluate the
structure of the cube and remove joins if possible -- in BOL, see the
section titled "Optimizing Cube Schemas" -- you may need to restructure your
cube to meet the 4 criterion listed there for the join to be removed.
4) re-process your cube and look at the SQL statement generated in the log
file -- ultimately you are trying to get the SQL statement down to a table
scan of the fact table. If you are using partitioning, the only join you
won't be able to remove (unless you set a value in the registry) is the join
used to enforce the data slice that you set on the partition.

Hope that helps.
--
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.

<aaron_kempf (AT) hotmail (DOT) com> wrote