furmangg
Mosha Pasumansky wrote: |
Did you try to run the very same MDX query right after the full processing of the cube Do you see it at its fastest on the first day, and then gradually degrading 4% as time passes by Or is it as fast immediatelly after full process as it was on the first day |
|
Mosha, I'm able to fairly consistently reproduce a 4% performance improvement after restarting SSAS with SP2 installed on a 32-bit box. By bouncing around some reports on the cube in question in addition to bouncing around the Project REAL cube, I can fairly easily get SSAS to consume about 1.2 GB of memory (which is about 3x of what it consumes after I restart and run the query I'm performance testing). So I feel pretty comfortable stating that 4% number.
On several 32-bit boxes under SP1 left running for several weeks, I was able to see a 10-17% perf improvement by restarting... but I'm not as comfortable sharing those numbers as they are difficult to reproduce without waiting a week. The total memory usage was at or under 1.2 GB, so I can't exactly explain why it would be more than 4%.
On the production 64-bit box, SSAS did consume 8GB of memory after months without restarting. After restarting SSAS, I may have even seen a 25% performance improvement... but I wasn't really looking for it back then so I don't really feel comfortable sharing that number... and we've begun restarting SSAS service weekly on production since then, so I'm not sure I'll be able to repro that number.
At this point, I can't explain how SSAS got to the point where it was consuming 8GB of memory on production. It could be that querying and a daily full process over several months gradually causes it to consume more and more memory. Or it could be that a stupid user wrote a bad ad-hoc query. I'll post back if I get any more info.
Doing a full process doesn't appear to slow performance as much as when SSAS is consuming a lot of memory due to lots of data being cached.
Mosha, I got a notification that you had posted the following, but somehow it doesn't appear in the thread:
Mosha Pasumansky wrote: |
Is the following correct (based on the previous posts):
1. The difference in performance is 4%
2. The memory consumption at peak is about 8 GBs after several months
If this is true, then one explanation could be that since there is plenty of memory, the cache can grow without limits, adding more and more elements. At some point the cost of searching inside the cache (even taking into account that AS uses very sophisticated multidimensional indexing data structures to look in the cache) becomes a factor. 4% to search inside huge cache doesn't sound unreasonable.
Of course, I could be completely off, but this would be my guess. |
|
That was precisely what I was thinking... that the more memory used, the slower it is to determine whether something's cached or not.
In my opinion, a best practice would be to restart SSAS at least weekly. And you should also test carefully whether any cache warming you're doing is helping you more than hurting you. (We're not doing any cache warming, but there have been some recent blog entries on the topic, so I thought I would mention it in this context.)