The query processor ran out of stack space during query optimization. Please simplify your query

Question

The query processor ran out of stack space during query optimization. Please simplify your query

Amit Kadam 0

code.txt

Hi All,

I have received below error during executing attached query.

The query processor ran out of stack space during query optimization. Please simplify the query.

It properly worked for @lineCount = 500 but yesterday we received @lineCount = 1200 first time

and it gives the error.

Please help me resolve this issue

Thanks

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-02-22T23:00:22.73+00:00

Since you did not share your query, we do not see your tables, we can only echo the error message: Simplify the query.

What does "SELECT @@version" return?
Amit Kadam 0 Reputation points

2023-02-23T06:57:31.2666667+00:00

File already attached.

Version: Microsoft SQL Server 2016 (SP3) (KB5015371) - 13.0.7016.1 Enterprise Edition (64-bit)

Erland Sommarskog 121.9K MVP Volunteer Moderator

OK, found the file, but what does the resulting query look like? I am not sure that I want to see it, because I have an uncanny feeling that it is a monster. But you can use the stored procedure below to get the text of the full query:

CREATE OR ALTER PROCEDURE SystemPrint @str nvarchar(max) AS
SET XACT_ABORT, NOCOUNT ON;
DECLARE @noofchunks int, 
        @chunkno    int = 0,  
        @pos        int = 0, -- substring start position
        @len        int;     -- substring length

SET @str = replace(@str, nchar(13), '');
SET @noofchunks = ceiling(len(@str) / 4000.0);

WHILE @chunkno < @noofchunks
BEGIN
   SET @len = 4000 - charindex(nchar(10) COLLATE Latin1_General_BIN2,
                               reverse(substring(@str, @pos, 4000)));
   PRINT substring(@str, @pos, @len);
   SET @chunkno += 1;
   SET @pos += @len + 1; -- accumulation + LF
END
go
DECLARE @FullQuery nvarchar(MAX) = @strQuery0 + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4
EXEC SystemPrint @FullQuery

Most likely though, you will need to reconsider your entire approach to what you are doing.

LiHongMSFT-4306 31,571 Reputation points

2023-03-10T08:28:27.5933333+00:00

Hi @Amit Kadam

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,

Cosmog Hong

2 answers

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-02-22T23:00:22.73+00:00

Since you did not share your query, we do not see your tables, we can only echo the error message: Simplify the query.

What does "SELECT @@version" return?
Amit Kadam 0 Reputation points

2023-02-23T06:57:31.2666667+00:00

File already attached.

Version: Microsoft SQL Server 2016 (SP3) (KB5015371) - 13.0.7016.1 Enterprise Edition (64-bit)
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-02-23T08:46:44.0833333+00:00

OK, found the file, but what does the resulting query look like? I am not sure that I want to see it, because I have an uncanny feeling that it is a monster. But you can use the stored procedure below to get the text of the full query:

CREATE OR ALTER PROCEDURE SystemPrint @str nvarchar(max) AS SET XACT_ABORT, NOCOUNT ON; DECLARE @noofchunks int, @chunkno int = 0, @pos int = 0, -- substring start position @len int; -- substring length SET @str = replace(@str, nchar(13), ''); SET @noofchunks = ceiling(len(@str) / 4000.0); WHILE @chunkno < @noofchunks BEGIN SET @len = 4000 - charindex(nchar(10) COLLATE Latin1_General_BIN2, reverse(substring(@str, @pos, 4000))); PRINT substring(@str, @pos, @len); SET @chunkno += 1; SET @pos += @len + 1; -- accumulation + LF END go DECLARE @FullQuery nvarchar(MAX) = @strQuery0 + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4 EXEC SystemPrint @FullQuery

Most likely though, you will need to reconsider your entire approach to what you are doing.
LiHongMSFT-4306 31,571 Reputation points

2023-03-10T08:28:27.5933333+00:00

Hi @Amit Kadam

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,

Cosmog Hong

Answer 1

Guoxiong 8,206

Did you try this:

DECLARE @strQuery VARCHAR(MAX) = ''
SET @strQuery = @strQuery0 + @strQuery1 + @strQuery2 + @strQuery3 + @strQuery4;
EXECUTE(@strQuery);

Ronen Ariely 15,206 Reputation points

2023-02-23T06:50:42.7266667+00:00

hi @Guoxiong ,

This looks like an answer to a totally different question. I remember that question that this is the answer to :-)
Amit Kadam 0 Reputation points

2023-02-23T07:02:07.3766667+00:00

Yes, I tried it but didn't work.

Answer 2

LiHongMSFT-4306 31,571

Hi @Amit Kadam

Simplify the query by breaking the query into multiple queries along the largest dimension.

First remove any query elements that are not really necessary, then try adding a temp table and splitting the query in two.

Merely moving a part of the query to a subquery or function or common table expression is insufficient, since they get recombined by the Transact-SQL compiler.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Amit Kadam 0 Reputation points

2023-02-23T07:04:04.8433333+00:00

Yes @LiHongMSFT-4306 we are trying to break that query into multiple

Share via

The query processor ran out of stack space during query optimization. Please simplify your query

2 answers

Your answer