THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

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.

Published Tuesday, June 2, 2015 10:37 AM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement