Tabular Models unloading from memory? RRS feed

  • Question

  • So the scenario we have is we have 4 front facing SSAS Tabular servers spread across a number of data centres to help us with worldwide geographic challenges and to provide redundancy.

    We process a pair of new cubes overnight on a processing Server (no end users on this), we then SYNC these with the 4 front facing servers and a redundant processing spare. So we do 10 cube syncs (2 cubes on 5 servers)

    This is all undertaken within a SSIS package by issuing a set of TMSL SYNC commands.

    This has been fine for months moving both models in about 15 mins (models are 12gb and 15gb compressed)

    We often see the servers taking a while to "spin up" the VMs are running in an uncontested mode so they have the requisite RAM allocated and aren't being starved in any way by the underlying tin. 

    An example of this, is if we connect in SSMS we connect immediately but if we expand the Databases folder in the Object Explorer tree it can often take several minutes to give us the expanded DB list. If we monitor the server concerned we can see the RAM used by SSAS on the server spinning up from a fairly low level (CPU is often 15% during this phase) as it loads the two models. Eventually it reaches a footprint of about 52Gb (both cubes loaded with some overhead I assume) at which point it will start responding to queries.

    This spin up delay seem to occur after a sync is undertaken for the first user that hits any given server, after that it seems to be fine. We do have some anecdotal evidence that if a server isn't active for a period we get the same "spin up" behaviour.

    Does anyone have any ideas as to what might be causing this behaviour and or how we can prevent it?



    BI Addict!

    Thursday, February 21, 2019 4:13 PM

All replies

  • Hi Steve Powell,

    For "Eventually it reaches a footprint of about 52Gb (both cubes loaded with some overhead I assume) at which point it will start responding to queries." You might could refer to this post(Darren's suggestion) for more details.

    In addition, you said when user hit the server, the sync will be slow. This might be related sync single thread? You could try to Sync SSAS at other period, avoid working peak time.

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, February 22, 2019 7:31 AM
  • Thanks Zoe

    But the slow spin up isn't really the same issue as Darrens post. The issue is in SSMS, although we also see the same spin up if we issue a simple DAX Evaluate statement. The Evaluate we are running is a table with two columns and a single row (the process date and checksum for the dataset) so it can't be spinning the model out into memory for that. Unless it does some pre-expansion on all the tables in the model? Equally if it is spinning the entire meta data model out for that, why does it sometimes spin the thing down? Why does the first user to hit it pay the price but not subsequent users?

    So unfortunately that post doesn't seem to be covering this scenario. 

    I suspect the next thing is to run a full XEvent trace on a server as we get one to spin up to see what events are being triggered by the connection/query. We currently run an xEvent trace on all the SSAS servers but we're not seeing anything in the data at the moment. The current trace is however oriented towards query and error capture. So maybe its other events that we aren't seeing.



    BI Addict!

    Friday, February 22, 2019 4:52 PM
  • SSAS does not have any feature that "spins down" tabular models. Even if you completely misconfigured the memory settings the memory cleaner threads will not touch the in-memory vertipaq stores. The only thing I know of that removes processed databases from memory is stopping the SSAS service itself.

    When you start the SSAS service it does not load all the databases into memory it just loads up the metadata about the databases, it is the first connection to a given database that will trigger re-loading it into memory. If I ever have to stop a production SSAS instance I will usually connect to it with SSMS after re-starting it as I know that will populate the databases collection which "touches" every database and forces the to be loaded back into RAM.

    But if you are not stopping/starting these query servers I'm not sure what else could be doing this. If it was a hypervisor issue you would not see the memory ramping up in task manager.

    Doing an XEvent trace, making sure to include the progress event messages is probably a good idea to see if that can provide some hints as to what is going on. - please mark correct answers

    Friday, February 22, 2019 10:47 PM
  • Hi,

    When starting SSAS for the first time, restarting the service, or Syncing, SSAS does NOT load the model in memory. (just try to start a stopped SSAS service - it will be fast and use almost no RAM)

    The first connection to it will force the real load of any database that is used. It can be all of them (if you open SSMS and expand the databases) or only one if you connect to a single model from Excel or PowerBI

    If  you want to make sure your end users find the model loaded after your syncs, it is quite simple. Just issue at the end of your SSIS package any action that will force SSAS to load the model. It could be a process default if the model is in stable state, or a simple DAX query to the model. 

    Hope this helps

    • Proposed as answer by zenzeinet Friday, January 24, 2020 6:10 PM
    Friday, January 24, 2020 6:09 PM