SSAS 2008, 2012 SQL Server Analysis Services - Performance optimization msmdsrv.ini

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 )