Warming the OLAP cache

Analysis Services has a data (and aggregation cache) not dissimilar to a relational database cache. For really good cube performance it is desirable to have most of your cube resident in the cache. Querying the cube will help bring data into the cache, while cube processing will tend to clear the cache. More accurately, partition processing (incremental or full) will clear that entire partition from the cache.

Analysis Services, by default, will freely use 65% of a computer's memory, so data should not be pushed out of the cache unless AS is using 65% of memory. With commodity 64 bit servers, the memory available to Analysis Services should be, at least, in the tens of GBytes.

Here is an example of a query that has been run on a cold cache. Notice how the first Query Subcube has an EventClass of "non-cache data". This means that the storage engine is going to disk since the data/aggregations are not in cache.

You can help warm the cache by executing MDX queries, or you can execute the CREATE CACHE statement. The CREATE CACHE statement looks similar to a regular MDX query, the main difference being a resultset is not returned. For example

CREATE CACHE FOR [EasternMining] AS
[Port].[Ports].&[80]
*{[Measures].Members}
*{[Date].[Year Month Day].[Year].&[2010].[January].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3]}
go
CREATE CACHE FOR [EasternMining] AS
[Port].[Ports].&[80]
*{[Measures].Members }
*[HoursOfDay].[Hour Of Day]
*{[Date].[Year Month Day].[Year].&[2010].[November].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3]}go

Now let's run the query again and check the Profiler trace. Here is the output

Notice now, all the Query subcubes have an EventClass of "Cache data". This means that AS has found the data for this query in the cache. Also note, the duration for the first subcube is 0ms. This is a great improvement over the original cold cache subcube of 47ms.

Generally, it is a good practice to warm the cache after cube processing. This will help the performance of the first few queries that hit the cube after processing.

By the way, when warming the cache you should be aware of SSAS' limitation in keeping data in the cache see http://richardlees.blogspot.com.au/2011/12/why-doesnt-ssas-cache-entire-cube.html

Also, as a cube designer, you should be aware of the cache warming effect and design partitions so that you isolate the volatile data as much as possible, so that you minimise cache clearing when processing.

If you want to experiment with cache warming, you will probably want the ability to clear the cache quickly. Here is an example of a cache clearing statement for a cube

Here is the MDX query I used in the demonstration above

WITH
member measures.MyCalc as
avg(tail(nonempty([Date].[Year Month Day].[Year].&[2010].[November].[1]
:[Date].[Year Month Day].[Year].&[2010].[December].[3].lag(1)
,[Measures].[Bytes Total]),6)
,[Measures].[Bytes Total])
SELECT {measures.MyCalc} ON COLUMNS ,
{[HoursOfDay].[All HoursOfDay].[00:00-01:59].[00:00-00:59],[HoursOfDay].[All HoursOfDay].[00:00-01:59].[01:00-01:59],
[HoursOfDay].[All HoursOfDay].[02:00-03:59].[02:00-02:59],[HoursOfDay].[All HoursOfDay].[02:00-03:59].[03:00-03:59],
[HoursOfDay].[All HoursOfDay].[04:00-05:59].[04:00-04:59],[HoursOfDay].[All HoursOfDay].[04:00-05:59].[05:00-05:59],
[HoursOfDay].[All HoursOfDay].[06:00-07:59].[06:00-06:59] ,[HoursOfDay].[All HoursOfDay].[06:00-07:59].[07:00-07:59],
[HoursOfDay].[All HoursOfDay].[08:00-09:59].[08:00-08:59],[HoursOfDay].[All HoursOfDay].[08:00-09:59].[09:00-09:59] }
ON ROWS
FROM EasternMining
WHERE ([Port].[Ports].&[80] )