SSAS Tabular Cube Reload (Seems to need a user to trigger the load of the data form disk)

Question

We are seeing some odd behaviour on our SSAS instances. We process our cubes as part of an overnight job on different environments, on our prod environment we process the cube on a separate server and then sync it out to a set of user facing servers. We are however seeing this behaviour even on environments where we process and query on a single instance.

The first user that hits any environment with fresh data seems to trigger a reload of the cube data from disk. Given we have 2 cubes that run to some 20Gb this takes a while. During this we are seeing low CPU utilisation, but, we can see the memory footprint of the SSAS instance spooling up, this is very visible if the instance has just been started as it seems to start using a couple of hundred Mb initially and then spool up to 22Gb at which point is becomes responsive for end users. During the spool up DAX stuiod/Excel/SSMS all seem to hang a far as the end user is concerned. Profiler isn't showing anything usfeul other than very slow responses to META data discover requests.

Is there a setting somewhere that can change this? Or do I have to run some DAX against the cube to "prewarm" it?

Is this something I've missed in the past because all my models were pretty small (sub 1Gb)

This is SQL 2016 SP2 running Tab Models at compat 1200.

Many thanks

Steve

Answer 1

I see that you are suffering from an acute OLAP cube cold. :) You need to get it warmer (as you've guessed it, you need to issue a command against it, after (re)starting the service).

What you want to do, is issue a discover command - a query like this one should be enough:

SELECT * FROM $System.DBSCHEMA_CATALOGS

If you want the full story, and a detailed explanation on how to automate this warming, you can find my post here: https://fundatament.com/2018/11/07/moments-before-disaster-ssas-tabular-is-not-responding-after-a-server-restart/

Hope it helps.

Have fun. :)