CommitTimeout and ForceCommitTimeout in SSAS

There has been some confusion over CommitTimeout and ForceCommitTimeout  in the past. Hopefully this will be of some use.

SSAS Processing requires write lock on the cube to commit the process. To acquire the write lock there should be no read lock present on the cube. Read locks are acquired when a MDX query is fired against SSAS cube. COMMITTIMEOUT property will wait till all the read locks are closed before committing. For example, say the commit timeout is 30 seconds and if read locks are not released for commit to happen then rolling back of processing transactions takes place.

FORCECOMMITTIMEOUT property is used to avoid rolling back of processing transactions. FORCECOMMITTIMEOUT will wait the specified time for long running queries (that have acquired read locks) to complete its work before cancelling them (to acquire write lock for processing).

The following is general representation of how locks for processing and querying work.

To summarize:
COMMITTIMEOUT – Cancels processing (after specified time) if write locks cannot be obtained.
FORCECOMMITTIMEOUT – Cancels long running queries (after specified time) to acquire write locks for processing. When these queries are cancelled the user gets an error “Server action is cancelled”.

COMMITTIMEOUT of 0 will never let the processing operation timeout.

COMMITTIMEOUT of 0 and a low value of FORCECOMMITTIMEOUT would mean user queries getting cancelled more often. A high value of FORCECOMMITTIMEOUT would mean user queries (even badly performing queries) get more time to finish its operation and processing operations get delayed as they will have to wait until all the read locks (held by querying operation) are released, thus giving a perception of bad processing performance.

If we set both COMMITTIMEOUT and FORCECOMMITTIMEOUT to zero then write lock on cube will wait indefinitely till all the read locks are released. There will be no errors or timeouts but the system will generally appear to be badly performing. Please note, if the user queries the cube during the commit phase of processing then the queries get queued up.

If you want the processing to take priority the set the COMMITTIMEOUT to 0 and FORCECOMMITTIMEOUT to 500 (default of 3000 is also OK). FORCECOMMITTIMEOUT of 500 will enforce the user queries to be cancelled after 5 seconds.

If querying has to take precedence then set COMMITTIMEOUT to 500 which would enforce the processing operations to be cancelled after 5 seconds of holding the write lock. Setting COMMITTIMEOUT and FORCECOMMITTIMEOUT to 0 would also give precedence to the query but without any timeouts of processing operations. This will generally give an impression of server performing badly.

To avoid such unwanted situations it is best scale out Analysis Services operations by separating the  processing and querying operations.