SSAS Clearing the cache

In SSAS, when you’re trying to do performance tests, it’s important to know what performance is from the cold cache. You definitely don’t want a warm cache messing with your measures that you’re trying to get metrics on! An important item of consideration is that SSAS is NOT like the database engine side of SQL Server, in that you run one command and you’re done. Since SSAS has 3 separate distinct parts (the formula engine, the storage engine, and the file system) you want to ensure that you clear the cache in all 3 places in order to get a clean run.

Clearing the formula engine and the storage engine is pretty simple, just open up an XMLA query window and run the following command: (I had some issues getting wordpress to take the “object” tag, so be sure to insert the “<” back in prior to running. Sorry!)

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

Object>

<DatabaseID>Your Database Name Here</DatabaseID>

/Object>

</ClearCache>

In addition to this, there is also the small matter of the actual physical files that are created for the cache. Just running the above is going to clear out half of your cache, but remember that since Analysis Services creates the files physically, just clearing out the logical layer isn’t going to get us the right results. Luckily, we have an app codeplex project for that! It’s an AS Stored Procedure project located here. Download it, install the Assembly into Analysis Services, and then run your Analysis Services mdx script like normal!

Once you’ve registered the assembly, the actual script is below. Each of these calls needs to be executed separately.

//checks the size of the Windows File System Cache

call ASSP.GetFileSystemCacheBytes()

Running this should yield something that looks like the below:

image

//clears the size of the Windows File System Cache

call ASSP.ClearFileSystemCache()

And you’ll get a window that looks like the below:

image

Sorry, nothing exciting here. 🙂 Just a note that the command completed successfully.

Now let’s run our first command again:

//checks the size of the Windows File System Cache

call ASSP.GetFileSystemCacheBytes()

Our new results windows looks like:

image

Notice how much of an improvement we gained. This was just using my local multidimensional Adventure Works project. An important item to note is that the asstoredprocedures codeplex project is not offically supported by Microsoft, as it’s a codeplex project. The only other alternative that I know of that you could use to clear the file system cache is to use the fsutil.exe utility, which is supported by Microsoft. However, this utility requires that 1) Analysis Services is on it’s own separate drive and mountpoint and 2) you would have to run it outside of regular business hours, as the utility causes disruption to anyone currently using the cube. In my opinion, this assembly with the right command does the exact same thing and with a much less hassle and no un-mounting required.

So in conclusion, when clearing the cache in Analysis Services, keep in mind that you need to clear it everywhere, not just the formula and storage engine.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s