I have always wondered if queries against a SSAS cube while it is processing would
be forced to wait the entire time the cube is processing, or just for a few minutes?
Or would the query bomb out? Surprisingly, I found little information on the internet
about this. Fortunately, with help from SSAS guru’s
Chris Webb and Adam Jorgensen,
they lead me to an
old posting that explained it. Here is my summation:
You can query a cube while it is processing. The worst that could happen is near
the end of processing there is a brief period of time where your query could be
killed or it might be queued, and it would be queued only for a short time, even
if the cube processing takes hours. The reason is the way a cube is processed:
When an cube object is processed a new version of the object is created and this
new version is where the processing actually takes place.
For example, the first time you process a dimension you will see in the dimension
folder files with a name like 1.(All).astore where ’1′ stands for the
version of the dimension. When you process the dimension again you will see files
with version ’2′, so the above file will change to 2.(All).astore.
When the processing is complete a “pending commit” lock is placed on
the object that was being processed (and any dependent objects). If there are any
current queries executing against the object in question they will be allowed to
continue, and any new queries will be queued up until the current version can be
swapped out for the new version. So the only time a cube query could be delayed
is during this swapping, which could be anywhere from a few seconds to a few minutes,
depending of course on the size of the object.
After the commit operation the old set of object files are deleted and Analysis
Server will start to use the new files.
In this example if the dimension was called Reseller, the DDL definition of the
Reseller dimension, which is kept in the Dim Reseller.1.dim.xml file, would be deleted
and you would see a new file created called Dim Reseller.2.dim.xml. If you looked
inside this file, you would see <ObjectVersion>2</ObjectVersion>
There is an SSAS property called
ForceCommitTimeout that specifies what happens to a query that is executing
against the cube when the “pending commit” lock is placed, meaning these
queries are blocking it from completing. ForceCommitTimeout is a signed 32-bit integer
property that defines the timeout, in milliseconds, before a pending commit should
cancel other commands that preceded the current command, including queries in process.
The default value is 30 seconds (30000 milliseconds).
If the ForceCommitTimeout has expired and there are queries still executing they
are cancelled (the receive a “Server: The operation has been cancelled”)
and the old version of the object is swapped out for the new one and any queries
that were queued up are allowed to commence.
This means that increasing the ForceCommitTimeout will give currently executing
queries more time to complete. But it also means that queries that were executed
soon after the “pending commit” lock was taken will be stalled for the
timeout period before they even start to be executed. This will result in the perception
of inconsistent performance as any queries executed during this window might take
nearly twice as long to start producing results.
The “pair” to the ForceCommitTimeout setting is the
CommitTimeout setting, where you could cause the commit of the process operation
to timeout and roll back, allowing currently executing queries to continue to completion.
Seems unlikely you would want to use this as much as the ForceCommitTimeout as you
are basically saying that queries are more important than the processing operation.
The default value for this property is zero (0), which indicates that the server
will not time out commit operations.
This also leads to another question: If you process a cube and the processing bombs,
will the users continue to be able to query the cube? If you fully process a dimension,
such as after making dimension structural changes, but you don’t process the
cube that references that dimension (or the cube bombs when you try to process it),
the cube will be left in an unprocessed state and it will be unavailable for querying.
Other than that particular case, the users will be able to query the cube. So if
you decide to do a full process on the cube and it errors out, you don’t have
to worry that you just made the cube un-accessible.
Republished from James Serra's Blog [70 clicks].
Read the original version here [32134 clicks].