How long until SSAS Tabular starts up? Real soon now, or maybe never!
This article is based on my experiences with SQL Server Analysis Services Tabular, together with large SSAS Databases (Cubes) – i.e. over 100Gb. Your experiences might differ, as “classic” SSAS works differently, and you may be lucky enough to be working with relatively small Databases.
SSAS appears to start quickly
When starting SQL Server Analysis Services (SSAS) Tabular, the Service is quick to report that it has started (and it has). In my opinion, this status is not entirely accurate.
SSAS can actually be very slow to load
When accessing SSAS, most users want to use data held in a Database (Cube). SSAS only loads this data into memory when a user attempts to view the list of Databases – i.e. using SSMS to connect to the SSAS Instance, right-clicking on Databases. At this point, SSAS decides it should load the Databases and perform basic consistency checks. This can take a long time.
SSMS becomes unresponsive, and can also block your usage of other query windows on other Servers (SSAS or SQL Server). If you try to query the SSAS Database remotely at this point, you will receive errors. SSAS is busy, but it won’t tell you what it’s doing and certainly won’t give you a clue on how long it’ll be before you can access data.
Contrast this with the regular SQL Server engine.
While SQL Server works in the same way, it is clearer to see exactly what is going on with the SQL Server Engine. The SQL Server ERRORLOG displays status information as the SQL Server starts, listing each Database as it goes through the Recovery process to ensure data integrity.
For SSAS, loading the Databases is an “all or nothing” operation. It does not update the list of Databases each time another one is loaded, just once it has processed all of the Databases. This is certainly different from how the SQL Server engine operates.
SSAS Loading Process
As part of the startup routine, SQL Server lists each of the connected Databases that it has to load. It will report individually as each one is integrity checked and made available. If an individual Database fails at this point, it will be marked as “suspect”, but the SQL Server will be ready for use, together with the other Databases.
SSAS does not tell you what it is doing – it has a Log file (msmdrv.log), but SSAS does not list the Databases as it loads and performs integrity checks as the SQL Server Engine does. As mentioned in a previous post, I am learning more and more about SSAS so may well update this in the future as I learn more. Please feel free to email me if you can help.
Starting SSAS is very much an “all or nothing” operation. If a Database fails to load then SSAS is unlikely to start.
The lack of progress can cause issues.
I have observed a situation where engineers have re-started an SSAS Server (because that fixes everything) because it hasn’t started up in a reasonable time. I have also seen situations where SSAS has gone into an endless loop, trying to start, hitting a corrupt Database, then re-starting.
It is possible to configure SSAS to provide feedback on what it’s doing, but that needs to be set up beforehand. So, what can you do?
Earlier in this article, I mentioned that SSAS takes time to load Databases into memory.
This behaviour can be observed from Windows Performance Manager. Find the “msmdrv” service and observe that the memory usage increases over time until it settles – at this point, all of the Databases are listed in SSMS.
As a rough check, is the memory allocated to “msmdrv” continuing to increase? If it is then it’s still loading and it won’t show any Databases until they have all been loaded into memory. Leave it alone!
How long before SSAS loads?
But how long is it going to take? In my experience, there’s a correlation between the size of the SSAS Data (DataDir folder) and the amount of memory used. This comes in handy as you can estimate how long it’s going to take for SSAS to fully start if you know the amount of memory that it will be using. In my experience, the memory required is roughly double the size of the DataDir folder.
Baseline
Baselines are useful here. One scenario that I have seen is where the Server hosting SSAS is restarted, SSAS does not respond for some time (because it’s still loading Databases into memory), causing panic. This results in the operator restarting the Server again, potentially getting into an endless loop until someone is contacted who has SSAS experience (in this example me), and I told them not to panic – it typically takes over an hour to fully restart SSAS on that particular Server. Again, it helps if you have experience in knowing how long it takes for this step to complete (if not, keep watching the memory!).
Corrupted SSAS Database
If an SSAS Database is corrupt then SSAS will fail to load, but it will not report the issue. No Databases will be shown, and the “MSMDRV” Service will stop increasing its memory usage.
The memory usage will then start to reduce again until it gets back down to zero. At this point, the whole process is repeated in an endless loop. This can be quite frustrating, to say the least!
Removing corrupted Databases will be covered in a future blog post.
In the unlikely event that someone is reading this post and has a corrupted Database, please e-mail me directly. I will provide instructions on how to resolve the issue.
Nigel.
