Jaka jest maksymalna liczba zmiennych lokalnych, które mogą brać udział w operacji SET?

Mam procedurę składowaną, która zawiera logikę biznesową. Wewnątrz mam około 1609 zmiennych (nie pytaj mnie dlaczego, tak działa silnik). Próbuję SETzmiennej do połączonej wartości wszystkich innych zmiennych. W rezultacie podczas tworzenia pojawia się błąd:

Msg 8631, poziom 17, stan 1, procedura XXX, wiersz RRRR Błąd wewnętrzny: Osiągnięto limit stosu serwera. Poszukaj potencjalnie głębokiego zagnieżdżenia w zapytaniu i spróbuj je uprościć.

Doszedłem do wniosku, że błąd wynika z liczby zmiennych, których muszę użyć w SEToperacji. Mogę wykonać zadanie, dzieląc je na dwie części.

Moje pytanie: czy są jakieś ograniczenia w tym obszarze? Sprawdziłem, ale nie znalazłem.

Sprawdziliśmy błąd opisany w tym KB , ale to nie jest nasz przypadek. Nie używamy żadnych CASEwyrażeń w naszym kodzie. Używamy tej zmiennej tymczasowej do przygotowania listy wartości, które należy zastąpić za pomocą funkcji CLR. Zaktualizowaliśmy nasz SQL Server do SP3 CU6 (najnowszy na bieżąco), ale nadal występuje błąd.

Msg 8631, poziom 17, stan 1, wiersz xxx
Błąd wewnętrzny: Osiągnięto limit stosu serwera.
Poszukaj potencjalnie głębokiego zagnieżdżenia w zapytaniu i spróbuj je uprościć.

Ten błąd występuje w przypadku długich SETlub SELECTzmiennych list konkatenacji przypisań ze względu na sposób analizowania i wiązania tego typu instrukcji przez SQL Server - jako zagnieżdżoną listę konkatenacji z dwoma wejściami.

Na przykład SET @V = @W + @X + @Y + @Zjest związany z drzewem postaci:

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

Każdy element konkatenacji po pierwszych dwóch powoduje dodatkowy poziom zagnieżdżenia w tej reprezentacji.

Ilość miejsca stosu dostępna dla SQL Server określa ostateczny limit tego zagnieżdżenia. Po przekroczeniu limitu wewnętrzny wyjątek jest generowany, co ostatecznie powoduje wyświetlenie powyższego komunikatu o błędzie. Przykład stosu wywołań procesowych po zgłoszeniu błędu pokazano poniżej:

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

Jest to podstawowy limit ze względu na sposób, w jaki wiele konkatenacji jest obsługiwanych wewnętrznie. Wpływa SETi SELECTinstrukcje przypisania zmiennej jednakowo.

Obejściem tego problemu jest ograniczenie liczby konkatenacji wykonywanych w pojedynczej instrukcji. Zazwyczaj będzie to również bardziej wydajne, ponieważ kompilowanie drzew głębokich zapytań wymaga dużych zasobów.

Zainspirowany @Paul „s odpowiedzi , zrobiłem kilka badań i stwierdził, że o ile prawdą jest, że przestrzeń stosu ogranicza liczbę powiązań,, i że przestrzeń stosu jest funkcją dostępną pamięć, a tym samym zmienia się następujące dwa punkty są również prawdziwe :

1. istnieje sposób na wtłoczenie dodatkowych konkatenacji w jedno zdanie, ORAZ
2. używając tej metody, aby wyjść poza początkowe ograniczenie miejsca na stosie, można znaleźć rzeczywisty limit logiczny (który nie wydaje się zmieniać)

Najpierw dostosowałem kod testowy Paula do łączenia ciągów:

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

W tym teście najwyższe, jakie mogłem uzyskać, kiedy działałem na moim niezbyt świetnym laptopie (tylko 6 GB pamięci RAM), to:

• 3311 (zwraca 3312 znaków) przy użyciu SQL Server 2017 Express Edition LocalDB (14.0.3006)
• 3512 (zwraca 3513 znaków) przy użyciu SQL Server 2012 Developer Edition SP4 (KB4018073) (11.0.7001)

przed pojawieniem się błędu 8631 .

Następnie próbowałem pogrupować konkatenacje za pomocą nawiasów, tak aby operacja konkatenowała wiele grup konkatenacji. Na przykład:

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

Dzięki temu mogłem znacznie przekroczyć poprzednie limity zmiennych 3312 i 3513. Zaktualizowany kod to:

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

Maksymalne wartości (dla mnie) mają teraz zostać użyte 42dla pierwszej REPLICATE, a więc przy użyciu 43 zmiennych na grupę, a następnie 762dla drugiej REPLICATE, a więc przy użyciu 762 grup po 43 zmienne każda. Grupa początkowa jest zakodowana na stałe za pomocą dwóch zmiennych.

Dane wyjściowe pokazują, że @Szmienna zawiera 32 768 znaków . Jeśli zaktualizuję początkową grupę, aby była (@A+@A+@A)zamiast (@A+@A), to pojawia się następujący błąd:

Msg 8632, poziom 17, stan 2, wiersz XXXXX
Błąd wewnętrzny: Osiągnięto limit usług wyrażania. Wyszukaj potencjalnie złożone wyrażenia w zapytaniu i spróbuj je uprościć.

Zauważ, że numer błędu jest inny niż wcześniej. Teraz jest: 8632 . I mam ten sam limit bez względu na to, czy korzystam z mojej instancji SQL Server 2012, czy SQL Server 2017.

To chyba nie przypadek, że górna granica tutaj - 32.768 - to maksymalna pojemność od SMALLINT( Int16w .NET) JEŻELI zaczynając od 0(wartość maksymalna wynosi 32.767 ale tablice w wielu / większości języków programowania są oparte na 0).

Innymi słowy, jest to po prostu brak pamięci, ponieważ procedura przechowywana wykonana w pamięci i dostępne tranzystory sprzętowe lub wirtualna pamięć stron dostępna dla SQL są pełne!

Więc to w zasadzie przepełnienie stosu w SQL Server.

Teraz najpierw spróbuj uprościć proces, ponieważ wiemy, że potrzebujesz 1609 zmiennych,

Ale czy potrzebujesz wszystkich zmiennych jednocześnie?

W razie potrzeby możemy zadeklarować i używać zmiennych.

Na przykład:

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

Ale jeśli spróbujemy tego w pętli, dodając

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

Uwaga: zużyje to więcej procesora i zajmie trochę więcej czasu w obliczeniach.

Teraz będzie to powolne, ale będzie miało zaletę mniejszego zużycia pamięci.

Mam nadzieję, że to pomoże. Prześlij zapytanie, abyśmy mogli zrozumieć dokładny scenariusz.

Używanie instrukcji SELECT zamiast zestawów SET może poprawić wydajność i czytelność oraz może ominąć podany błąd. Więc zamiast:

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

Możesz to zrobić:

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

I ustaw wszystkie trzy wartości w jednej instrukcji.