Home > Analysis Services, Locking, Performance, Tips, Trouble Shooting > Analysis Server appears to hang…..

Analysis Server appears to hang…..

We had an on going 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! 🙂

Setting Default Multiple-user nonblocking settings
CoordinatorQueryBalancingFactor -1 1
CoordinatorQueryBoostPriorityLevel 3 0

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.

Advertisements
  1. September 8, 2016 at 18:23

    Thank you for your blog. Great information here.

    I also use ASTrace. Recently, I noticed that even though ASTrace stops randomly. Are you aware of any automated process to interrogate the ASTrace process/log at a specified interval to see if it is running and if it is not, restart the ASTrace service?

    I understand that by design, if SSAS service stops or is restarted, ASTrace will stop auditing so anytime a user restarts/stops Analysis Services, they also will need to restart the ASTrace service. That might be what is happening here, just looking for an automated process to stay on top of things.

    Any advice,tips, tricks would be greatly appreciated.

    • September 9, 2016 at 08:06

      Hi

      Yes I experienced this pain in the early days and use to poll at set intervals to see if the service was up and if not restart it.

      However ASTrace has had some development effort since those days so check and see if your running the latest build and if not then download it because ASTrace will now check to see if the reader/writer is open and if not it will log an error and reconnect to SSAS or DBengine as appropriate. This enhancement also handles your scenario for restarting SSAS and might cover the stop depending on how long the service is down as it uses a x retries with x minutes between logic.

      I also introduced the ability for ASTrace to connect to multiple instances in the newer version 🙂

      Regards

      Andrew

  2. September 9, 2016 at 14:01

    Thanks Andrew. I will verify that I am on the latest version of ASTrace and go from there. Appreciate the insight!

  1. October 9, 2012 at 15:19

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: