SSAS Cache PerfMon Counters
- Hi,
I've done a Warm Up of the cache running some queries and just notest this behaviour looking for Permonace monitor:
MSAS 2008:Memory:
Memory Usage KB: 1.100.900,000
Cleaner Memory shunk KB/sec
When I finish running the warm up and done nothing on the database my Memory Usage KB starts shirnking without changing the Cleaner Memory shrunk KB/sec.
When the Memory Usage starts to shrink does it mean that the queries that I run are being erased of the cache?
Can I assume that if the cache is being cleaning up then the value of the Cleaner Memory shrunk will change?
Thanks.
All Replies
- I suspect that what you are seeing is temporary data structures being cleaned up. SSAS is pretty aggressive about holding onto objects in cache and will not release them unless the service is under memory pressure or the objects in the cache are invalid.
http://geekswithblogs.net/darrengosbell - please mark correct answers - But how do I know that cache is being cleaned up?
My server as 16 GB of memory and for some queries he goes to only 5 MB of Memory available. The cleaner Memory Shrunk doesn't change , is always 0.
When I try to run one of the queries again (after the warm up), it takes a long time to run , so I assume that it was cleaned up from the cache. In MSAS 2008: Memory the memory Usage KB doesn't go near the Memory Limit Low KB.
How can I discover that the results of my queries are being erased from the cache? - If you are not getting close to the Memory Low Limit KB then the cleaner will not be an issue.
There are many things that can prevent the cache being used aside from just the cache being cleared.
One idea would be to use profiler to look at some of the storage engine events to see if the query is hitting cache, aggregations or partitions. It is apparently possible to "over populate" the cache and even though something is in the cache SSAS may not find it and the query optimizer may give up scanning the cache and just hit the raw data.
Checking the Cache hit/miss/insert counters may also give you some hints.
Different security roles also get different caches and the exact nature of your queries and calculations can also have an impact.
http://geekswithblogs.net/darrengosbell - please mark correct answers This is the behaviour:
If I do the warm Up with queries that use a single Sum Measure then everything is fine.
I do the warm up using three measure groups ,and two of them are distinct counts, and then I check that they entry the cache (I see on profiler), but in my performance monitors the Cache Insert/misses are both incremented.
It's always the same security role.
My queries only change the value of the dimension that appears on the where clause, and it always looks for the aggregations and for the partitions when i execute each of them.
If i execute the query after the first execution is very fast and it is a direct cache hit but after a while it is again slow, depending the amount of rows that I bring.
"It is apparently possible to "over populate" the cache " - How can i be sure?
It seems that the more number of that I query , the less the cache is being hit.
Thanks.My queries only change the value of the dimension that appears on the where clause, and it always looks for the aggregations and for the partitions when i execute each of them.
If i execute the query after the first execution is very fast and it is a direct cache hit but after a while it is again slow, depending the amount of rows that I bring.
I think your distinct count measure groups are probably your issue. With measure groups with aggregateable measures, the cache is also aggregateable. So if you query calendar months on the rows and then later do a query with calendar quarters on the rows, SSAS can use the month cache and roll it up to quarters. So you get more cache hits and better performance. With distinct count measure groups, every different query needs to scan the partition data.
http://geekswithblogs.net/darrengosbell - please mark correct answers
