Here’s something that had me stumped for quite a long time earlier this week…
I’m doing some performance tuning work for a customer at the moment, and following my usual approach I took a test query, cleared the cache, ran it and noted how long it ran for and what I saw in Profiler and MDX Studio. However this time I saw some strange things happening: I’d run the same query on a cold cache more than once and the query times would differ by anything up to a minute; even more confusing, the information I got from Profiler and MDX Studio would be different too – for example, the number of Query Subcube Verbose events in recorded in Profiler for a query might differ from run to run by several hundred. This had me completely confused. There was no-one else on the server and I was running on a cold cache. What was going on?
It turned out that I was running out of memory. I was working on a 32-bit box with a lot of databases on, so even on a cold cache the amount of memory used by SSAS was approaching the LowMemoryLimit threshold (see Marco’s post here, the AS2005 Performance Guide and this white paper for an explanation of what this is). When I ran the query – which had a lot of calculations – memory usage went above the LowMemoryLimit so the cleaner thread was waking up and evicting elements of shrinkable memory, which presumably included data in cache that had been put there by the current query. Since the exact moment that this threshold was crossed would vary from run to run, and as I assume that what the cleaner thread would evict from memory would also vary, this explained the different query times and Profiler activity. As soon as I moved onto a 64-bit box with no other databases this stopped happening.
Of course nowadays I would expect that most people are running on 64-bit so it’s much more unlikely that you’ll ever cross the LowMemoryLimit, but I do see it happen from time to time. So if you’re in the same position and suffer from inconsistent query performance, check your memory usage!
Hi Chris,This is interesting, I\’ve been facing with performance issues for the last month. The difference is that I have 64 bits servers. But I don\’t discard problems with memory usage.How can I check if the AS2005 is reaching that limit? Is there a specific perfmon counter?Best regards,Fernando
Take a look at the \’Memory Usage KB\’ and \’LowMemoryLimit KB\’ counters in Perfmon. If the first is greater than the second before the query runs, or while it runs, then you\’re hitting this issue. But as I said it\’s highly unlikely this will happen on a 64-bit system – there are probably other more likely causes for your performance problems. I suggest reading the AS2005 Performance Guide for some tips.