March 12, 2020 at 9:38 am
We have a Stored Procedure that uses a recursive CTE and, from time to time, the Stored Procedure fails because the maximum recursion of 100 (the default) has been reached.
We'd like to adjust this value this to ensure that:
a. The Stored Procedure never fails for that reason
b. The MAXRECURSION value is set to a value that permits a. but doesn't allow for an infinite loop.
Is there any way that, given some test data, one can find out how many recursive calls there have been? I've been experimenting with PRINT statements but they don't seem to work ...
Thanks in advance
Edward
March 12, 2020 at 10:34 am
to find out how many recursions then put an int as your anchor, then keep incrementing it
something like this (I copied and pasted from another site)
WITH cte
AS (SELECT 1 AS n -- anchor member
UNION ALL
SELECT n + 1 -- recursive member
FROM cte
WHERE n < 50 -- terminator
)
SELECT n
FROM cte;
I don't normally recommend using print statements (in sql agent they cause so many other things in the job log it becomes unreadable)
the "where n<50" stops your infinite loop , question is... why do you want to vary it? do you just want to figure out the value or are you trying to do it dynamically (I do not recommend that)
MVDBA
March 12, 2020 at 10:40 am
Normally when I am working with rCTE I add a column to prevent circular dependencies (which is what normally causes the max recursion error)
example below is to do with Active Directory group membership
briefly - a Group can contain more Groups up to the point where all members are Users
but in some cases group A contains B, B contains C and C contains A - this is a circular reference and would give an error.
As such I setup a "call stack" with the group name (domain + name) - and on the union all part of the CTE if that combination already exists on the stack then it is a recursive and we ignore it
with DepTree
( GroupDomain
, GroupName
, MemberDomain
, MemberName
, callstack
, NestLevel
)
as (select o.GroupDomain as GroupDomain
, o.GroupName as GroupName
, o.SourceDomain as MemberDomain
, o.SamAccountName as MemberName
, cast('|' + o.SourceDomain + '|' + o.SamAccountName + '|' as nvarchar(max)) as callstack
, 0 as NestLevel
from #groupsmembers o
union all
select r.GroupDomain as GroupDomain
, r.GroupName as GroupName
, o.SourceDomain as MemberDomain
, o.SamAccountName as MemberName
, callstack + o.SourceDomain + '|' + o.SamAccountName + '|' as callstack
, NestLevel + 1 as NestLevel
from DepTree r
join #groupsmembers o
on o.GroupDomain = r.MemberDomain
and o.GroupName = r.MemberName
where callstack not like '%|' + o.MemberDomain + '|' + o.SamAccountName + '|%'
)
March 12, 2020 at 10:47 am
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32,767. When 0 is specified, no limit is applied. If this option isn't specified, the default limit for the server is 100.
When the specified or default number for MAXRECURSION limit is reached during query execution, the query ends and an error returns.
Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15
The maximum you can set it to is 32,767, if you use this value you will not get an infinite loop but it will allow the most recursions before it gives up.
March 12, 2020 at 11:04 am
Thanks everyone. I agree about not using PRINT statements, but I was not planning on adding this to the production code, just during the debugging process. The reason I want to do this is because it's occasionally running up against the default MAXRECURSION value, and I wanted to find out how many actual recursions were being called ... if it were 101 then that would merely be irritating; if it were 101,000 that would be a problem! I know about the 32,767 and 0 values, but I want to be able to set the value to a sensible figure that will allow the code to run without failing, but also without breaking anything!
March 12, 2020 at 11:10 am
Just out of interest... is this an update proc or a select proc?
MVDBA
March 12, 2020 at 11:26 am
Just out of interest... is this an update proc or a select proc?
It uses the recursive CTE to populate some memory tables, which are then used in a succession of INSERT statements, then a MERGE and finally an UPDATE and a DELETE. It's what would probably be called in a C# coding context a "God Class". Given half a chance I'd probably refactor it, but this is regression testing and there isn't much appetite for more of the same! If it helps. the target databases are SAGE and MESTEC.
March 12, 2020 at 11:49 am
you can easily update the code I supplied to flag when a circular happened - I would be pretty sure that that is what is happening to you
potentially outputting the value of circularcall, nestlevel and other details onto a new table to report on it.
this in conjunction with setting the max recursion level high enough you can see what values are causing you issues.
, cast('|' + o.SourceDomain + '|' + o.SamAccountName + '|' as nvarchar(max)) as callstack
, 0 as circularcall
, 0 as NestLevel
from #groupsmembers o
...
, case when callstack like '%|' + o.MemberDomain + '|' + o.SamAccountName + '|%' then 1 else 0 end as circularcall
, NestLevel + 1 as NestLevel
from DepTree r
join #groupsmembers o
on o.GroupDomain = r.MemberDomain
and o.GroupName = r.MemberName
where r.circularcall = 0
March 16, 2020 at 12:39 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply