Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fix SQL Server CTE Maximum Recursion Exhausted Error


By:   |   Last Updated: 2017-10-13   |   Comments (1)   |   Related Tips: More > Scripts

Problem

Common Table Expression, also known as a CTE, can be thought of as a derived table that is defined within the query execution scope, without being stored physically in the database and survives only for the running query duration. A CTE allows us to write more readable, maintainable complex queries, by dividing these queries into small blocks that can be gathered together to build the final complex query. The CTE can be easily included within stored procedures, views, triggers and functions.

One of the most useful benefits of the CTE is the ability to create a recursive query within it. This means that the CTE can be self-referenced within the same query. But if it is not designed carefully, it may result in an infinite loop. To prevent that, SQL Server set the default value of the recursion level to 100. In specific cases, you may exceed that default value of recursion. In this case the query will fail showing that you canít exceed this 100 limitation. How could we tune the recursion value to fit our query?

Solution

Limiting the number of recursions allowed for a specific query in SQL Server with the 100 default value prevents the cause of an infinite loop situation due to a poorly designed recursive CTE query. But it also leaves the choice for you to tune that value to fit your query, where you can easily increase or decrease that value at the query level using the MAXRECURSION query hint in the OPTION clause. The MAXRECURSION value specifies the number of times that the CTE can recur before throwing an error and terminating.

You can provide the MAXRECURSION hint with any value between 0 and 32,767 within your T-SQL query, with MAXRECURSION value equal to 0 means that no limit is applied to the recursion level.  This opens up the risk of an infinite loop with poorly written queries.

Let's start our demo to understand how it works practically. Assume that we have a user-defined function that is used to split the strings depending on the provided delimiter. The function is written using the recursive CTE mechanism.  One of the ways that can be used to write a function is shown below:

USE MSSQLTipsDemo
GO
CREATE FUNCTION [dbo].[SplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT        'Value' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
   

This type of function is sometimes used for splitting strings in some systems. For example, the below stored procedure takes a list of comma separated entities as a parameter and returns these entities as individual entities:

USE MSSQLTipsDemo
GO
CREATE PROC REC_Entity (@Entity AS NVARCHAR(MAX))
AS
BEGIN 

CREATE TABLE #Entity (RetValue  NVARCHAR (3))
INSERT INTO #Entity SELECT * from [SplitString] (@Entity, ',') 
SELECT * FROM #Entity
DROP TABLE #Entity

END
   

Now, we will declare two entities lists; @ENTITY1 and @ENTITY2, @ENTITY1 contains about 24 comma separated entities and @ENTITY2 contains about 110 comma separated entities. We will pass these two parameters to the previously created stored procedure to split its values using the T-SQL script below:

DECLARE @ENTITY1 AS NVARCHAR(MAX)
DECLARE @ENTITY2 AS NVARCHAR(MAX)

SET @ENTITY1='AZZ,AZT,AZS,AZR,AZP,AZO,AZN,AZL,AZI,AZG,AZD,AZB,AZA,AYY,AYW,AYU,AYT,AYS,AYR,AYQ,AYP,AYO,AYN,AYM'
SET @ENTITY2='AYL,AYK,AYI,AYG,AYD,AYC,AYA,AXX,AXV,AXU,AXT,AXS,AXR,AXP,AXN,AXM,AXL,AXK,AXJ,AXG,AXF,AXE,AXD,AXC,AXB,AXA,AWZ,AWR,AWP,AWN,AWM,AWK,AWE,AWD,AWB,AWA,AVX,AVW,AVV,AVU,AVP,AVO,AVN,AVL,AVK,AVI,AVG,AVB,AVA,AUZ,AUY,AUX,AUW,AUV,AUU,AUT,AUS,AUR,AUQ,AUP,AUO,AUN,AUM,AUL,AUK,AUJ,AUI,AUH,AUG,AUF,AUE,AUD,AUC,AUA,ATZ,ATY,ATX,ATW,ATV,ATU,ATT,ATS,ATR,ATQ,ATP,ATO,ATN,ATM,ATL,ATK,ATJ,ATI,ATH,ATG,ATF,ATE,ATD,ATC,ATB,ATA,ASZ,ASY,ASX,ASW,ASV,ASU,AST,ASR,ASQ,ASP'

EXEC REC_Entity @ENTITY1
EXEC REC_Entity @ENTITY2
   

Running the previous query, you will see from the result that splitting the first parameter that contains 24 comma separated entities will be completed successfully, and splitting the second entities list will fail as shown in the result below:

First Run With CTE Recurrsion Error

Checking the error message returned from the query execution, you will see that splitting the second entities list failed due to exceeding the maximum recursion level default value of 100. The first entities list requires 24 recursions to be split, which is less than the 100 maximum number of recursions. Splitting the second entities list requires 110 recursions, exceeding the 100 default value of recursions. Recall that this recursion limitation is mainly used to protect the system from the infinite loop issue resulting from the poorly written queries. The error will be as shown below:

SQL Server CTE Recursion Error

As we mentioned previously, although this limit will protect you from the infinite loop issue, you can easily tune the MAXRECURSION value to fit your query. You can use the MAXRECURSION query hint within the previously defined stored procedure in the part that calls the string splitting function to fit this query only, with MAXRECURSION value equal to 110, using the modified stored procedure below:

USE MSSQLTipsDemo
GO
ALTER PROC REC_Entity (@Entity AS NVARCHAR(MAX))
AS
BEGIN 

CREATE TABLE #Entity (RetValue  NVARCHAR (3))
INSERT INTO #Entity SELECT * from [SplitString] (@Entity, ',') OPTION (MAXRECURSION 110)
SELECT * FROM #Entity
DROP TABLE #Entity

END
   

Executing the modified stored procedure again to split the same two entities lists using the T-SQL script below:

DECLARE @ENTITY1 AS NVARCHAR(MAX)
DECLARE @ENTITY2 AS NVARCHAR(MAX)

SET @ENTITY1='AZZ,AZT,AZS,AZR,AZP,AZO,AZN,AZL,AZI,AZG,AZD,AZB,AZA,AYY,AYW,AYU,AYT,AYS,AYR,AYQ,AYP,AYO,AYN,AYM'
SET @ENTITY2='AYL,AYK,AYI,AYG,AYD,AYC,AYA,AXX,AXV,AXU,AXT,AXS,AXR,AXP,AXN,AXM,AXL,AXK,AXJ,AXG,AXF,AXE,AXD,AXC,AXB,AXA,AWZ,AWR,AWP,AWN,AWM,AWK,AWE,AWD,AWB,AWA,AVX,AVW,AVV,AVU,AVP,AVO,AVN,AVL,AVK,AVI,AVG,AVB,AVA,AUZ,AUY,AUX,AUW,AUV,AUU,AUT,AUS,AUR,AUQ,AUP,AUO,AUN,AUM,AUL,AUK,AUJ,AUI,AUH,AUG,AUF,AUE,AUD,AUC,AUA,ATZ,ATY,ATX,ATW,ATV,ATU,ATT,ATS,ATR,ATQ,ATP,ATO,ATN,ATM,ATL,ATK,ATJ,ATI,ATH,ATG,ATF,ATE,ATD,ATC,ATB,ATA,ASZ,ASY,ASX,ASW,ASV,ASU,AST,ASR,ASQ,ASP'

EXEC REC_Entity @ENTITY1
EXEC REC_Entity @ENTITY2
   

You will see that the two stored procedure calls are executed successfully and the two comma separated entities lists are split completely without showing any error as shown in the result below:

Successful SQL Server CTE MaxRecurrsion Configuration

To be more dynamic, it is better to tune the MAXRECURSION value in the previous stored procedure with the maximum number of entities that can be passed to that stored procedure. For example, if you are dealing with countries that will not have more than 500 entities, you can replace the previous query hint with OPTION (MAXRECURSION 500), setting 500 as the maximum allowed recursion level for that query, and at the same time protecting your system from the infinite loop issue, by terminating the query if it exceeds that number of recursions.

If your query requires recursion level greater than the maximum allowed MAXRECURSION value, which is 32,767, you can achieve that by breaking the MAXRECURSION limitation by setting the MAXRECURSION value to 0 using the OPTION (MAXRECURSION 0) query hint. In this way, you will take the risk of falling in the infinite loop problem if the CTE recursion query is not written well.

Next Steps


Last Updated: 2017-10-13


next webcast button


next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, October 13, 2017 - 8:09:33 AM - Tom Groszko Back To Top

 You can exceed a limit with a well written query if the database contains a circlular structure. Likely a database with a problem and a query poorly written.

 


Learn more about SQL Server tools