SSAS Freezing. Needs restarting or server reboot.

Have you had the situation where SSAS becomes unresponsive, consuming too much memory and the only way out is to restart SSAS or to reboot the server?

With the default settings of SSAS this can happen with heavy MDX queries.  When I say heavy queries, I really mean queries that require large amounts of memory.  It is helpful to think of SSAS satisfying queries from within memory, which isn't exactly true, but it helps understanding.  This is very different to SQL Server, where a resultset does not need to be instantiated in memory, so that a 5 TB table can be returned from one query with a modest memory overhead.  With SSAS, the query cellset is generally constructed in memory.  So if your query asks for 100 columns and 500,000 rows, then think of 50 Million cells instantiated in SSAS memory. 

The memory required for a single query is not restricted by Memory\TotalMemoryLimit and can continue to consume memory until either the query completes or the system grinds to a halt with Windows page thrashing.

One might argue that you should write MDX queries that do not consume so much memory, but I believe that queries should not be capable of crippling the system.  Unfortunately there is no way to cap the memory consumption of individual queries.

The easy and recommended (by me) solution to this issue is to set the Memory\HardMemoryLimit (new in SQL 2008) to a value between Memory\TotalMemoryLimit and 100.  TotalMemoryLimit is 80 by default (that's 80%), so you might set HardMemoryLimit to 90, which will cause SSAS to begin cancelling queries once memory exceeds 90%.  The queries that get cancelled would have had little chance of succeeding.  They recieve the following message.
Server: The operation has been cancelled due to memory pressure.
My recommendation is to set the HardMemoryLimit even before you suffer from these heavy queries. It is better to have queries terminated than SSAS request excessive memory and cause Windows to thrash. If you want to err on the side of not cancelling queries, set the HardMemoryLimit closer to 100. If you want to minimise the possibility of having Windows thrash, set the HardMemoryLimit closer to TotalMemoryLimit. Never set the HardMemoryLimit equal to or less than the TotalMemoryLimit.




If you find that queries are being cancelled that you would like to have complete, then I would suggest you either tune the queries to use less memory or add more RAM to your x64 system.