SQL Server Performance Forum – Threads Archive

The query processor ran out of internal resources

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

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.

What about the SP1 CTP release which was released recently —————————————-
http://spaces.msn.com/members/dineshasanka

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

Yes I applied it. Still the same error —————————————-
http://spaces.msn.com/members/dineshasanka

Does anyone has an idea of next release of SP1 for SQL Server 2005 —————————————-
http://spaces.msn.com/members/dineshasanka

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.

Yes I did run UPDATE STATISTICS WITH FULLSCAN —————————————-
http://spaces.msn.com/members/dineshasanka

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.

I applied SP1 but still the problem exists —————————————-
http://dineshasanka.blogspot.com/

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.

Please satya check whether MVP newsgroup has any idea. —————————————-
http://dineshasanka.blogspot.com/

Couldn’t tell you. I ran this
sp_MSForEachTable ‘Update Statistics ? WITH FULLSCAN’ —————————————-
http://dineshasanka.blogspot.com/

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.

Thanks satya,
Anyway , I have divided one SP to four SPs for the moment I can manage now —————————————-
http://dineshasanka.blogspot.com/

]]>