cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rkapukaya
Helper I
Helper I

SSAS Tabular Model Process - Low Memory Error (Standard Edition)

This is the environment I work with:

  1. Microsoft SQL Server 2017 Standard Edition (64-bit)
  2. One database engine instance with 3 databases
  3. One Analysis Services instance with 4 databases (Tabular model, average size of each database 3.9 gb)
  4. Database engine and ssas are on same server.
  5. Server has 256gb ram; 80gb allocated to database engine, 60 gb allocated to etl tool.

I read, if SQL Server is Standard Edition, it will assign a limited memory (15gb) to analysis service database. Also I can not create a new partition for one table. I wanted to create multiple partitions for one table and refresh/process only partition which has hot data. But I couldn't do that.

So unfortunately, I have to full process for each tables. If there is an option to achieve this?

Here are my steps to process:

  1. When I try to deploy ssas tabular model via Visual Studio, it throws "low memory" error. I have 10 tables in my tabular model, so I filtered table queries to "select top 10". After that I deployed the model successfully.

  2. After first step, I edited and removed "top 10" part from each tables partition in SQL Server Management Studio.

  3. I extracted full process script for each partitions, created a linked server between database and ssas instance.

  4. I created cursor to process partition of 10 tables. But I got an error "low memory".

Here is an example of process a partition.

   DECLARE @MyScript NVARCHAR(MAX) = '{"refresh":{"type":"full","objects":[{"database":"Dwh_Reports","table":"Fact Sales","partition":"Par_Fact_Sales"}]}}'

   EXEC (@MyScript) AT SSAS

To sum up, my questions;

  • I have 4 databases on my ssas instance. That means, per instance uses 15gb memory and i have to allocate 60gb memory to ssas?

  • My ssas instance configuration is default, I didn't change anything;

     Memory \ LowMemoryLimit       -> Value : 60
     Memory \ TotalMemoryLimit     -> Value : 80
     Memory \ VertiPaqMemoryLimit  -> Value : 60
     Memory \ VertiPaqPagingPolicy -> Value : 1
    

Do I have to change this configurations?

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @rkapukaya ,

 

You'd better to divide your  engine instances and ssas instances to different servers. And please refer to https://www.sqlbi.com/articles/optimizing-memory-settings-in-analysis-services/  to optimize the ssas.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

FarhanAhmed
Super User II
Super User II

What is the data source of your ssas model?

What are the size of your tables that you are processing?

Make sure that you are not pulling unnecessary columns in your data model to avoid extra space.

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps