If the symptom is a sudden decrease of speed, I would use the following tools:
- SSAS Profiler: profile the whole query and analyze what part of the execution is time-consuming. There are two main issues that could cause the performance issue: the Formula engine could be using too much time because complex calculations are necessary, and the subcube queries could not hit the cache any more (or not too often)
- Performance Monitor: there is a very good page here that explains which performance counters display useful hints. The key counter here would be "Memory Usage KB", which should be compared against "Memory Limit High" of the SSAS:Memory section.
In general, I would suspect that the amount of data used to calculate the query result has reached the memory limit of the server. It is well known that the query performance, expecially if string comparisons are used in the query, highly degrades when not all data can be kept in memory buffers. SSAS then switches to disk mode and reads the data again and again.
If SSAS Profiler show that the query is the only one executing, I would suggest to add more RAM to the machine. If that is not possible, partitioning the fact data could solve the problem, if the query can be formulated that it only accesses some partitions (so memory consumption of cache is less).
If SSAS Profiler shows that other queries are processed in parallel, you should try and serialize the queries.
If partitioning is already done, sou should examine the "Process" entries ans see, if too much partitions are accessed by the query. In this case, rewriting the query or (if it's the main query on the cube) redefinition of the partitioning algorithm would be an option.
If all that does not help - put on your head light and dive into the system :-)
Replied on Jun 9 2011 2:22PM