SSAS – Force into loading Databases
When the SQL Server Analysis Services (SSAS) Tabular Service is started, it can take a long time before it is ready to be queried. This can cause delays to Service, not to mention confusion.
This Blog Post will explain what is happening during this time and a method that can be used to improve. It’s worth mentioning that the SSAS Tabular Databases that this has been used on are quite large (> 100Gb).
Is SSAS Tabular loaded or not?
By default, SSAS will start the Service but not load any Databases into memory.
In order to access SSAS Databases, they need to be in memory.
This means that following a restart of the Server, for example after Windows Patching, SSAS Tabular can appear unresponsive at first.
This can be shown by restarting SSAS, opening the SSAS instance in SSMS and clicking the + sign to the left of “Databases”. It is only at this point that SSAS will attempt to load the Databases into memory. This can be an issue for example if a Server is rebooted over a weekend – the first person in after this event may be waiting some time if the Databases are large (or if it’s an automated process then it may timeout).
As discussed in a previous Blog Post, evidence can be found of this by looking at the memory usage of the “msmdrv” Service. It increases as SSAS loads Cubes.
Can SSAS Tabular be forced (to load more quickly)?
There may be a number of ways of dealing with this, but I will share what has worked for me.
Running an MDX query against an SSAS Tabular Database to read data will cause it to be loaded into memory if it’s not already loaded (in order to provide the results).
By running the MDX query against each of the Databases in your SSAS Instance, you can load them all into memory.
In my case, the following code can be used to simulate what happens when the “Databases” section is expanded:
SELECT DISTINCT
[DIMENSION_CAPTION],
DIMENSION_CARDINALITY AS [Count]
FROM $system.MDSchema_Dimensions
By Scheduling the queries to be run against SSAS, and setting the Schedules to run upon SQL Server Agent Startup (which will happen each time the Server is re-started), Databases can be loaded more quickly and in a more controlled manner.
After setting this up for the first few Databases, I had a thought. Can you query multiple Databases simultaneously? It turns out that you can, and this can significantly reduce the amount of time to load all of the Databases on an SSAS Tabular Instance.
The overall duration will be bound by the time taken to load the largest of the Databases.
Scheduling the load of SSAS Databases
Create a SQLAgent Job to run on a SQL Server instance that exists on the same Server as SSAS. I know that best practice dictates that SSAS would be hosted on a separate Server, but this rarely happens with the cost of SQL Server licensing. I would go as far as recommending that a small SQL Server Instance is added just for this purpose.
The first Step of the Job should be to just delay by a set amount of time. This is useful for two reasons – one, it provides an quick and easy way to see how long the Job has been running for as the initial step is recorded in the Job History.
Second, when gMSA (Group Managed Service Accounts) are used for SQL Server, then the Services need to be set to “Delayed Start”, therefore SQL Server Agent might start before the SSAS Tabular Service is available (default is two minutes). By including a delay of perhaps five minutes, this gives sufficient time for all of the Services to start.
Under Steps, create a Step to delay by five minutes:
Now, create a second Step to perform an Analysis Services Query against the Cube. Note that you need to select Type of “SQL Server Analysis Services Query” and specify the Server Name and Port of the SSAS Tabular Instance.
The SQLAgent Job should be set to “Start automatically when SQL Server Agent starts”.
By creating multiple SQLAgent Jobs, multiple Databases can be set to load simultaneously. I was not able to see a simple way of automating this, but knowing the size of the Databases to be loaded allowed me to schedule a few to run this way, with smaller Databases being queried using multiple steps in a “catch all” SQLAgent Job.
Through using this method, I was able to reduce the time between Server restart and SSAS Tabular data being available from 90 minutes to 15 minutes. That’s a great result to make patching or reboots more acceptable. There’s also the advantage of knowing exactly what’s happening – all important for those moments where someone’s stood behind you, willing it to load more quickly.
Hope this helps, Nigel.
