none
Criteria-Based Distribution Group Limitations

    Question

  • Is anyone aware of any documentation from MS around the limitations of criteria-based groups and/or sets?  I'm in the process of transitioning several hundred Exchange Dynamic Lists into criteria-based groups within the FIM portal.  I'm using Powershell to create the groups directly into the portal and that process has gone very well thus far.  The problem comes in when the nightly FIM maintenance job(s) run, it often bombs on a particular group, with the SQL error below.  The group appears in the portal and gets exported to AD ok, I can even go into the portal and view it's members without error.  When I try to modify the criteria, however, I get "Failed to process the request:  Unknown Error" that occurs during submission.  Often times I can delete and recreate the group with one or two less exclusions and I'm back in business.  I'm hoping that a document or recommendations exist so I can apply that prior to running my PS scripts.

    SQL ERROR:

     

    Date                 11/23/2011 1:00:24 AM

    Log                  Job History (FIM_TemporalEventsJob)

     

    Step ID             3

    Server               ServerName

    Job Name                     FIM_TemporalEventsJob

    Step Name                    FIM_MaintainGroupsStep

    Duration                        00:01:08

    Sql Severity                  16

    Sql Message ID                        50000

    Operator Emailed                     

    Operator Net sent                     

    Operator Paged                       

    Retries Attempted                     0

     

    Message

    Executed as user: Domain\SQLAdmin. Reraised Error 50000, Level 16, State 1, Procedure MaintainSets, Line 373, Message: <Failures><_x0040_failedSetCorrections SetKey="97118" ErrorMessage="Reraised Error 8623, Level 16, State 1, Procedure ?, Line 2, Message: 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."/><_x0040_failedSetCorrections SetKey="97126" ErrorMessage="Reraised Error 8623, Level 16, State 1, Procedure ?, Line 2, Message: 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."/></Failures> [SQLSTATE 42000] (Error 50000).  The step failed.

     

     

    Thanks,

     

    Jeff

     

    Wednesday, November 23, 2011 3:02 PM

Answers

  • Like Bob, I have run into this limitation as well.  I'm not sure this will help you in your situation, but it may help others that aren't running nightly queries. 

    What I normally do when I have more than 5 exclusions is to create one or even two sets with part of those exclusions, then reference those sets in the ultimate member set. i.e.  Resource ID not in "partial exclusion set".  This way the final set membership only has 1 or 2 exclusions inside it. 

    I believe this won't help you in this instance because the query is still running into complexity, but it may simply because you have reduced complexity per set....if it does, let us know....assuming the indexing isn't the cause.

    • Marked as answer by Jeff_Craig Monday, November 28, 2011 8:05 PM
    Monday, November 28, 2011 2:19 PM

All replies

  • It sounds to me like the problems you are having are to do with either the criteria complexity, or degradation of your full text catalog on the FIM db.  Since the latter is easiest to deal with, first have a look at setting up this and after running for a few days see if your failures go away.  If not, can you then post an example of the set/group xml filter you are using which is problematic?
    Bob Bradley (FIMBob @ http://thefimteam.com/) ... now using Event Broker 3.0 @ http://www.fimeventbroker.com/ for just-in-time delivery of FIM 2010 policy via the sync engine
    Friday, November 25, 2011 11:43 PM
  • What's the criteria on the group that fails?
    My Book - Active Directory, 4th Edition
    My Blog - www.briandesmond.com
    Saturday, November 26, 2011 7:41 PM
    Moderator
  • Thanks for the response guys.  Bob, I'll give the indexing a shot.  The complexity of the groups I'm building is primarily related to certain security requirements that I've been given relating to 'Job Title'.  For example, none of the groups can contain employees that are contractors, vendors, temporary, etc.  In all, there are 5 titles that I must exclude.  Employees must also be active and company must match.  In other words, before I even get started, we have 7 exclusions to work with.  Is it unrealistic to think that FIM can accomplish what we need?  As I mentioned, I can create the group and even view it's membership.  It's the SQL job that bombs as well as the fact that I am unable to later modify the group that indicates there's an issue.

    Brian, it doesn't appear to fail on one particular criteria, but it does seem to succeed when I remove one or more of the exclusions I've referenced above.

     

    Thanks again,

     

    Jeff

    Monday, November 28, 2011 1:34 PM
  • I've run into something similar before ... too many clauses in a set definition to even save the definition.  One approach to try is to use a sync rule (assuming you import this info from somewhere) and translate your various values (e.g. using IIF statements ... cumbersome but they work) into say a new boolean attribute binding of person ... then define your set criteria on that.  Still ... try the indexing first.
    Bob Bradley (FIMBob @ http://thefimteam.com/) ... now using Event Broker 3.0 @ http://www.fimeventbroker.com/ for just-in-time delivery of FIM 2010 policy via the sync engine
    Monday, November 28, 2011 1:52 PM
  • Like Bob, I have run into this limitation as well.  I'm not sure this will help you in your situation, but it may help others that aren't running nightly queries. 

    What I normally do when I have more than 5 exclusions is to create one or even two sets with part of those exclusions, then reference those sets in the ultimate member set. i.e.  Resource ID not in "partial exclusion set".  This way the final set membership only has 1 or 2 exclusions inside it. 

    I believe this won't help you in this instance because the query is still running into complexity, but it may simply because you have reduced complexity per set....if it does, let us know....assuming the indexing isn't the cause.

    • Marked as answer by Jeff_Craig Monday, November 28, 2011 8:05 PM
    Monday, November 28, 2011 2:19 PM
  • Bob,

    I tried indexing but unfortunately that didn't seem to do the trick.  Either way, It's brought some best practice items to light that I didn't have implemented, so thanks.

     

    Gdtilghman,

    I followed your idea and created a set with all of my exclusions.  Worked like a champ!  It's pretty hokey that I have to do it this way but I greatly appreciate the idea.  I'll run with it for now, until MS can address this down the road (hopefully).   If you've found this practice to be prohibitive in anything else please let me know.

     

    Thanks again gents.

    Monday, November 28, 2011 8:05 PM
  • Yep - I have used that approach myself, and can vouch for it, although I agree it's ugly to have to do it and should be unnecessary.
    Bob Bradley (FIMBob @ http://thefimteam.com/) ... now using Event Broker 3.0 @ http://www.fimeventbroker.com/ for just-in-time delivery of FIM 2010 policy via the sync engine
    Monday, November 28, 2011 9:07 PM
  • Just thought I'd make a note here that regularly running a "rebuild all" on selected FIMService db indexes generally has a positive and immediate impact on FIMService performance issues, including the problem discussed in this thread.  SQL performance remains the single biggest factor in resolving issues of this nature.  Generally speaking do all you can to improve SQL before you start tinkering with timeouts.

    I have found that in some cases extending the timeouts has no effect whatsoever.  Tim Macaulay has since published this article on what to do when this happens, but I have found that there is no real fixed rule about when the error can occur, and have found it to vary from environment to environment with exactly the same filter definition.


    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using FIM Event Broker for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    Sunday, November 24, 2013 2:10 PM