Optimize Heap Memory Settings for Analysis Services Tabular 2012/2014 #ssas #tabular

In the last months I assisted many companies implementing solutions based on Analysis Services Tabular. There is not so much difference between the versions 2012 and 2014, because SQL Server 2014 didn’t introduce new features to the BI services. Thus, my considerations are valid for both.

One issue observed in different cases was a general performance degradation after a few days of work. Restarting the msmdsrv.exe service was enough to restore normal performance. The problem might affect both query and process operations. Microsoft released a hotfix (KB2976861) that mitigates the problem for slowness of full process, but it is not something that completely solve the problem.

The real reason of the issue is the fragmentation of the memory heap. Analysis Services can use its own heap algorithm, or the standard Windows one. It seems that the workload generated by Tabular creating objects of a dynamic size is an issue for the Windows Low-Fragmentation Heap, which is the default setting in Analysis Services (because of a better scalability).

In the Heap Memory Settings for Analysis Services Tabular 2012 / 2014 article on SQLBI you can find a complete description of the settings to control heap memory used by Analysis Services. If default values produces the symptoms described above, then consider changing them with the suggestions included in the article.