I recently was required to check on the performance of an SSAS instance. We had a new OLAP model with several MDX queries that we wanted to ensure were performing adequately. Now, you may know that SSAS is using caching in order to speed up its response time. Initial queries are directed to disk and once data is first read, it is then being placed in cache to serve future queries faster, eliminating the need to access disk again.
What we did first was to clear the cache, using the following XMLA command:
Where the “DBName” stands for our OLAP database. Next we started hitting the server with MDX queries and ran Profiler against the instance to gauge response time and query behaviour. We were using the “Query Begin” and “Query End” events as well as the “Get Data From Cache” and “Query Subcube Verbose” to monitor cache behaviour.
We ran Windows Performance Monitor in the background as well, with the following counters:
1. Total Queries From File: this counter tells us the number of queries being serviced by the file system, i.e. before data is cached.
2. Total Queries From Cache: this counter tells us the number of queries being serviced by the cache.
When we ran the first MDX query, we saw an increase in the “Total Queries From File” counter and the next time we ran the same query we saw an increase in the “Total Queries From File” counter, which is exactly the behaviour that we were expecting.
However, looking at Profiler, we noticed that the “Get Data From Cache” event was raised even when we ran the MDX query for the first time. We can provide an answer to this seemingly abnormality by looking the “Query Subcube Verbose” event. When running the query for the first time, you will see an indication that the engine queries “Non-cached data” first and that issues a query on “Cached Data”. The second time you run the MDX query, we only see the "Cached Data” query events being raised. So, the way this all works is like this: the Query Processor engine will always execute its queries from cache. This is why we will always see the “Get Data From Cache” event raised, regardless of the fact cache was cleared or not to begin with. However, when no data is in cache, it will trigger a “Non cached data” query first, the Storage Engine will fetch data from disk, place it in cache, and the the Query Processor engine will query this newly fetched cached data.
Hope this will help anyone who is doing SSAS performance monitoring!
(This is post number 16 for week number 16 of my SQL blog challenge)