12

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?

1
  • 3
    I just wanted to check you understood the limit of 100 was only on views and functions and that you could use a stored procedure and override locally there? Is there any particular need to use a function? As recursion is fairly inefficient, I would also suggested walking the hierarchy only once and storing the output in a table. You could then create a function which referred to that table. What do you think? – wBob Apr 5 '16 at 20:38
10

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.

2
  • This is frustrating and doesn't answer the question instead burying us in a rabbit hole of documentation. EF Core (a typical ORM) generates queries for you even if you give it a raw SQL statement it wraps that in a parent select, anyone using EF Core has this problem. Your solution is "plan your queries". – War Feb 26 '20 at 10:46
  • @War It's the best answer I can give to this particular question with the detail provided. The only way I know of to add a max recursion hint is via a SQL Server thing called a Plan Guide, which has nothing to do with "planning your queries". If you have a specific question of your own, please ask it separately with a minimal reproducible example. – Paul White Feb 26 '20 at 13:21
9

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

1
  • this doesn't work since the "OPTIONS" parameter has to be applied at the statement level and the statement in question is the call to the function, this will return an exception. – War Feb 26 '20 at 10:41
0

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.

5
  • 3
    I don't see how this solves a recursion problem. The invocation of the function is not recursive. – ypercubeᵀᴹ Apr 5 '16 at 19:21
  • @ypercubeᵀᴹ - the recursive bit of the CTE goes where I have my ellipsis - my particular recursive logic is not really relevant to the problem, but you can assume that the CTE is, in fact, recursive. The where clause after the ellipsis prevents too many recursions from happening by using the function parameter as a constraint. I guess there should be a statement after the CTE definition, though. I'll add that. – carl.anderson Apr 5 '16 at 20:30
  • 3
    I understand very well that the CTE is recursive. The problem is that the invocation (function calls) are not recursive. For example, you call the functions with starting points (rows) with EventID=1 (and 101,201, ...901). But the original query (if run with MAXRECURSION=100000000) may never visit the row with EventID=101 (and 201, ..,901). So the two queries (original and your solution) may return different results (no row with 101 in the first, yes in the 2nd)! Or it may visit the 101 but before step 100, so your solution would include the row twice in the results (again different) – ypercubeᵀᴹ Apr 6 '16 at 9:30
  • 2
    Unless of course the data are connected thorugh sequential EventID values (1,2,,3...,99,100,101,..). In which case, you wouldn't need a recursive CTE at all. – ypercubeᵀᴹ Apr 6 '16 at 9:34
  • How does this solve the unknown depth issue for something like ... getting a tree from a given DMS path as a rowset? – War Feb 26 '20 at 10:42

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.