Qual é o número máximo de variáveis ​​locais que podem participar da operação SET?

Msg 8631, nível 17, estado 1, linha xxx
Erro interno: o limite de pilha do servidor foi atingido.
Procure um aninhamento potencialmente profundo em sua consulta e tente simplificá-lo.

Esse erro ocorre com listas de concatenação de atribuição longa SETou SELECTvariável devido à maneira como o SQL Server analisa e vincula esse tipo de instrução - como uma lista aninhada de concatenações de duas entradas.

Por exemplo, SET @V = @W + @X + @Y + @Zestá vinculado a uma árvore da forma:

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

Cada elemento de concatenação após os dois primeiros resulta em um nível extra de aninhamento nessa representação.

A quantidade de espaço de pilha disponível para o SQL Server determina o limite final para esse aninhamento. Quando o limite é excedido, uma exceção é levantada internamente, o que eventualmente resulta na mensagem de erro mostrada acima. Um exemplo de pilha de chamadas de processo quando o erro é lançado é mostrado abaixo:

Reprodução

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);

Este é um limite fundamental devido à forma como várias concatenações são tratadas internamente. Ela afeta as instruções de atribuição SETde SELECTvariáveis ​​igualmente.

A solução é limitar o número de concatenações executadas em uma única instrução. Isso também costuma ser mais eficiente, já que a compilação de árvores de consulta profunda exige muitos recursos.

Inspirado pela resposta de @Paul , fiz algumas pesquisas e descobri que, embora seja verdade que o espaço da pilha limita o número de concatenações e que o espaço da pilha é uma função da memória disponível e, portanto, varia, os dois pontos a seguir também são verdadeiros :

1. existe uma maneira de amontoar concatenações adicionais em uma única instrução, E
2. usando este método para ir além da limitação de espaço de pilha inicial, um limite lógico real (que não parece variar) pode ser encontrado

Primeiro, adaptei o código de teste de Paul para concatenar 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);

Com este teste, o máximo que consegui ao executar em meu laptop não tão bom (apenas 6 GB de RAM) foi:

• 3311 (retorna um total de 3312 caracteres) usando o SQL Server 2017 Express Edition LocalDB (14.0.3006)
• 3512 (retorna um total de 3513 caracteres) usando SQL Server 2012 Developer Edition SP4 (KB4018073) (11.0.7001)

antes de receber o erro 8631 .

Em seguida, tentei agrupar as concatenações usando parênteses de forma que a operação concatenasse vários grupos de concatenações. Por exemplo:

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

Fazendo isso, consegui ir muito além dos limites anteriores de 3312 e 3513 variáveis. O código atualizado é:

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);

Os valores máximos (para mim) agora são usar 42para o primeiro REPLICATE, usando assim 43 variáveis ​​por grupo, e depois usar 762para o segundo REPLICATE, usando assim 762 grupos de 43 variáveis ​​cada. O grupo inicial é codificado com duas variáveis.

A saída agora mostra que há 32.768 caracteres na @Svariável. Se eu atualizar o grupo inicial para ser (@A+@A+@A)em vez de apenas (@A+@A), recebo o seguinte erro:

Msg 8632, Nível 17, Estado 2, Linha XXXXX
Erro interno: Um limite de serviços de expressão foi atingido. Procure expressões potencialmente complexas em sua consulta e tente simplificá-las.

Observe que o número do erro é diferente do anterior. Agora é: 8632 . E, tenho esse mesmo limite, independentemente de usar minha instância do SQL Server 2012 ou a instância do SQL Server 2017.

Provavelmente não é coincidência que o limite superior aqui — 32.768 — seja a capacidade máxima de SMALLINT( Int16no .NET) IF começando em 0(o valor máximo é 32.767, mas os arrays em muitas/a maioria das linguagens de programação são baseados em 0).

Agora, isso é simplesmente falta de memória em outras palavras, pois a operação do procedimento armazenado feito na memória e os transistores de hardware disponíveis ou memória de página virtual disponível para SQL estão cheios!

Portanto, é basicamente Stack Overflow no SQL Server.

Agora, primeiro tente simplificar o processo, pois sabemos que você precisa de 1609 variáveis,

Mas você precisa de todas as variáveis ​​ao mesmo tempo?

Podemos declarar e usar variáveis ​​quando necessário.

Por exemplo:

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

Mas se tentarmos isso em um loop adicionando

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

Observação: isso usará mais CPU e levará um pouco mais de tempo no cálculo.

Agora, isso será lento, mas terá a vantagem de usar menos memória.

Espero que isso ajude, Por favor, poste sua consulta para que possamos entender o cenário exato.

O uso de instruções SELECT em vez de SETs pode melhorar o desempenho e a legibilidade e pode contornar o erro declarado. Então ao invés de:

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

Você pode fazer:

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

E defina todos os três valores em uma instrução.