x

Query optimization - Please help

While executing a stored procedure, I am getting this error message,

Msg 8621, Level 17, State 2, Line 1 The query processor ran out of stack space during query optimization. Please simplify the query.
more ▼

asked Jan 27 '12 at 10:40 PM in Default

Bins gravatar image

Bins
68 3 5 7

Could you post the query?
Jan 28 '12 at 01:21 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

This is a rare internal error (the optimizer contains logic to avoid stack problems in the most common cases). Related errors can occur as a result of having too many EXISTS clauses at the same level, for example. In this case it is likely you have too many nested subqueries, UNIONs or something of that nature.

In any case, the error message is pretty specific about the problem and what you should do about it. Encountering this sort of error is usually a pretty reliable sign that your query is too complex - probably for humans as well as optimizers. Find a way to break the logic into simpler stages.
more ▼

answered Jan 28 '12 at 02:26 AM

SQL Kiwi gravatar image

SQL Kiwi
1.1k 1 4

I always like the catastrophic failure errors with malformed merge queries
Jan 28 '12 at 02:58 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x673
x562

asked: Jan 27 '12 at 10:40 PM

Seen: 1044 times

Last Updated: Jan 27 '12 at 10:40 PM