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.

Graphical view of “Logical Query Processing”

A nice article by Pinal Dave on “Logical Query Processing”. This article explains about the different phases of statement execution.

Article Link http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Download Logical Query Process

SQL Server Agent service stopped because audit fails

I was stunned when I saw SQL Agent service is stopped unexpectedly on my SQL Server 2008 ENT. edition and there were no much informational message in SQL Server Error log (“SQLServerAgent initiating shutdown following MSSQLSERVER shutdown). But when I checked the Windows Error Log I got the below message…

“An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the “-f” switch, and using SP_CONFIGURE.”

For solutions, I surfed through MSDN, blogs, articles etc. and I got the below answers or the suggestion…

  1. Start perfmon and check for drive where Sql server writes the Log file check drive’s wait times. if your drive is too busy, it cannot keep up with writing the audit trace file, hence Sql server will have doubts the audit trace records all info, and so shut itself
  2. If you run any kind of server side trace with auto shutdown option, and for whatever reason trace cannot be written to file Sql server shuts down.

Configuration about my SQL Server environment…

  1. C2 Audit mode is not configured on the server
  2. Agent service is running under Service account.

Let’s have your comments…

References:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/eb94db75-fed1-4a99-b321-4a931de402ed

http://www.eggheadcafe.com/software/aspnet/33958815/sql2005-sp2-stopeed-becau.aspx

Index de-fragmentation with SQL Server

Here is the informational video by Kimberly L. Tripp for beginners as well as those who want to understand Index de-fragmentation more. In this video Kimberly explain about…

  • How Fragmentation occurs?
  • What Fragmentation means?
  • How to see and minimize fragmentation?
  • Automating the index Rebuilds etc.

Video Link = http://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=30

Introduction to SQL Server 2008 Backup Compression

Microsoft has introduced a new concept “Compression” in SQL Server 2008. Backup Compression is a new feature of SQL Server 2008 which significantly reduces the backup file size and speed up the backup and restore process. By default, this feature is turn off when you install the SQL Server 2008. Backup compression uses a Compression algorithm to compress the data stored on backup media. You can enable the backup compression using sp_configure stored procedure or by GUI.

USE master
GO 
EXEsp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE 

You can do so by setting the server-instance properties by GUI (SSMS).

Specifying for the backup to be compressed is really simple. Just specify COMPRESSION in the WITH clause of the BACKUP command. Let’s see the difference between the backup file size with and without specifying the COMPRESSION. I have tested this scenario on my local machine on AdventureWorksLT2008 database.

Uncompressed backup

BACKUP DATABASE AdventureWorksLT2008 
TO DISK = 'C:\Backup\AdventureWorksLT2008_Uncompressed.bak' 
WITH INIT

------------------------------
Processed 824 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Data' on file 1.
Processed 4 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Log' on file 1.
BACKUP DATABASE successfully processed 828 pages in 1.486 seconds (4.351 MB/sec).

Compressed backup

BACKUP DATABASE AdventureWorksLT2008 
TO DISK = 'C:\Praxair_Shared\AdventureWorksLT2008_Compressed.bak' 
WITH INIT, COMPRESSION

------------------------------
Processed 824 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Data' on file 1.
Processed 1 pages for database 'AdventureWorksLT2008', file 'AdventureWorksLT2008_Log' on file 1.
BACKUP DATABASE successfully processed 825 pages in 0.605 seconds (10.653 MB/sec).

The Uncompressed Backup process took 1.486 sec to complete the backup process and created 7.08 MB backup file, whereas the Compressed Backup process took just 0.605 sec and created 1.27 MB backup file.

For more information on Backup Compression you can navigate through different blogs or can refer the MSDN on Microsoft… http://msdn.microsoft.com/en-us/library/bb964719.aspx.

Working with comma separated string using XML

SQL DBAs and Database Developers often require parsing or splitting the comma separated string into rows. I guess, you might have already written or used any Split FUNCTION in your database. Here, I would like to demonstrate a different technique to split the delimited string; I used this technique while working with LINQ technology.


IF OBJECT_ID('Table1') IS NOT NULL
      DROP TABLE Table1
GO

CREATE TABLE Table1(KeyID INT)
GO

INSERT Table1
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
GO

DECLARE @p2 VARCHAR(100)
SET @p2= '1,3'

SELECT      *
FROM  Table1
WHERE KeyID IN (
            SELECT  Split.PId
            FROM  (SELECT CAST('<r>' + REPLACE(@p2, ',', '</r><r>') + '</r>' AS XML) AS P ) xmlP
            CROSS APPLY (SELECT P.PId.value('.', 'varchar(10)') PId FROM xmlP.P.nodes('r') AS P(PId) ) Split
)
GO

Restore database from SQL Server 2008 to SQL Server 2005

We have recently upgraded to SQL Server 2008, some application still pointing to SQL Server 2005. I wrote this article just for FYI… HOW to restore SQL Server 2008 to SQL Server 2005. The same situation I face in my daily work. Backing up databases in SQL Server 2008 and then restoring them into SQL Server 2005 is not directly possible. The database backups are not backward compatible in SQL Server 2008.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/32a67469-0636-4ded-923f-2e257f7e68e1/