There might not be enough Ram available to Retrieve the Data, as when the ETL Completes, it has used up most of the Memory on the Server

Utilize the SSAS Profiler and the Activity viewer on codeplex. Also I would turn on perfmon with various SSAS related counters including those related to memory utilization, deadlocks, subcubes, locks, aggregations and of course non SSAS related counters. Based on the results of these three tools, would could potentially see if it is single poorly written MDX query which is causing the issue (like some of those coming out of Excel when groupings are used). Or maybe the server is running out of memory (although you will likely get an error in that instance). Also, many SSAS instances also run other tools like SSRS etc, and it could be a query in SSRS that is bogging down the server. Also, the profilers and activity viewer could quickly show you if you have a large amount of activity for the current period, maybe a month end situation.

Presuming the cubes are MOLAP I would check environmental issues with the server/network, then look into aggregations and partition design. The data volume/complexity may have reached a volume where aggregations and partition design are not optimal

First check the load on server. What process are using more resurces. i other then SQL kill them

otherwise, Profiler to diagnose the cause

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 :-)

We can do following things 1.We can use Performance Advisor for Analysis Services :http://www.sqlsentry.net/performance-advisor/sql-server-analysis-services.asp 2.Have a look at the Designed cache structure for SSAS 3.Look into partition stuff and analysis according to performance

First:Restart a Machine if not Restart Sql Server Services Second: Check Memory Deadlock. Third: Profiler to identifier the resources.

Very interesting suggestions so far but this subject needs more explanation.

• Need to revisit the aggregates in the tune them if required.
• Partition slice the cube.
• Need to check the buffer size.

Thank you all for participating :)