Msg 8621: The query processor ran out of stack space during query optimization

I got the above error when I tried to execute the following query

delete from BUSINESS_ASSOCIATE
where ba_name is null

As you can see the query isn't exactly complicated.

What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.

So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.

-Jamie

anyone?

|||

Hi Jamie,

I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?

Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.

Cheers

Rob

|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||

Hi guys,

I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..

-Jamie

|||

Jamie Thomson wrote:

Hi guys,

I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..

-Jamie

OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892

Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.

-Jamie

|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||

Umachandar Jayachandran - MS wrote:

This is not related to the memory allocated to SQL Server or any of those settings.

Thanks Uma. I was just about to go away and test that. Good job I checked here first

-Jamie

|||

It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.