How to clear SSAS cache using C# for query performance tuning

First let me give you a little background of why you would want to clear SSAS cache from C# code when you can do this using an XMLA command from SSMS.

If you have a slow MDX/DAX SSAS query , you have a couple of options for improving the performance (assuming no hardware changes):

1. Rewrite the query differently if you have control over the query. (You will have two queries that you want to compare against the same database.)
2. Make changes to the SSAS database to follow a better design. (You will have one query to run against two databases)

Regardless of which route you go, you should compare the performance before and after the changes to see how much you gained from the change.

Measuring performance improvements for one or a couple of queries

If you only have one or a handful of queries, you can compare their run time in SSMS. In order to get a fair comparison you have to measure the query run time with and without a clear cache. You can clear the database cache by running an XMLA command in between runs (See XMLA code for clearing SSAS cache).

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

MyTestDatabase

</ClearCache>

The above code clears the SSAS database cache for MyTestDatabase.

Measuring performance improvements for a large set of queries

Now let’s suppose you have captured a large number of long running queries from a production server and you are working on making changes to your SSAS database design (by following best practices,…) to improve overall query performance. If you can deploy two databases side by side to a Dev or QA environment, you can run the set of queries side by side, record their run time and compare the results to see how your changes have affected the overall performance. This will give you a good indication of how your changes will impact a live query load in production. If you can improve overall query performance on a Dev/QA environment, you will most likely get at least a good percentage of the improvement in your production environment. The reason I say a good percentage vs all is that usually Dev/QA environments have slower hardware and most probably you will be the only user or one of the few users on the Dev/QA environment running queries so the load conditions are different).

In order to get a good measurement, you need to run the set of queries two different ways:

1. Run the batch without clearing the cache before each query. This is similar to how a live load in production would run (although sequentially vs. many connections at the same time in production). Some of the queries will benefit from previous queries since they may ask for calculations/data that may already be in cache.
2. Run the batch and clear the cache before each query.

All of this can be done using C# or script task in SSIS. You store the queries in a SQL table and then read them back with a program written in C# from either Visual Studio (Cosole App) or a script task in SSIS. Then you run each one at a time, store their run time results in another table, and then analyze the results using either SQL or Excel. Optionally you clear the cache between each query.Clearing the cache between each query allows you to check individual queries and pinpoint the ones that improve the most or do not benefit from your changes.

When I started searching the methods in Analysis Services DLLs (note: the DLLs have changed since SSAS 2016) looking for a method I could call to clear the cache, unfortunately I did not find anything. After much search, I came across this old post by Darren Gosbell where he used Microsoft.AnalysisServices.Xmla DLL. The code is unbelievably easy to the point where I had to verify it was actually submitting it by running an extended events trace (profiler still works pretty well for SSAS too) and observing the difference. 

Sample Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.AnalysisServices.Xmla;
/* If you see a red squiggly line under Microsoft.AnalysysServices.Xmla above
* it means that you need to check your reference to XmlaClient class.
* From solution explorer, go to References –> Add Reference –> Browse –> Navigate to
* Microsoft.AnalysisServices.XMLA on your machine and add it. *
**/

using System.IO; //This is added here so that you can reference the File class without specifying the full name.

namespace Clear_SSAS_Cache
{
class Program
{
static void Main(string[] args)
{
//Notify the user and make sure they actually want to run this code!
char PressedKey = ‘N’;
Console.Title = “Clearing SSAS database cache!!!!!”;
Console.WriteLine(“Clear SSAS database cache? Press Y for Yes or any other key for No followed by Enter.”);
ConsoleKeyInfo PressedKeyInfo = Console.ReadKey();
Console.WriteLine();
PressedKey = Char.ToUpper(PressedKeyInfo.KeyChar);

if (PressedKey == ‘Y’)
{
Console.WriteLine(“Clearing the cache…”);
XmlaClient clnt = new XmlaClient();
//Update the following to your SSAS server instance.
clnt.Connect(@”localhost”);
//update the following path to point to your XMLA file.
string xmla = File.ReadAllText(@”C:\Clear_Cache.xmla”);
clnt.Send(xmla, null);
Console.WriteLine(“Cleared the cache.”);
clnt.Disconnect();
}
else Console.WriteLine(“No changes made.”);

Console.WriteLine(“Press any key to continue.”);
Console.ReadKey();

}
}
}

Obviously do not run this against a production server since clearing a production SSAS database’s cache impacts the performance negatively. Also please note that the above code does not have any expectation handling so if the XMLA passed has any errors it will error out.

I used the above method to clear the cache between queries in the program that runs my query batches. Once I have the results, I can pinpoint the queries that improved the most and the ones that did not improve. It is important that this part be done on a clear cache. Once I have this data I can go back to the design and focus on the parts that did not improve.

Adding a reference to Microsoft.AnalysisServices.Xmla

If you get a red squiggly line under “Microsoft.AnalysisServices.Xmla” in your code, you are missing a reference to your project.

Search for Microsoft.AnalysisServices.Xmla.dll on your machine. Usually it is installed with SSAS at “C:\Windows\Microsoft.NET\assembly\GAC_MSIL” along with all the other SSAS DLLs.

If you don’t have it on your machine, you can copy if from any other machine that has SSAS running into a folder on your machine.

Once you locate the file, you can add a reference to it to your project by right clicking on References in Solution Explorer. Then click on Add followed by Browse. Find the file and click Add and then OK. The red lines should go away now.

Extended Events Trace for capture Command Begin/End

<Create xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<ObjectDefinition>
<Trace>
<ID>Catch Clear Cache Commans</ID>
<Name>Catch Clear Cache Commans</Name>
<XEvent xmlns=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300″&gt;
<event_session name=”Catch Clear Cache Commans” dispatchLatency=”0″ maxEventSize=”0″ maxMemory=”4″ memoryPartition=”none” eventRetentionMode=”AllowSingleEventLoss” trackCausality=”true” xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<event package=”AS” name=”CommandBegin” />
<event package=”AS” name=”CommandEndWithError” />
<event package=”AS” name=”CommandEnd” />
<target package=”package0″ name=”event_stream” />
</event_session>
</XEvent>
</Trace>
</ObjectDefinition>
</Create>