Work Item Rules Validation Exceeds SQL Limits

Question

I am getting a very problematic error attempting to save any kind of update to any work item in a Project (adding a new wi or updating an existing one).  The only error report I find is on the Server's app log where it says:

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."

I look this up on MSDN and it says this refers to a SQL limit of 65,535 "identifiers and constants" in a sql query (http://support.microsoft.com/kb/913050).

The only cross-reference I can find for this error and TFS is here:

http://social.msdn.microsoft.com/Forums/en-US/tfsworkitemtracking/thread/cb8f5b3c-b63d-41be-86e2-b48795bc213c/

After reading this, I have a question about the statment "Basically when you save your work item, we validate all the rules associated with the fields for that work item type.  Under rare conditions the rule validation expression can become too complex for SQL to evaluate."

After trying a number of other things, I came to the conclusion that I had to take seriously the possibility that SQL was receiving an expression from TFS that did indeed exceed this limit.  However, the only way it would even come close is if I assumed that TFS created an "Rules Validation" query encompassing ALL the Work Items on our server across all Projects - We  have about 35 Work Items, all uploaded to 13 Team Projects and with an average of about 100 Fields each and we are rapidly expanding the complexity of the Fields and Work Flows (lots of When, Copy, and other qualifiers, etc.).  If I add up all of these and assume they are part of one big query, I guess we could indeed surpase 65K identifiers in sql.

I have no idea what TFS is doing as part of it's "rules validation", and I find this implausible.  However, what I experimented with was removing about 12 or so Work Item definitions (using tfpt destroywitd) from a single Project, and then attempted a save on the last WIT I was trying to upload that started this whole mess.  It saved just fine.  Then I readded the previously removed WITs, tried again to save ANY work item, and got the same SQL error.

So.....could someone please explain what is going on here?  Have we indeed exceed another TFS limitation, one that is apparently not documented?  Is there a way to actually calculate when this limit is reached, etc.?  We have already reached the limit of the maximum number of Fields defined in TFS (about 1000) and I have been working to reduce that number using global fields (we are down to about 750 or so now). 

Any ideas are welcome.

Answer 1

Hi,
    I am a Dev on the TFS team. What is going on here is that we have a single expression per Project that we use to validate Workitems being created/updated in that Project. This expression grows with the number of rules you have in all Workitems in the Project. Pretty much every behavioral qualifier you put on a field (Say the VALIDUSER tag or an ALLOWEDVALUES entry) results in a Rule being created and increases the number of sub epxressions in that expression. eg. An ALLOWEDVALUES tag added to Field F1 in workitemtype Bug in project P1 would create a subexpression for Project P1 like "If  WorkItemType=Bug , then F1 should be a member of some set S1". This Rule is then validated every time you insert/update a workitem in This subexpression gets added to the larger expression for project P1. Notice that though you added one rule to a field, the number of identifiers added to the expression was > 1. Nested rules (things which apply only on a transition or when the value of some other field is a certain value will cause more conditions to be added to the IF statement.

Once that expression reaches a certain size/complexity, SQL cannot evaluate it any more and gives you the error that you are seeing. When you remove some WITs from a project, you make the expression for the project simpler and SQL is able to interpret it, so you can save workitems into that project. This is a limitation of the TFS system. There is no simple workaround for this, other than to reduce either the number or the complexity of the WorkItemTypes in a Project. 

It is difficult to document this in a straightforward way because this a combination of not just the number of fields, but also of what rules you have for those fields, etc. I can try and work with our User Ed team to see if this is something we can explain simply in the future.

Thanks,
Amit
Dev, TFS

Answer 2

AGhosh,

Thanks very much.  This is pretty much the conclusion we were guessing at.  I do have one question.  We get this error only after the last WIT update has been made and we attempt to save a Work Item.  The WIT that "causes" the problem passes validation during the upload process (I typically use the command line witimport tool) so you'd not know there is a problem until someone attempts the first work item update.  So, does TFS perform a single WIT validation on the upload, and only the collective, Project-wide validation during an update?  If so, what is the problem with validating WITs singly during WI updates, like it does during template imports?

Thanks.

Answer 3

>> does TFS perform a single WIT validation on the upload, and only the collective, Project-wide validation during an update?  If so, what is the problem with validating WITs singly during WI updates, like it does during template imports?
Not quite. Just to be clear, TFS does an individual validation of the Workitemtype (WIT) to make sure it is creating a valid set of rules. This is an Admin action (via Witimport/witadmin).

What happens when you save a workitem is  a WorkItem (WI) validation using the rules specified for the WorkItemType. It is an implementation detail of TFS that all the rules of all WorkItemTypes in a Project are combined into a single expression when evaluating a WorkItem insert/update. It is the WorkItem evaluation which is failing for you.

One thing which TFS could do would be do a dummy WI creation after importing a WIT before commiting changes to make sure we do not commit the WIT changes if we are going to hit this isssue. I will file a suggestion for this iternally.

One suggestion would be to find out whether you indeed do need 35 WITs in every project. If your system has been in operation for a while, you could Query against the WorkitemLatestUsed view on the TFSWorkitemtracking DB and do some count(*)/group by System.ProjectID, System.WorkItemType queries to figure out how may workitems of each workitemtype are being actively used on each project. That might tell you which WITs you can safely remove from which Project.

Thanks,
Amit
Dev, TFS

Answer 4

AGhosh,

We have a test Project on our server so we always upload changes there first and see how they work before going forward with the "real" projects.   What might be more helpful is to expose a tool or something that we can use to know when we break this limit, something that tells us the size of our WIT validation expression (or maybe just a rough formula).

As for the # of WIs, the first thing I did was to find out which ones were not used at all (about a dozen of them) and have gotten approval to remove these.  However, the remaining ones are in the process of further definition and so will become much more complex.  My customer is aware of the issue and they now know to bear this in mind as they define what they need in the system. 

Thanks.

Answer 5

BTW - Eugene has put together a great blog post of WIT complexity limitations that includes this particular issue:  http://blogs.msdn.com/eugenez/archive/2009/05/07/work-item-customization-tidbits-limits-of-complexity-part-13-of-x.aspx

---

Ed Blankenship, http://www.edsquared.com, Microsoft MVP (Team System)

Answer 6

I forgot about this one.  Turns out the TFS team knows about it and has decided to fix this for the Beta 2 release of TFS 2010.  I got that from an engineer while doing a support ticket for this issue.