Marco RussoA Tabular model in Microsoft SQL Server Analysis Services 2012 and 2014 uses the same memory allocation infrastructures used by Multidimensional models. The two type of models do not have the same behavior and the default memory heap settings that works usually well for Multidimensional can produce undesired effects in Tabular, resulting in lower performance.
Analysis Services requests memory to the Windows operating system in two ways: using its own heap implementation, or using the one provided by Windows. This behavior is controlled by the HeapTypeForObjects and MemoryHeapType parameters of the Memory group in the Analysis Server Properties window, as shown in the following picture.
The white paper Analysis Services Operations Guide includes an historical description of the reasons why these two heap implementations exists (see section 2.3.2.4). The default settings in Analysis Services 2012 and 2014 use the Windows implementation of the heap instead of the proprietary one available in Analysis Services.
The following is the meaning of the two settings we are considering:
- HeapTypeForObjects: choose the heap system to allocate objects of a fixed size, such as instances of classes in C++ (which is the language used by Microsoft to write Analysis Services). Possible values:
- 0 – Use Windows Low-Fragmentation Heap (LFH)
- 1 – Use custom heap implementation of Analysis Services
- MemoryHeapType: choose the heap system to allocate objects of a dynamic size, such as strings, vectors, bytes, and so on). Possible values:
- 1 – Use custom heap implementation of Analysis Services
- 2 – Use Windows Low-Fragmentation Heap (LFH)
Early versions of Analysis Services used the custom heap implementation. The Windows LFH was adopted to get better performance in a concurrent environment. In theory, a workload of many users running queries at the same time performs better by using the Windows LFH. However, this is true only for Analysis Services Multidimensional. During a query, the VertiPaq engine used by Analysis Services Tabular allocates much more memory than the OLAP engine used by Multidimensional does. These allocations are often larger than 16KB and the Windows LFH does not perform very well with these large memory blocks; it is designed for smaller ones. A possible consequence is memory fragmentation in the heap (despite its name “low-fragmentation” would suggest a different behavior). Memory fragmentation produces two consequences: a growing size of the overall memory allocated by Analysis Services and slower performance during query execution.
For this reason, the dynamic allocation of objects in Tabular might perform better with the custom heap implementation in Analysis Services. It is suggested you monitor the query execution time with cold cache for the same queries over a number of days, and the memory committed by the Analysis Services Process after a number of cycles (process/queries). You have a symptom of problems with heap implementation when you see a server that slows down many queries, and then immediately restore expected response times as soon as you restart the service (or the entire server).
In presence of these symptoms, you can consider to switch the MemoryHeapType setting to the custom heap implementation of Analysis Services. Usually there is no need to change HeapTypeForObjects. Usually, the following configuration produces better results for Analysis Services Tabular:
- HeapTypeForObjects = 0
- MemoryHeapType = 1
We have observed improvements in many Analysis Services Tabular configurations by using these settings. However, you should verify the real benefit in your specific scenario and query workload, using settings other than the default one only whether you can measure a real performance benefit for that.
UPDATE 2016-07-03 The memory fragmentation problem raised by default settings described in this article could affect also Multidimensional instances of Analysis Services, even if it tends to be more common on Tabular.
UPDATE 2016-07-11 The setup of SQL Server 2016 does not apply the new default setting of -1 for MemoryHeapType, which implements an automatic choice currently corresponding to a new hybrid allocator (value 5) that should solve the problem described in this article. If you are running Analysis Services 2016, check your memory configuration as described in this blog post. The recommendation is good for both Tabular and Multidimensional.