PROBLEM STATEMENT

When attempting to create a set in the FIM Portal with multiple negative conditions.  For example:

AccountName is not "1" +

AccountName is not "2" +

AccountName is not "3" ... etc.

When attempting to create one of these sets we get the below PostProcessing Error:

"The server was unwilling to perform the requested operation"





Application Event Log

The Application event log shows MSSQLServer throwing EventID 8623

"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.  Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

More information:

Per FIM best practices (http://technet.microsoft.com/en-us/library/ff608274(v=WS.10).aspx) the use of negative conditions is discouraged, likely due to the expensiveness of queries based on multiple negative conditions. 

Minimize the use of negative conditions

Negative conditions are the membership conditions that make use of the following operators or functions: !=, not(), < , <=. To optimize for performance, where possible, express the condition that you want with multiple positive conditions rather than as a negative condition.

In testing, I have found this to happen when more than 8 negative conditions are combined, or a combination between 8 negative and one or more positive conditions. It appears to happen in various versions of FIM 2010 and FIM 2010 R2, including the latest versions (4.0.3644.2 and 4.1.2548), as well as FIM 2010 R2 SP1.  This problem also affects criteria-based groups.

FIM_TemporalEventsJob fails:  Additionally, I have seen the FIM_TemporalEventsJob fail on the FIM_MaintainSetsJob with the same event ID 8623 error when trying to process sets that have been created with more than 8 negative conditions.  We are still trying to determine how these sets were created in the first place, but initial research seems this may be somewhat dependent on the version of FIM.

 

Workaround:

Let's say we run into this issue and we can't create a criteria-based group with the desired membership.  What do we do?
Create a set called "_ExcludedAccounts" and use positive criteria:
AccountName is  "1" or
AccountName is "2" or
AccountName is "3" ... etc.

Then simply modify your initial set to be All Users with ResourceID not in "_ExcludedAccounts".