Cache Warming on an Analysis Services Tabular server

Article
07/22/2013

SQL Server 2012 introduces the new tabular BISM modeling in Analysis Services. If you have many tabular databases deployed, or several large tabular databases, a server restart can cause a performance hit. The typical scenario is that when the Analysis Services service starts, it does not immediately load and warm the cache of the database files. The first user will have to wait for their database to load which can take 30 seconds up to a few minutes, depending on how many GB of files there are to load.

The database will get loaded on demand when the first connection is made to that database. The Vertipaq engine will read all the database files from the disk and use the RAM as a cache to populate the data structures in memory.

For example, when using Management Studio, when you connect to the tabular instance shortly after starting the service, it will query metadata from ALL databases. The SSMS window may become nonresponsive until the server replies with all the metadata requested.

One simple way to forcibly warm the cache ahead of time is to run a simple Discover command to query each database metadata.

I have also filed a design change request to request the Analysis Services product group to please add a feature to autoload some or all databases. This could be triggered internally by the service whenever it starts to save user effort from creative workarounds. If you like the idea file it on https://connect.microsoft.com/sql

A simple workaround is to run a cache warming query:

Make a new MDX query window in Management Studio and runthis kind of query to illustrate how a simple cache warmer would work. This isn’t practical, because by the time Management Studio has shown you the list of databases, it has already queries all the metadata and the data files are loaded in RAM.

SELECT * FROM $System.DBSCHEMA_CATALOGS

Make a new XMLA command window and run:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions />
<Properties />
</Discover>

Automation to open the databases:

1. ASCMD.exe

To automate calling those we could use ASCMD.exe, which is an old sample back from 2005 days.
https://msdn.microsoft.com/en-us/library/ms365187(v=SQL.90).aspx

You could make a batch file that you run shortly after starting the service.

2. Schedule it

Maybe a SQL Agent job is sufficient to run the code on a schedule every couple of minutes. However, it is harder to trigger it to start whenever the service restarts. I can use a SQL Server Analysis Services Query job step type.

The job interface requires a database name, which is not very useful for the discover query we need to run, so that’s annoying, but at least its automated.

Note: that the default SQL Agent Service Account may not have access to Analysis Services databases to run any query. If you use a domain account for your Agent service account, you may add the domain account into the AS security to run the query; but if the Agent service is started with the system account or the NT Service\SQLAgent$ account, you would need to create a credential and proxy and specify the “Run as” on the job step to run as another user, and give that credential/proxy account rights in Analysis Services.

3. Windows Task Scheduler

Similarly you could use Windows Task Scheduler to run a batch file or start a powershell script.

A. Note in the Windows Event Viewer events when your AS Tabular instance starts.

There is an Event 0 (289) from the source MSOLAP$InstanceName when my service starts up- that may be a good trigger to launch the powershell script.

Log Name:      Application
Source:        MSOLAP$TABULAR
Date:          7/22/2013 9:14:44 PM
Event ID:      0
Task Category: (289)
Level:         Information
Keywords:      Classic
User:          N/A
Description:
Service started. Microsoft SQL Server Analysis Services 64 Bit Developer (x64) CTP 11.0.3000.0.

B. Make a powershell script in notepad to prime the Analysis Services tabular databases.

Save the script with the .ps1 extension in a folder which is easily accessible.

Mine may not be perfect, but it is simply to load the sqlps, connect to my analysis services tabular instance, and run a query. Change the server name and instance name to match your computer.

import-module sqlps
cd SQLAS\MyServername\MyInstanceName
Invoke-ASCmd -Query "SELECT * FROM $System.DBSCHEMA_CATALOGS"

Test your script in the powershell command first before proceeding.

Powershell C:\Scripts\CacheWarmer.ps1

C. Now use Task Scheduler to watch for this event and trigger the cache warm up routine.

Start > Run > MMC.exe. Add the Task Scheduler snapin. Connect to the local server.

Pick the triggering event at Application, your MSOLAP$InstanceName, Event ID 0

Have it start a program - powershell and run your cachewarmer.ps1 script (as an argument – use double quotes around the path if it has spaces)

E. You can Finish the wizard, and view the properties.

If you need to change the account which runs the routine, do so in the properties. The account will need rights within your Analysis Services to run the query.

F. Test the Task, by stopping and starting (restart) your tabular instance service.

G. Now check your task scheduler status and see if it ran.

You can close the windows and check the history after you restart the services a few times to test it.

Share via