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

Install SQL Server 2016 SP1 for Analysis Services #ssas #tabular #sqlserver

Microsoft recently released the Service Pack 1 for SQL Server 2016. If you use Analysis Services, installing this update is a no-brainer and you should do that as soon as possible. The reason are all related to memory management. Microsoft officially described this in a blog post on MSDN. I suggest you reading it, but let me describe in a more informal way why this update should be a priority for you.

  1. SSAS Tabular had a heap fragmentation issue in SSAS 2012 and 2014, but most of the issues were still present in SSAS 2016 because of other bugs. If you run SSAS 2012/2014 (any version/SP), or SSAS 2016 RTM (any CU), then you should mitigate the problems using the workarounds described in the article Heap Memory Settings for Analysis Services Tabular 2012 / 2014. The new allocator in SSAS 2016 SP1 should get rid of the issues, so you should reset the HeapTypeForObjects and MemoryHeapType settings to their new default values (MemoryHeapType=-1 and HeapTypeForObjects=0). I still don’t have metrics from real world usage, so I invite you to test these parameters, monitor the memory in your server after a few hours/days and report (using comments) whether the new settings work better than the previous ones.
  2. Improved performance thanks to the new memory allocator. I made some stress test on machines with one socket and multiple cores, and I measured improvements between 5% and 10% on test stressing queries over large tables using the same hardware and with the same condition. Since the improvement is for memory allocation, I would not be surprised if there are improvements also in processing time. However, getting faster performance for free is always welcome.
  3. NUMA support. The Microsoft post is very honest and describe it as “NUMA awareness” and talks about “better NUMA support”. I still don’t have numbers, but based on a number of information I collected we can say that the benefits are relevant for large data models running on 4 sockets or more. I have seen statements describing as 30GB the model size that would start to get improvements, but I think that you should also consider the model type. A single model with a huge number of tables and columns might not benefit too much, whereas if a single table has billions of rows, then the benefits should be more visible. Remember: if you are able to store your model on a single socket machine, you probably have (on average) better performance. But if you want to enter the club of 10+ billion rows in a single table, then this is an option you should consider. If you have these models and you have 4 sockets or more, please share your experience!
    At the same time, if you have many small models, it’s probably better to split your workload on different SSAS Tabular instances, each one running on a single socket. But you can try, and share your results especially if my prediction is wrong and you see performance benefits scattering data of small models on multiple sockets.
    Finally, if you have a large table (>2 billion rows) and only 2 sockets, please contact me to play with some VertiPaq non-default setting.

Getting scalability over NUMA architecture is not easy, but this is a long awaited first step in the right direction. In any case, this update is a priority if you have SSAS Tabular 2016.

Published Saturday, November 19, 2016 11:22 PM by Marco Russo (SQLBI)



Joseph Heiner said:

Hi Marco,

Our team currently has around a 600 Gig Tabular Cube with multiple Dimissions and multiple fact tables.. We are looking at installing the Service Pack update.

Besides Installing this new update to help with the NUMA Problems (We have had NUMA disabled on our servers since SQL 2016 non SP1 was not NUMA Aware. Are there any other SSAS Configurations that will help query performance in the system?

Our System has 4x 400 gig NUMA Cores in the system.

We will provide some Testing results, of before and after the Update for comparing how much this service pack improves the query performance.

Thank You


November 22, 2016 6:55 PM

Marco Russo (SQLBI) said:

Hi Joseph, sorry for the delay, I didn't notice the comment before (I missed the notification mail for some reason). If you have 4 nodes, you should try to enable the NUMA nodes and make sure you reset to default values the HeapTypeForObjects and MemoryHeapType settings. After you install SP1, check what are the suggested default values, and reset the settings to them. In this way you will use the new allocator provided by SSAS 2016 SP1. The default value of new settings are already optimized for 4 NUMA nodes.

Let us know your findings!

December 4, 2016 4:33 PM

Quicksilver said:

The description for Event ID 22 from source MSOLAP$TABULAR cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

An unexpected exception occurred.

the message resource is present but the message is not found in the string/message table

Let us know your findings

December 12, 2016 8:34 AM

Marco Russo (SQLBI) said:

Interesting - did you contact Microsoft support for that?

January 19, 2017 2:23 PM
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