Here are some real life values from testing in a customer Environment that helps you improving query, processing Performance of SSAS Cubes. We build 3 different classes of Servertype's each is dedicated for SSAS Service without any other app on it. There is a class for developers and Designers (design) optimized for both query and processing and one class (Build) for processing the cubes (on example a monthly Basis) another one for enduser Access and BI Frontendtools optimized on reading with multiple users. Testing was done on HP DL385 with 2x12 Core AMD CPU's and 128 GB RAM. Performance improved from the original Settings to theses with factor 2-4.

 

Design

Build

Frontend- useraccess

Msmdsrv.ini config directory of SSAS (don’t miss to make a copy before editing)

Query: MaxThreads

48 (2 pro Core)

48 (2 pro Core)

48 (2 pro Core)

Process: MaxThreads

120 (5 pro Core)

288 (12 pro Core)

120 (5 pro Core)

Memory: MemoryHeapType

2

2

2

Memory: HeapTypeForObjects

0

0

0

Memory: LowMemoryLimit

35

65 %

35

Memory: PreAllocate

25

30

25

Memory: TotalMemoryLimit

80 % (in Byte )

Ca 80 % (in Byte e.g 100 GB = 107374182400 byte)

80

CoordinatorExecutionMode

-8

-8

-8

Process:BufferMemoryLimit

30 % (in Byte angeben)

Ca.30 % (in 32212254720 byte)

60 (default)

Query: DataStorePageSize

65536

65536

65536

Query: DataStoreHashPageSize

65536

65536

65536

CoordinatorQueryMaxThreads

-6

-6

-8

CoordinatorBuildMaxThreads

6

6

6

LimitSystemFileCache

16384 (MB)

16384 (MB)

16384 (MB)

CoordinatorQueryBalancingFactor

-1 (default)

-1 (default)

1

CoordinatorQueryBoostPriorityLevel

3 (default)

3 (default)

0

OLAP Process: AggregationMemoryLimitMin

4 GB (in Byte )

4 GB (in Byte 4294967296 byte)

4 GB (in Byte )

OLAP Process: AggregationMemoryLimitMax

20 GB (in Byte )

20 GB (in Byte 21474836480 byte)

20 GB (in Byte )