How Much Memory Required for Your SSAS Tabular Implementation?

If you are a Data Warehousing and  BI professional who is working with SQL Server Analysis Services Tabular, one of the quintessential questions you would have been asked by your clients is How much memory will be required for the production server? In this post, I’ll explain how to estimate the required memory for your requirement.

Image Reference: www.intelligenthq.com

The truth is it always depends on how much data volume is there in the source (relational data warehouse) and the growth ratio. In this case, you have to be smart with calculating the storage growth of your relational Data Warehouse and how much volume growth you would expect for next couple of years.

You are supposed to talking about Memory.. So why worrying about storage? 

Here is the catch. SSAS Tabular semantic database is an in-memory database. So all the data resides in-memory unless you chose the direct-query mode. (this post is focused on in-memory mode). So, that disk I/O is not a main factor to consider when you give hardware requirements for clients.

Follow are predominant key factors for better performing your Tabular Implementation

• Size of Memory
• Memory Bandwidth
• Speed of Memory
• Speed of CPUs
• Onboard cache size

Let’s move back to the topic. How much the memory required for the Tabular implementation. It is a good chance you get into the development server and see how it goes before diving into estimate the Production workloads.

It's well known and documented you can experience 3x compression of data when you processed and completely loaded into the Tabular database.

Just assume you are building your Tabular model on top of a well modeled relational data warehouse. Your data warehouse is 3GB in size. Once it loaded into the Tabular model it will become literary around 1GB. This 1GB data holds in memory. Typically it is recommended to estimate 3x size of memory for the full process the tabular model. Addition memory used for hold a copy of your Tabular model. When you make process full for the second or next time It keeps a second copy of your data model for addressing incoming queries. The copies will be wipe as soon as completed the process fully.

How to Check the Required Memory Size?

Best way to get a rough estimate of the memory requirement is,

1. Go to Management Studio 
2. Right-click the Tabular Database and select Properties. In there you can get the Estimated size. 

What you have to go multiple the estimated size into 3. Then you will get the rough estimate for your model processing and query workloads. In this case,

121.99MB x 3 = 365.97MB 

Would you excuse me, this is not a good example for when we talk about hardware sizing 😊

By default, the Vertipaq memory limit is 60% of RAM. And the Total memory for the Tabular server is 80% of RAM.

According to this above diagram, you can realize, even though you have not loaded any single database into the Tabular server, you will still need some amount of memory for Formula Engine and Msmdsvr process to execute. You can check it by Right-click the Tabular server instance and go to Properties,

In my case, its 19.2GB allocated for Vertipaq engine by default as my development computer has 32GB of RAM. (These values are configurable)

A formula to Get Initial Estimate

The formula below gives an initial estimate for your requirement even before implementing a tabular model. 

Estimated Memory = (Size of uncompressed data/10) x 2

In here uncompressed data means the size of your relational source database. Ex: Contoso DW relational data warehouse. This denominator 10 is the average level of compression. In my post earlier mentioned it is like 3x compression. But in your solution may be lower compression like 3 times or much higher compression like 10. It is all vary based on the data types, length and the cardinality of your data set. Multiply by 2 for the keep secondary copy of your compressed DB and for other operations. 

Once you have done the initial estimate, you can refine this figures by doing a prototype and adjust based on the incremental data growth. 

In this post, I tried to deliver some high-level understanding of Memory requirements and how to do an initial estimation for your SSAS Tabular implementation. I hope now you can answer your client's queries about the Memory sizing 😊

Reference: MSDN