3

We have started encountering an issue regarding the refresh of our tabular SSAS model.

The tabular SSAS model has 38 tables within it.

This process has been running without issue for over a year, however for around a month now, we have not been able to sucessfully process the tables within the model.

If i access the SSAS database > Right Click > Process Database > Select the mode to Process Default followed by OK, this is when the problem occurs.

It will sit there for around 5 minutes before failing with the error messsage:

Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available.

enter image description here

enter image description here

If i try and 'process' the tables individually, i recieve the same error message too.

I have looked into the memory settings for SSAS, within the advanced window and have reset the values to their defaults. So the key values (as im aware of) are currently:

enter image description here

The server has been rebooted several times, we still have the same problem.

Environment Details:

Windows Server 2016 Datacenter

SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64)

SSAS Version: 14.0.223.1

Server Mode: Tabular

Server Memory: 64Gb

SQL Server Assigned Memory: 28Gb

I have ready multiple articles online regarding these sorts of problems, however nothing seems relevant / useful so far.

Any guidance / assistance would be greatly appreciated.

Disclaimer: I am not a BI / SSAS guy. Im just a DBA who has been given this problem to look at, so forgive me if i dont quite explain this correctly.

3

TL/DR: Add more Memory, reduce the size of your Model(s), and/or move either the SQL Server Services or SQL Server Analysis Services to a different server (e.g. scale out)

Longer Explanation: We went through this exercise a few months back with our Tabular SSAS production server, and actually reached out to Microsoft for "formal" recommendations as our Infrastructure team was being stingy with RAM (which I can understand as it's not exactly cheap). Just for clarity's sake, the error we ran into was as follows:

The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

Our server was originally setup with 64GB of memory and was hosting 2 SSAS models totaling 40GB in size. No other SQL Server services were hosted on this machine. Some days our models would process without issue, but most days they would fail. We would reboot the server and then maybe they would succeed... if the wind was just right and the stars and planets all aligned.

Unlike Multidimensional (MOLAP/ROLAP/HOLAP) models, the default Tabular Models are loaded entirely into memory as they leverage In-Memory technology. If the model(s) are unable to be loaded entirely into memory, you run into problems.

Sadly, Microsoft's documentation breaks down on what the "memory recommendations" are as I cannot find any formal document providing anything other than "minimum" levels that are needed to just run the service. From the support ticket we filed, Microsoft's recommendations were as follows:

For a model of size X, provision between 2X - 10X RAM on the SSAS server to be used by the SSAS service, which is further influenced by the following factors:

  • Cube Processing requires 2X - 3X RAM for full processing which includes a shadow copy of the model in builtin memory.
  • The number of Users/Reports connected to the cube also increase RAM requirements, at times up to 10X depending upon number of reports, volume, etc. as users/reports can generate DAX queries which perform calculation or memory materialization (which cause the engine to build an intermediate non-compressed result and can cause the memory consumption to go higher than expected).
  • The number of Models being processed can also increase the memory footprint required.
  • Enable the VertiPaqPagingPolicy if the setting is disabled, so SSAS can utilize the OS paging file for additional memory at the cost of processing and query performance.

What we ended up doing was increase the amount of RAM on our server, which ultimately solved our issues for the time being. The only other real alternative "solutions" is to limit the amount of data you need in your model(s) or scale out (e.g. move services to another server) your deployment to other servers.

What I suspect is happening in your case is that your SSAS service is running out of memory because your SQL Server Service is also hosted on the same server. Basically you need to either segregate these services from one another or have enough RAM on the server to let them run in parallel. I would highly suggest segregating your SSAS services to a different server if possible, but licensing challenges may impact this so be sure to have enough RAM.

Other things you can fiddle with are the config settings located in the msmdsrv.ini file, but for our scenario, we didn't have much success with these making any significant differences in the eventual outcome of running out of memory.

  • Thanks so much for the comprehensive answer. We are going to look into the two options of 1) Adding additional memory or 2) Separating out the SQL instance from the SSAS instance. The strange thing is, I cannot see evidence of this memory pressure anywhere. I have looked through all the perf mon counters for SSAS when the error occurs and nothing looks like the culprit. Thanks again anyway, have took your scenario on board. – grouchball Mar 22 at 18:48
  • 2
    We have now separated out the functions. SQL on one server and SSAS on another. The model is (so far) refreshing without any issues. Thanks again for your help. – grouchball Mar 28 at 14:40
2

I had similar problem and until we start using Azure Analysis Services, I had to find a work around with SSAS standard edition which can only allocate 16GB of memory regardless whether you're on-prem server has 64GB of memory.

If you are using Enterprise Edition I suggest looking into creating partitions in the tables in your data model and only refresh records have been updated recently. Otherwise, if you are running standard edition, then refresh your model in 2 parts or more. For example put 19 tables in one job, depending on the size of your tables. Try to balance it.

{  
  "refresh": {  
    "type": "full",  
    "objects": [  
      {  
                 "database":"AdventureWorks",
                "table":"A"
      }  
      , 
      {  
                 "database":"AdventureWorks",
                "table":"B"
      }  
      , 
      {  
                 "database":"AdventureWorks",
                "table":"C"
      }  
      ,     
      {  
                 "database":"AdventureWorks",
                "table":"D"
      }  
      ,
      {  
                 "database":"AdventureWorks",
                "table":"E"
      }  

      ,
      ....


    ]  
  }  

}

I think a better solution will be to use either one of those S' Plans on Azure depending on the size of your data model and then use table partitions.

  • My workmate found another solution which is doing the data load only refresh and then do the calculate refresh after. – Jerry Chuen Jul 24 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.