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.

http://support.microsoft.com/default.aspx/kb/872934

Wednesday, April 06, 2005

Analysis Services Network Performance Part III

Analysis Services Network Performance Part III

Many people have raised a few good points in reply to my last positing. The consensus is that many people would assume that naturally verbose XMLA would be a slower solution than the binary connection protocol that PTS uses.

In fact if you try to connect with the uncompressed XMLA stream that will be the case, IIS compression reduces the network traffic by around 80-90% due in part to the repetitive nature of XMLA.

However for me the key difference is more fundamental, it is the model of connection and querying that is different.

We treat the server almost like a web service in that we submit a query and then get back a response with pretty much what we asked for. This is wildly different to PTS which will return what it thinks you asked for, plus a load of other stuff.

My understanding is that PTS on the client will take your MDX query and break this down into a number of queries; it then gets the results of these queries and then passes them back to the client tool.

In this process you may find that the data shipped back is at a lower level that you requested in your query, or contains more members than you asked for because PTS is trying to be smart about caching on the client.

This model makes sense on a LAN because you have a large amount of bandwidth, however on a WAN this model gets messy real quick. What the XMLA SDK does is give us the opportunity to do the PTS bit back on the server and then just ship back the data that we asked for in the first place, this gives you a much thinner way of providing your users with the service that they require.

If you want some more specifics in info of report times vs location etc please contact me