Analysis Server appears to hang…..

We had an ongoing problem where by users would suddenly start complaining that the Analysis Server was hanging. When investigating the box we could see that there appeared to be no physical constraints. Disks were a bit above average, CPU was very low and there was plenty of memory.

When looking at the server through the SSAS activity viewer (part of the community samples) we could see that users were definitely queuing up and many of the queries should have returned in less than a second but were hanging around for ever (30 minutes or more). It was as if we were experiencing some form of blocking…….

To compliment our monitoring we use ASTrace (also part of the community sample) to do real time logging of what's happening on the SSAS server to a SQL Server and amongst the capabilities it gives us is the ability to run a little procedure to show us what mdx/xmla is running with duration etc (its much more friendly than the activity viewer). So, when we were experiencing the trouble our script showed that an MDX query that touched multiple partitions totalling 100’s of gb’s appeared to be at the head of the chain every time.

Amongst the SSAS performance counters we were monitoring 2 particular counters gave us real insight into the bottle neck, these are:

MSOLAP\THREADS:Processing pool busy threads
MSOLAP\THREADS:Processing pool job queue length

You may think that processing threads are just used for cube processing but in fact they are also used for reading partition data. Using these counter, we could see that when we were experiencing the hanging all processing threads were in use and there was a constant queue and as we had already increased our processing threads to an appropriate value so further increases were not on the agenda.

What this did confirm to us though was that we were experiencing a form of MDX Blocking caused by a query consuming all threads and effectively starving all other queries.

So, fortunately there are 2 server configuration settings you can change to prevent the blocking from occurring and these are listed below. Its quite obvious these settings will address the blocking, NOT! :)

So how did i find these little gems? They are in the SQL Server 2008 Analysis Services Performance Guide (yes i know this is 2005) and its a MUST READ for anyone that has an SSAS server. I consider this guide gold dust and if you have not read it, shame on you! :) The people that wrote it truly know their stuff. There is a SQL Server 2005 Analysis Services Performance Guide but the settings were not in that one.

Please note, these particular settings are in SP2 but not RTM.

Now, the last thing to say about the settings which is stated in the document. It is indicated that there is an impact on overall throughput but my testing did not identify any. However, just because i did not see any performance degradation it does not mean you will not so make sure you test.