SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Here is something interesting that came up a newsgroup thread a little while ago, that I though merited some more explanation.

Have you ever seen "the operation has been cancelled" errors from a long running query (one that takes more than 30 seconds) while a cube or partition is being processed?

What you may be seeing is a  "by design" behaviour, where a pending commit lock will cancel long running queries that are blocking it from completing. There is a property that can be set at both the server and processing command that controls this behaviour called ForceCommitTimeout which defaults to a value of 30 seconds.

Books Online has the following description of ForceCommitTimeout

ForceCommitTimeout

A signed 32-bit integer property that defines the timeout, in milliseconds, before a commit should cancel other commands that preceded the current command, including queries in process.

The default value for this property is zero (0), which indicates that other commands will not be forced to timeout when the current command times out.

As you can see from the screen shot below, this is not entirely accurate. The default value is actually 30 seconds (30000 milliseconds). I'm not sure if this changed with SP2, but this might explain the couple of recent threads on the newsgroup.

In order to understand more about this setting we need to answer the question of what exactly is going on during processing?

  • When an object is processed a new version of the object is created and this new versions is where the processing actually takes place.

Take for instance dimension Reseller. First time you process this dimension you will see in the dimension folder files with names like 1.(All).astore … 1 stands here for the version of the dimension. Once you process the dimension you will see files with version 2 the file above will change to 2.(All).astore. So during commit operation the old set of object files dies and new files then start being used by Analysis Server.

The DDL definition of the Reseller dimension which is kept in the Dim Reseller.1.dim.xml file will go away and you see new file created Dim Reseller.2.dim.xml. If you look inside this file, you will see <ObjectVersion>2</ObjectVersion>

  • When the processing is complete a "pending commit" lock is placed on the object that was being processed (and any dependant objects). If there are any current queries executing against the object in question they will be allowed to continue, any new queries will be queued up until the current version can be swapped out for the new version.
  • If the ForceCommitTimeout has expired and there are queries still executing they are cancelled and the old version 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 produce results.

The "pair" to this setting is the CommitTimeout, where you could cause the commit of the process operation to timeout and roll back, allowing currently executing queries to continue to completion. I can't really see people using this option nearly as much as the ForceCommitTimeout as you are basically saying that Queries are more important than the Processing operation.

(If you are interested the full thread that sparked the idea for this post can be found here: http://forums.microsoft.com/MSDN/showpost.aspx?postid=1416621&siteid=1)

 Thanks to Edward for his assistance with some of the details of this post

Print | posted on Tuesday, April 24, 2007 8:07 AM

Comments on this post

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
CAN YOU PLEASE LET ME KNOW HOW TO CHANGE THE DEFAULT VALUE IN ForceCommitTimeout RIGHT NOW IT IS NOT GETTING CHANGED IF I AM CHANGINGIN IT IN THE VALUE
Left by jim on Nov 25, 2008 3:48 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
Right-clicking on the server in SSMS and going into the properties and changing the value should work. If this does not work for you and it is urgent you should ring Product Support.
Left by Darren Gosbell on Nov 25, 2008 7:24 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
How can I set the CommitTimeout and/or ForceCommitTimeout when processing a cube?

I want to process some Dimensions and Cubes but user queries are blocking the commit phase of the Processing and locking the entire database. So our preferred method would be to 'kill' the user queries blocking the commit and allowing the commit to succeed.

Any help appreciated, thanks
JB
Left by Jonathan on Sep 11, 2009 8:06 PM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
@JB - I believe that these are server settings and that you have to set them at the server level as I outlined in this post
Left by Darren Gosbell on Sep 13, 2009 10:11 PM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
Simple fix but can cause a massive mulitple cascading freakout if not upped to a over 60 seconds
Left by Saved my B*tt -- thanks! on Oct 10, 2009 1:47 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
Yes, you may want to tune this relative to the average response time of your queries. Just keep in mind that any query requests that start after the commit lock has been requested will be stalled until the commit takes place, therefore they could do nothing for 60 seconds until the commit is processed before they can start running. Effectively making the user wait 120 seconds for their query to return.
Left by Darren Gosbell on Oct 10, 2009 10:59 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
"property that can be set at both the server and processing command "

Suggests that there is an option both at the server level and per processing command.

Any Ideas?

Attila
Left by Attila on Oct 16, 2009 12:32 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
@Atttila - I'm not sure I understand the question. Ideas about what?

You can definitely set this option at the server level from the server properties in Management Studio.

And this article http://msdn.microsoft.com/en-us/library/ms186627.aspx lists both properties as properties that can be set in the Properties element of an Xmla command.
Left by Darren Gosbell on Oct 16, 2009 6:51 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
Hi Darren,

My B*tt is not entirely saved at this point. The ForceCommit has helped to a degree but not entirely. The Freakouts continue. They love the cube but it is like an abusive spouse or something... the AS server gets a little irritable (memory) and starts the Excel 2007 Pop ups randomly...

I've noticed that we are facing the same issues in this thread but instead of a Parent-Child Dimension, we have muliple Calcs 30 that seems to trigger the Server Operation Cancelled when these are dropped in a Cross Join. :

http://www.windows-tech.info/15/9f7d5d4e7fee1a5e.php

I guess I'm looking for a list of issues that can trigger this: (e.g.)
1. Hardware
2. Software - Win Server Settings (3GB switch on 32 bit) - Limits on Standard vs. Enterprise.
3. AS Server Settings
4. Dimension Structure
5. Max Cube Size
6. Disk Speed
you name it -
7. missed patches
8. Profiler Traces running
9. Memory Insufficient
10. Triggers for MDX Query Execution going to Cell from Set vice versa
11. Queries will Cube-Dims Processing even on another Cube.
12. AWE Enabled?
13. Big Dimensions -- what memory does it grab? During crossjoins? etc...
14. Big Calcs across 7-8 multi-million (80) row Fact Tables?
15. Is there a rough dead stop limit for Fact Tables for 32 Bit-- (like looking at the Folders and Dim files and the server -- at count of X Dims of XXXX size)


Clearly on a Win 2003-Standard AS on 32 bit with 4 GBs of Ram, there will be issues with Scalability. The problem is after restarting the OLAP Service... the baby is beautiful for a few hours until later in the day when the Caching reaches a limit or the OLAP Service can't create Sessions and then the AS pops the Excel Red Xs causing Freakouts. "Server Operation Cancelled."

Any guidance is greatly appreciated. This can lull a developer... development works ffine... but a user with 20 tabs in Excel hhits Connection refresh and the trouble starts.

Is 32 Bit on AS only for testing and development? It seems to me it is impossible to do any Prod delivery with 32 bit even with AWE. Correct?
Left by Saved my B*tt -- thanks! on Oct 17, 2009 12:40 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
one more:
16. Settings in the msmdsrv.ini which can affect the Memory management for this Error...
Left by Saved my B*tt -- thanks! on Oct 17, 2009 1:31 AM

# re: SSAS: Processing, ForceCommitTimeout and "the operation has been cancelled"

Requesting Gravatar...
Hi Daren,

In xmla proceesing command, I set MaxParallel to 1 since I had Memory allocation failure: Not enough Storage. So I got: Operation has been cancelled error, and I set ForceCommitTimeOut to 0 as no one is quering my cube yet, however the error remained. Appreciating your advice.

Amir.
Left by Amir K. on Jun 16, 2010 12:02 AM

Your comment:

 (will show your gravatar)