10

I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of all other variables. As a result during creation I get the error:

Msg 8631, Level 17, State 1, Procedure XXX, Line YYY Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

I figured out that the error is due to the number of variables that I need to use in the SET operation. I can perform the assignment by splitting it in two.

My question is are there some restrictions in this area? I checked, but I did not find any.

We checked the error described in this KB, but this is not our case. We don't use any CASE expressions inside our code. We use that temporary variable to prepare a list of values that have to be replaced using a CLR function. We updated our SQL Server to SP3 CU6 (latest up to date), but we still experience the error.

15

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.

For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:

ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W 
            ScaOp_Identifier @X 
        ScaOp_Identifier @Y 
    ScaOp_Identifier @Z 

Each concatenation element after the first two results in an extra level of nesting in this representation.

The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Stack trace

Repro

DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1; 
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.

The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.

4

Inspired by @Paul's answer, I did some research and found that while it is true that stack space does limit the number of concatenations, and that stack space is a function of available memory and thus varies, the following two points are also true:

  1. there is a way to cram additional concatenations into a single statement, AND
  2. using this method to go beyond the initial stack space limitation, an actual logical limit (that does not appear to vary) can be found

First, I adapted Paul's test code to concatenate strings:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
    DECLARE @S VARCHAR(MAX), @A VARCHAR(MAX) = ''a''; 
    SET @S = @A';

SET @SQL += REPLICATE(CONVERT(NVARCHAR(MAX), N' + @A'), 3312) + N';';

-- SET @S = @A + @A + @A...
SET @SQL += N'SELECT DATALENGTH(@S) AS [Chars In @S];';
EXECUTE (@SQL);

With this test, the highest I could get when running on my not-so-great laptop (only 6 GB of RAM) was:

  • 3311 (returns 3312 total chars) using SQL Server 2017 Express Edition LocalDB (14.0.3006)
  • 3512 (returns 3513 total chars) using SQL Server 2012 Developer Edition SP4 (KB4018073) (11.0.7001)

before getting error 8631.

Next, I tried grouping the concatenations by using parenthesis such that the operation would be concatenating multiple groups of concatenations. For example:

SET @S = (@A + @A + @A + @A) + (@A + @A + @A + @A) + (@A + @A + @A + @A);

Doing that I was able to go well beyond the previous limits of 3312 and 3513 variables. The updated code is:

DECLARE @SQL VARCHAR(MAX), @Chunk VARCHAR(MAX);

SET @SQL = '
    DECLARE @S VARCHAR(MAX), @A VARCHAR(MAX) = ''a''; 
    SET @S = (@A+@A)';

SET @Chunk = ' + (@A' + REPLICATE(CONVERT(VARCHAR(MAX), '+@A'), 42) + ')';

SET @SQL += REPLICATE(CONVERT(VARCHAR(MAX), @Chunk), 762) + ';';

SET @SQL += 'SELECT DATALENGTH(@S) AS [Chars In @S];';

-- PRINT @SQL; -- for debug

-- SET @S = (@A+@A) + (@A + @A...) + ...
EXECUTE (@SQL);

The maximum values (for me) now are to use 42 for the first REPLICATE, thus using 43 variables per group, and then using 762 for the second REPLICATE, thus using 762 groups of 43 variables each. The initial group is hard-coded with two variables.

The output now shows that there are 32,768 characters in the @S variable. If I update the initial group to be (@A+@A+@A) instead of just (@A+@A), then I get the following error:

Msg 8632, Level 17, State 2, Line XXXXX
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Notice that the error number is different than before. It is now: 8632. AND, I have this same limit whether I use my SQL Server 2012 instance or the SQL Server 2017 instance.

It is probably no coincidence that the upper-limit here — 32,768 — is the max capacity of SMALLINT (Int16 in .NET) IF starting at 0 (the max value is 32,767 but arrays in many/most programming languages are 0-based).

0

Now, this is simply Out of memory in other words, as the operation of stored procedure done in memory and the available hardware transistors or Virtual Page Memory available to SQL is full!

So its basically Stack Overflow in SQL Server.

Now, first try to simplify the process, as We know you need 1609 Variables,

But Do you need all variables at the same time?

We can declare and use variables where needed.

For Example:

Declare @var1 int, @Var2 int @Var3 int, .... , @var1000 int; -- Here assume Thousand Variables are declared

Declare @Tot Int;
SET @Tot = 0;
if(True)
Begin
    SET @TOT = @TOT+ VAR1 + VAR2 + .... + VAR1000; -- This might fail; 
End

But if we try this in a loop by adding

Declare @Tot Int;
SET @Tot = 0;
DECLARE @i int, @Count int;
SET @i = 1;
SET @Count = 1609;
WHILE (@i <= @Count)
BEGIN
   DECLARE @SQL NVARCHAR(128);
   SET @SQL = 'SET @TOT = @TOT+ VAR'+ cast(@i as nvarchar);
   EXEC (@SQL);
   SET @i = @i + 1;
END

Note: This will use more CPU, and take bit more time in calculation.

Now this will be slow, but have advantage of less memory usage.

I hope this helps, Please post your query so that we can understand exact scenario.

-4

Using SELECT statements instead of SETs can improve performance and readability, and may get you around the stated error. So instead of:

SET @a = 1
SET @b = 2
SET @c = @e + 2*@d

You can do:

SELECT @a = 1, @b = 2, @c = @e + 2 * @d

And set all three values in one statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.