msg 8621: the query processor ran out of stack space during query optimization

Jamie Thomson

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 am 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 is a huge problem for me. Any advice about how to progress would be welcomed.

-Jamie

Jamie Thomson

anyone

Robert Varga

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 am sure you've considered anyway.

Cheers

Rob

Umachandar Jayachandran - MS


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

Jamie Thomson

Hi guys,

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

 

 

-Jamie

 

 

Jamie Thomson

Jamie Thomson writes:

Hi guys, I sure can. I will 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 have uploaded a DB with data in it) so oly MSFT can see it.

-Jamie

Umachandar Jayachandran - MS


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.

Chris Winland


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

Umachandar Jayachandran - MS


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

Jamie Thomson

Umachandar Jayachandran - MS writes:

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

Umachandar Jayachandran - MS

Edmund P


Was there ever a workaround or a resolution on this

Umachandar Jayachandran - MS


There is no workaround other than simplifying your schema to reduce the number of FK references.