Farmville On Blackberry?

Freeze SA: Competition between Processing and MDX

Here is a recurrent problem of freezes found an instance SSAS 2005 which I faced a number of times (attention, this problem has not been found on 2008).

I begin by saying that the freeze AS may be due to a multitude of sources I use here only one of them clearly identified.

You So SSAS an instance that runs well until you notice that:

• You have a processing operation in progress on one of your bases that do not end
• Some of your users are complaining because they can no longer get their data cube
• Any new connection to the proceeding is impossible, however, connections established before the freeze remain active, but as seen above, some queries no longer passes

Those are the basic symptoms. Now look at what is happening on our monitoring tools (by Performance Monitor and SQL Profiler)

There is a transaction on the Profiler is being canceled by AS: Why? Because a processing is about to end and tries to cancel the outstanding requests that use the files that this processing attempts to modify (see ForceCommitTimeout).

On Perfmon, we can observe that y 'a problem of CPU usage (plateau from 1:40 p.m. generated by the non release of resources by the current processing and cancellation of requests). Besides, if we control the value of the number of thread given to AS, we note that changes little or not at all.

Notes: Green Curve: Using server CPU / purple curve: CPU Usage by AS
Ordinate:% of use
Abscissa: Time

The almost complete blocking the body is due to two factors:

• A transaction processing current
• A query whose cancellation time is extremely long: in our case, when calculating cells NON EMPTY the rollback takes a long time. This behavior was changed with the advent of 2008 and Subspace Computation (Computation or Block). That is why for the moment this problem has been observed that under 2005

Why new connections to the proceeding can not succeed? Simply because ASA does not establish new connections / queries when processing a transaction is in its phase commit . It places them in its queue. If you find yourself in this situation, there are two solutions:

• Wait actual cancellation of the request
• Or restart the instance AS

The fact is that these two proposals are not all satisfactory. Wait several minutes / hours, or restart the instance. Fortunately, there are ways to circumvent this problem:

• The use of NON EMPTY to BEHAVIOUR optimization orders NON EMPTY (attention to its use in 2008)
• Rewriting the MDX (not always possible, depending on the client tool)
• The transition to 2008 (and yes)

Some documents :

Subspace / Block Computing:
SSAS 2008 Performance Guide , paragraph 2.3.2.1

NON EMPTY behavior:
Blog Marco Russo
SSAS Unleashed, Chapter 12, P196-197, Chapter 29 p 541