SQL Server Performance

The query processor ran out of internal resources

Discussion in 'SQL Server 2005 General Developer Questions' started by dineshasanka, Apr 5, 2006.

  1. dineshasanka Moderator

    I have a query with seven Unions. this was working happily in SQL Server 2000. When I ran the same query in SQL Serer 2005 I am getting following error.

    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.



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  2. Adriaan New Member

    There was another post with a SQL 2005 bug related to multiple processors, causing extremely bad response times compared to SQL 2000. There is supposed to be a bug fix for that in SP1 for SQL 2005 when it comes out.
  3. dineshasanka Moderator

  4. joechang New Member

    sp1 release candidate is available,
    if you cannot apply it,
    consider getting rid of silly indexes in the tables referenced in the query,
    that has more impact than the join complexity

    also, see if increasing the mem to leave alleviates this
  5. dineshasanka Moderator

  6. dineshasanka Moderator

  7. satya Moderator

    I would agree with Joe's comments here, and without knowing the tables or indexes, it is difficult to say. Did you run UPDATE STATISTICS WITH FULLSCAN when you had restored the database on SQL 2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. dineshasanka Moderator

  9. satya Moderator

    May be a candidate to get to MS Support about the root cause.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. dineshasanka Moderator

    I applied SP1 but still the problem exists

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  11. satya Moderator

    Last but not least raise a call with MS PSS or let me see whether MVP newsgroup has any idea.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  12. dineshasanka Moderator

    Please satya check whether MVP newsgroup has any idea.

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  13. dineshasanka Moderator

    Couldn't tell you. I ran this
    sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  14. satya Moderator

    Private MVP newsgroups are down, I'm unable to login yesterday and will try over the weekend.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  15. dineshasanka Moderator

    Thanks satya,
    Anyway , I have divided one SP to four SPs for the moment I can manage now

    ----------------------------------------
    http://dineshasanka.blogspot.com/

Share This Page