THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

A new MemoryHeapType default in #ssas #tabular 2016 (please, fix your setting in production!)

If you already installed Analysis Services 2016, you should change the MemoryHeapType setting. There is a new default value (-1), which is an automatic choice that currently applies a new hybrid allocator (which has the number 5 as a value). It should resolve the memory fragmentation problem causing performance issue as described in an article I wrote a few years ago. However, the setup does not write the new default value as a current value and it still write the old default “2”, which is not good for Tabular. Thus, if you installed SSAS Tabular 2016, you probably have this setting (look at the Default Value different than the Value!). The new setting is also the suggested one for Multidimensional.

image

You should change the value to -1 and then restart the service. After that, reconnect to SSAS Properties and check that you have the following configuration:

image

Of course, we hope future updates of SQL 2016 will fix this setup issue. In the meantime, fix the setting to avoid performance issues on a production server!

Published Monday, July 11, 2016 8:52 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jorge Esteves said:

Hi Marco,

Your reccomendation it's only for 2016 or it applies also on sql 2014 and 2012?

Or for 2012 and 2014 we must adopte your reccomendations on your article "Heap Memory Settings for Analysis Services Tabular 2012 / 2014"?

Thank you!

July 14, 2016 6:54 AM
 

Marco Russo (SQLBI) said:

The setting -1 is valid only for 2016. For 2012/2014 you should still adopt settings described in the article you cited.

July 14, 2016 7:53 AM
 

Manideep said:

Hi Marco

We have a SQL 2016 deployed on 4 of Azure VM D14 series(16p, 112 GB RAM),where we hosted 6 Tabular cubes (sizes varies from 8 to 12 Gb). These 4 servers are configured with round robin based Load balancer, which is used for excel reporting. But sometimes suddenly there was a slows down many queries of excel pivot reports, and then immediately restore expected response times as soon as you restart the service (or the entire servers in VIP).It is similarly like a symptom of problems with heap implementation which you explained earlier. As you suggested we changed the MemoryHeapType setting to (-1), but still we are facing this issue. can you please suggest how we can mitigate this.

And also all 4 VM's are consuming 90 % of memory for Tabular analysis services.

Appreciated your help. Thanks

July 21, 2016 4:19 PM
 

Marco Russo (SQLBI) said:

I would control performance counters in the VM and also counters from the host server. Using VM, if they are not committed to physical RAM, you might have that problem. I would also suggest you to try the (1) setting, too (just in case -1 has some regression). In general, you should monitor your servers in order to collect data to analyze what caused the slowdown. If you need direct assistance, please consider our consulting services (http://www.sqlbi.com/consulting)

July 22, 2016 2:33 AM
 

Daniel said:

Hi Marco

I just installed a standalone instance of SSAS 2016 EE RTM. It has already the values -1 set. Seems that this is fixed?

Cheers,

Daniel

July 22, 2016 8:44 AM
 

Marco Russo (SQLBI) said:

It should.

July 22, 2016 8:52 AM
 

Ron said:

Microsoft support is getting worse by the day.

We are using SSAS 2016 (tabular) mode on a brand new setup using the latest CU.

Good old msmdsrv.exe  is now randomly crashing with "SQLException64 in msmdsrv.exe tabular mode crash".

Tried raising a support ticket with out partner incidents and was told, sorry but mini dump analysis is only supported for premier customers. What a joke  !!!

Leaves me up the stream with no paddle...

September 28, 2016 2:29 AM
 

Marco Russo (SQLBI) said:

Ron,

did it improve with the latest CU/SP?

January 19, 2017 2:09 PM
 

Perfo said:

Hello Marco,

Will these settings better fit while using the processed cubes by micro stratergy.

We have a setup where the cube processing is done in different server(A) and later cubes are synced to server (B). And the issue we are facing is with memory consumption ( RAM), we use Microstrategy to run the reports using the cubes on server (B). when we execute these reports servers runs fast initially all of the sudden it wont let cursor move cause all the memory been utilized by ssas service.

Please advise what can we do .. we are using SSAS 2014 SP1 CU9

March 16, 2017 9:03 AM
 

Marco Russo (SQLBI) said:

You should investigating on what are the measures that materialize too much memory in SSAS. Usually this is an issue that requires optimizing DAX measures.

March 17, 2017 7:09 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Privacy Statement