Zmień wartość domyślną dla całego systemu dla maksrekursji

Jak zmienić wartość domyślną dla całego systemu MAXRECURSION?

Domyślnie jest to 100, ale muszę go zwiększyć do około 1000.

Nie mogę korzystać ze wskazówek dotyczących zapytań, ponieważ korzystam z programu, który pobiera moje zapytanie i wykonuje je dla mnie i niestety nie mogę obejść tego ograniczenia.

Mam jednak uprawnienia administratora do instancji serwera. Przeszukiwałem aspekty serwera, ale nie widzę tam nic związanego z opcjami zapytania lub rekurencją. Zakładam, że musi być miejsce, w którym mogę zaktualizować domyślne ustawienia systemowe.

Jakieś pomysły?

Jeśli twoje zapytania mają wspólny kształt, możesz być w stanie dodać wymaganą wskazówkę dotyczącą maksrekursji za pomocą jednego lub więcej przewodników planu.

Ich umiejętność może być sprytna. Jeśli dodasz do pytania określone szczegóły zapytania, możemy być w stanie to dla Ciebie rozwiązać. Zazwyczaj prześledzisz kod SQL faktycznie uderzający w serwer lub uzyskasz sparametryzowany formularz za pomocą wbudowanej procedury sys.sp_get_query_template , a następnie utworzysz szablon SZABLON i / lub OBJECT / SQL.

Więcej informacji znajduje się w dokumentacji:

• Przewodniki po planach
• Utwórz nowy przewodnik po planach
• Utwórz przewodnik po planach dla sparametryzowanych zapytań
• Określ zachowanie parametryzacji zapytania za pomocą przewodników planu
• Zastosuj ustalony plan zapytań do przewodnika planu
• Załącz wskazówki dotyczące zapytań do Przewodnika po planach
• Wyświetl właściwości przewodnika po planach
• Użyj SQL Server Profiler do tworzenia i testowania prowadnic planu
• Sprawdź poprawność planu po aktualizacji

Przewodniki po planach będą wymagały ponownej walidacji za każdym razem, gdy zmienia się kod aplikacji oraz gdy SQL Server jest łatany lub aktualizowany. To powinno być po prostu częścią twojego normalnego cyklu testowania.

Należy pamiętać, że sprawdzanie poprawności przewodnika planu przy użyciu sys.fn_validate_plan_guide może niepoprawnie zgłosić awarię, jeśli instrukcja prowadzona odwołuje się do tabeli tymczasowej. Zobacz to pytanie:

Sprawdzanie poprawności przewodnika planu za pomocą fn_validate_plan_guide daje fałszywe wyniki dodatnie

The Plan Przewodnik Udane i Plan Guide Nieudane Profiler i rozszerzony wydarzenia klasy mogą być również wykorzystywane do monitorowania aplikacji przewodnik planu.

Connect został wycofany przed sugestią ulepszenia produktu Zezwalaj na wartości maksymalne MAXRECURSION inne niż 100 dla widoków i implementacji UDF przez Steve'a Kassa . Jeśli chcesz teraz podjąć współpracę z Microsoftem, zapoznaj się z opcjami pomocy i opinii programu SQL Server .

Jeśli musisz bezwzględnie użyć funkcji (jak sugerujesz, ograniczenia narzędzia ETL), możesz określić OPTIONjako część funkcji o wielu tabelach, np. Coś takiego:

CREATE FUNCTION dbo.udf_MyFunction ( @StartID INT ) 
RETURNS @tv TABLE
(
id INT
)
AS
BEGIN

    WITH Episodes( xlevel, PersonID, EventID, EpisodeID, StartDT, EndDT ) AS (
    -- Anchor case - the first EventID for each person.
    SELECT 1 AS xlevel, PersonID, EventID, @StartID, StartDT, EndDT 
    FROM dbo.EventTable
    WHERE EventID = @StartID

    UNION ALL

    SELECT xlevel + 1, et.PersonID, et.EventID, c.EventID + 1, et.StartDT, et.EndDT
    FROM Episodes c
        INNER JOIN dbo.EventTable et ON c.PersonID = et.PersonID
            AND et.EventID = c.EventID + 1
    --WHERE c.EventID <= (@StartID + 99)
    )
    INSERT INTO @tv
    SELECT PersonID
    FROM Episodes
    OPTION ( MAXRECURSION 1000 )

    RETURN

END
GO

Działa to również dla mnie, gdy jest zawinięty w widok, jak sugerujesz, że twoje narzędzia ETL. Nie ma sposobu, aby zmienić ten system, ale ponieważ rekurencja może być nieefektywna, jest to prawdopodobnie dobra rzecz. Nie można określić wskazówki dotyczącej zapytania (za pomocą OPTION) w treści wbudowanej funkcji o wartości tabeli, jak w przykładzie.

Zastanów się nad zmianą procesu, aby przejść hierarchię tylko raz, gdy otrzymasz odcinki i zapisz dane wyjściowe w tabeli relacyjnej. Możesz użyć do tego celu zapisanego proc, aby nie napotkać tego ograniczenia.

Wydaje mi się również, że w twoim kodzie może być błąd: jeśli twoje CTE dołącza do personId i powtarza się w eventId, eventId 101 prezentowałby się dwa razy, jak sądzę, jako duplikat. Być może źle zinterpretowałem twój kod, daj mi znać, co myślisz.

HTH

Inspirację czerpałem z tego tematu .

Oto, co zrobiłem, aby rozwiązać problem.

CREATE FUNCTION MySchema.udf_MyFunction(@StartID INT) 
RETURNS TABLE 
AS RETURN
WITH
Episodes(PersonID, EventID, EpisodeID, StartDT, EndDT) AS (
  -- Anchor case - the first EventID for each person.
  SELECT PersonID, EventID, @StartID, StartDT, EndDT 
  FROM MySchema.EventTable
  WHERE EventID = @StartID
UNION ALL
  SELECT
    ...
  WHERE
    EventID <= (@StartID + 99)
)
SELECT * FROM Episodes

Następnie wywołuję tę funkcję w następujący sposób:

WITH
Episodes AS (
  SELECT * FROM MySchema.udf_MyFunction(1)
UNION ALL
  SELECT * FROM MySchema.udf_MyFunction(101)
UNION ALL
  SELECT * FROM MySchema.udf_MyFunction(201)
-- ...
UNION ALL
  SELECT * FROM MySchema.udf_MyFunction(901)
)
SELECT * FROM Episodes

W ten sposób moja logika CTE nie musi się powtarzać i nie płacę nic więcej pod względem wydajności. To uciążliwe, że trzeba to robić w ten sposób, ale mogę z tym żyć.