Error message “The query processor ran out of stack space during query optimization. Please simplify the query.”


You may have faced problem like this before also, when you were trying to delete a single/multiple record from a table. But have you got the solution for it?

Here is the problem I faced recently with SQL Server 2005 SP2 and the way I handled it after spending enough time on reading MSDN, various articles?

Scenario:

We have one table called “User” which is referenced by nearby 500 tables as well as self-referenced. Now when I tried to run a simple DELETE statement with primary key ID, I keep getting the stack overflow error (as mentioned in the Title). I was completely blank after reading MSDN, articles; I got no resolution anywhere.

Here are few reasons I have pointed out that could be the possibilities for this problem…

  1. Recursion during query optimization. Ref: http://support.microsoft.com/kb/945896
  2. The problem lies in number of Foreign Keys. In this scenario, the user table is referenced by many tables.Ref: http://www.sqldev.org/sql-server-database-engine/internal-query-processor-error-server-msg-8621-96775.shtml
  3. In some extreme situations stack size may become limit for given query. Ref: http://msdn.microsoft.com/en-us/library/aa226411(SQL.80).aspx

I used the traditional way to solve this problem. It could not be the correct way to resolve this, we can discuss on this as well as if I ‘get any resolution I’ll post it here.

Solution:      

  1. Disable all the referencing Foreign Keys. You can use the custom script or the below script. In below you will need to update the referenced entity.
    DECLARE @FKName			VARCHAR(255)
    DECLARE @MainEntity		VARCHAR(255)
    DECLARE @SQLString			VARCHAR(4000)
    
    DECLARE cur_FK CURSOR FOR
    	SELECT	name FKName, OBJECT_NAME(parent_object_id) MainEntity
    	FROM	sys.foreign_keys
    	WHERE	OBJECT_NAME(referenced_object_id) = 'User'
    OPEN cur_FK
    FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    	SET @SQLString = 'ALTER TABLE ['+ @MainEntity + '] CHECK CONSTRAINT [' + @FKName +']'
    
    	--PRINT @SQLString
    	EXEC (@SQLString)
    
    FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity
    END
    CLOSE cur_FK
    DEALLOCATE cur_FK
  2. Run the Delete statement.
  3. Enable all the Foreign Keys. You can use the same script mentioned above just change the “NOCHECK” keyword to “CHECK”.

I hope this will help you all. Share your ideas to handle this problem.

Advertisements

3 thoughts on “Error message “The query processor ran out of stack space during query optimization. Please simplify the query.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s