strange error: ran out of internal resources

Dear Experts,

While running a query with a lot of joins, I got the following error
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.
------------------------------------------------------------ -------

The really strange part is that if I left the clause "ORDER BY myDate"
off the query I didn't get the error. My work around was to put the
results of the query into a temporary table called #myTemp and then do
"SELECT * FROM #myTemp ORDER BY myDate".

1. Is there a way to tell the query processor to use more resources so
I don't get this error?

2. Since it seems like the ORDER BY clause is causing the problem, is
there a way to tell the query analyzer to just do the query and then
order everything instead of trying to be too clever?

3. Is there a better way to solve this problem than using a temporaray
table?

Thanks,
-Emin

Re: strange error: ran out of internal resources

Emin (emin.shopper@gmail.com) writes:
> The really strange part is that if I left the clause "ORDER BY myDate"
> off the query I didn't get the error. My work around was to put the
> results of the query into a temporary table called #myTemp and then do
> "SELECT * FROM #myTemp ORDER BY myDate".
>
> 1. Is there a way to tell the query processor to use more resources so
> I don't get this error?

No. Possibly more memory to SQL Server could help.

> 2. Since it seems like the ORDER BY clause is causing the problem, is
> there a way to tell the query analyzer to just do the query and then
> order everything instead of trying to be too clever?

Yes, leave out the ORDER BY clause. If you leave it in, the query
processor has to comply. Or tell you that you can't. It can't produce
an unordered result when you requested an ordered one. That would
be a bug.

> 3. Is there a better way to solve this problem than using a temporaray
> table?

Without know nothing about the tables or the query, I cannot propose any
alternatives. A temp table seems like an easy way out. But it's possible
that the query itself could be simplified.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: strange error: ran out of internal resources

On Sep 12, 6:03 pm, Erland Sommarskog wrote:
> Emin (emin.shop...@gmail.com) writes:
> > The really strange part is that if I left the clause "ORDER BY myDate"
> > off the query I didn't get the error. My work around was to put the
> > results of the query into a temporary table called #myTemp and then do
> > "SELECT * FROM #myTemp ORDER BY myDate".
>
> > 1. Is there a way to tell the query processor to use more resources so
> > I don't get this error?
>
> No. Possibly more memory to SQL Server could help.

The maximum memory is currently set to 2 GB. Is there some special
memory setting for the query analyzer?

> > 2. Since it seems like the ORDER BY clause is causing the problem, is
> > there a way to tell the query analyzer to just do the query and then
> > order everything instead of trying to be too clever?
>
> Yes, leave out the ORDER BY clause. If you leave it in, the query
> processor has to comply. Or tell you that you can't. It can't produce
> an unordered result when you requested an ordered one. That would
> be a bug.

What I meant was, is there a way to tell the query analyzer to first
produce an unordered result and then to sort it? My guess is that the
query analyzer is trying to keep things in sorted order during the
processing and this is what makes it run out of resources. The only
thing the temp table is doing is forcing the query analyzer to break
things into these two steps (1) do the query (2) then sort the
results. It seems like there should be a way to tell the query
analyzer this more directly.

Thank you very much for your answers.

Sincerely,
-Emin Martinian

Re: strange error: ran out of internal resources

Emin (emin.shopper@gmail.com) writes:
>> No. Possibly more memory to SQL Server could help.
>
> The maximum memory is currently set to 2 GB. Is there some special
> memory setting for the query analyzer?

First of all, it's the Query Processor. Query Analyzer is a GUI tool
that shipped with SQL 7 and SQL 2000.

No, there is not any such setting.

> What I meant was, is there a way to tell the query analyzer to first
> produce an unordered result and then to sort it? My guess is that the
> query analyzer is trying to keep things in sorted order during the
> processing and this is what makes it run out of resources. The only
> thing the temp table is doing is forcing the query analyzer to break
> things into these two steps (1) do the query (2) then sort the
> results. It seems like there should be a way to tell the query
> analyzer this more directly.

Look at the error message again:

The query processor ran out of internal resources and could not
produce a query plan.

It's not when running the query the Query Processor hits the ceiling,
but when trying to find out *how* to run the query. It has not
accessed any data at this point. When you remove the ORDER BY clause,
you make the query less complex, and the query processor is able to
build the plan. Why I cannot tell. It could be that the ORDER BY
clause simply is the straw the breaks the camel's back. It could
also be that the ORDER BY clause triggers a bug that causes the
query processor to go round in circles. Or something in between.

It's possible that you could help the optimizer by adding some query
hints, but I would not really hold my breath.

I think your workaround of bouncing the data over a temp table is a
good one.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx