Change the system wide default for maxrecursion

Question

How do I change the system-wide default value of MAXRECURSION?

By default it is 100, but I need to increase it to something like 1000.

I am unable to use query hints since I'm using a program that takes my query and executes it for me and I can't get around this limitation, unfortunately.

However, I do have admin rights on the server instance. I've poked around in the server facets, but I don't see anything there related to query options or recursion. I assume there has to be a place somewhere where I can update the system-wide default.

Any ideas?

Paul White · Answer 1 · 2020-02-26T13:30:13.440

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides.

There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form using the built-in procedure sys.sp_get_query_template, and then create a TEMPLATE and/or OBJECT/SQL plan guide.

See the documentation for further information:

Plan guides will need to be revalidated whenever the application code changes, and when SQL Server is patched or upgraded. This should just be part of your normal testing cycle.

Note that plan guide validation using sys.fn_validate_plan_guide may incorrectly report a failure if the guided statement references a temporary table. See this question:

Plan guide validation with fn_validate_plan_guide gives false positives

The Plan Guide Successful and Plan Guide Unsuccessful Profiler and Extended Events classes can also be used to monitor plan guide applications.

Connect was retired before the product improvement suggestion Allow MAXRECURSION limit values other than 100 for views and UDFs by Steve Kass was implemented. If you would like to take it up with Microsoft now, see the options at SQL Server help and feedback.

wBob · Answer 2 · 2016-04-06T16:18:13.017

If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify OPTION as part of a multi-statement table-valued function, eg something like this:

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

This also worked for me when wrapped in a view as you suggest your ETL tools does. There is no way to alter this system-wide, but as recursion can be inefficient, this is probably a good thing. You can't specify a query hint (using OPTION) within the body of an inline table-valued function, as in your example.

Consider altering your process to walk the hierarchy only once when you receive your Episodes and store the output in a relational table. You could use a stored proc to do this so would not run into this limitation.

I also think there might be a bug in your code: if your CTE joins on personId and recurses on eventId, the eventId 101 would present twice I think, as a duplicate. Possibly I've misinterpreted your code, let me know what you think.

HTH

score 0 · Answer 3 · edited May 23 '17 at 12:40

I took inspiration from this topic.

Here's what I've done to solve the 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

Then I invoke this function like this:

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

This way, none of my CTE logic has to be repeated and I don't pay anything extra in terms of performance. It's a nuisance that it has to be done this way, but I can live with it.

Change the system wide default for maxrecursion

3 Answers3