|
|
Posted by Erland Sommarskog on 09/13/07 21:45
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|