Wednesday, April 20, 2005

Analysis Services Locks

In a high stress environment where you are trying to process a partition whilst your users are actively querying the cube you may have reached the situation where Analysis Services appears to lockup and no queries are resolved, and connections are not honoured.

This can be caused by the fact that AS is waiting for the reader to finish its query before the writer (in this case AS itself) is able to commit the changes which have been created by the partition processing.

Depending on the nature of your application, holding up the entire system for one user to complete his/her query may not be optimal.

If you think you are suffering from this issue you can easily check this by keeping a close watch on the lock waits counter of AS, you will find that this will be increasing and not returning to 0 quickly (in our situation we found it stuck at around 50 for extended periods of time), you may also find the connections in progress counter creeping up and up as your users are caught in limbo .

Luckily there is now a great KB article which covers this issue and provides a neat fix.