none
Can SQL 2008 Debugger STEP INTO a recursive CTE?

    Question

  • As an example, taking the following AdventureWorks2008 sample stored procedure, can we "step into" the tree processing logic using the SSMS built-in debugger? Any other debugger?

    CREATE PROCEDURE dbo.uspGetManagerEmployees  @BusinessEntityID INT 
    AS 
     BEGIN 
      SET NOCOUNT ON; 
       
      WITH EMP_cte(BusinessEntityID,OrganizationNode,FirstName,LastName,RecursionLevel) -- CTE name and columns 
         AS (SELECT e.BusinessEntityID, 
              e.OrganizationNode, 
              p.FirstName, 
              p.LastName, 
              0 
           FROM  HumanResources.Employee e 
              INNER JOIN Person.Person p 
               ON p.BusinessEntityID = e.BusinessEntityID 
           WHERE e.BusinessEntityID = @BusinessEntityID 
           UNION ALL 
           SELECT e.BusinessEntityID, 
              e.OrganizationNode, 
              p.FirstName, 
              p.LastName, 
              RecursionLevel + 1 
           FROM  HumanResources.Employee e 
              INNER JOIN EMP_cte 
               ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode 
              INNER JOIN Person.Person p 
               ON p.BusinessEntityID = e.BusinessEntityID) 
      SELECT  EMP_cte.RecursionLevel, 
           EMP_cte.OrganizationNode.ToString() AS OrganizationNode, 
           p.FirstName             AS 'ManagerFirstName', 
           p.LastName             AS 'ManagerLastName', 
           EMP_cte.BusinessEntityID, 
           EMP_cte.FirstName, 
           EMP_cte.LastName 
      FROM   EMP_cte 
           INNER JOIN HumanResources.Employee e 
            ON EMP_cte.OrganizationNode.GetAncestor(1) = e.OrganizationNode 
           INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID 
      ORDER BY RecursionLevel, 
           EMP_cte.OrganizationNode.ToString() 
      OPTION (MAXRECURSION 25) 
     END; 
    
    GO 
    EXEC uspGetManagerEmployees 1
    GO
    /* RecursionLevel	OrganizationNode	ManagerFirstName	ManagerLastName	BusinessEntityID	FirstName	LastName
    1	/1/	Ken	Sánchez	2	Terri	Duffy
    1	/2/	Ken	Sánchez	16	David	Bradley
    1	/3/	Ken	Sánchez	25	James	Hamilton
    1	/4/	Ken	Sánchez	234	Laura	Norman
    1	/5/	Ken	Sánchez	263	Jean	Trenary
    1	/6/	Ken	Sánchez	273	Brian	Welcker
    2	/1/1/	Terri	Duffy	3	Roberto	Tamburello
    ....
    */ 
    

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 5:54 PM
    Moderator

Answers

  • No. A recursive CTE in essence is a single query/statement and this is the lowest level the T-SQL debugger can get to. I am not aware of any other debugger that can show the intermediate steps of recursive CTE. It does not sound easy as in essence it needs to have some kind of hooks into the SQL engine to get the temporary result sets, which probably is not available. Another reason is the "at once" nature of SQL to execute statements (that is not processing sequentially but rather dealing with sets).
    Plamen Ratchev
    Saturday, July 31, 2010 6:58 PM
    Moderator
  • One debugging technique that does work to debug recursive CTE's is to first run just the anchor portion of the cte, in your case

    SELECT e.BusinessEntityID, 
         e.OrganizationNode, 
         p.FirstName, 
         p.LastName, 
         0 
        FROM HumanResources.Employee e 
         INNER JOIN Person.Person p 
          ON p.BusinessEntityID = e.BusinessEntityID 
        WHERE e.BusinessEntityID = @BusinessEntityID 
    
    

    Then run the entire query, setting MAXRECURSION to 1.  That lets you see what the first recursion did (any rows which have been added that were not in the anchor have been added by the first recursion).  Then run with MAXRECURSION set to 2 and see what the second recursion did.  Etc.  In my experience this technique usually quickly leads me to what is wrong if I have a recursive cte that is not doing what I expected.

    Tom

    Saturday, July 31, 2010 8:09 PM
  • > No. A recursive CTE in essence is a single query/statement and this is the lowest level the T-SQL debugger can get to. I am not aware of any other debugger that can show the intermediate steps of recursive CTE. It does not sound easy as in essence it needs to have some kind of hooks into the SQL engine to get the temporary result sets, which probably is not available. Another reason is the "at once" nature of SQL to execute statements (that is not processing sequentially but rather dealing with sets).

    Yes, generally you cannot debug an SQL query piece by piece and for instance look at the result from a CTE. The execution plan may never produce that result, since execution order may be different.

    What could be possible is to have a debugger that permits to look at the output from various operators in the query plan. I would not be suprised if Microsoft internally has such a tool for testing. However, it is not likely to be very useful for the wide audience.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, July 31, 2010 8:42 PM
  • You mean manual debugging? That's what I would like to automate.

    Yes, I guess I do.  But I don't find it surprising that I have to do manual debugging.  I've not had a great deal of experience with debuggers in anything but SQL lately, but I don't recall a debugger that let's you see what goes on inside a single statement. So if you have

    A:=B+(C+D)/E/F*(G+H)/I;

    the debugger lets you see the values before the statement, and after the statement, but won't show you the intermediate results.  And, it seems to me, you are asking to see the intermediate results in SQL and I'm not surprised there is no way to do it.

    I agree, it would be nice if you could.

    Tom

    Saturday, July 31, 2010 10:27 PM
  • Yes, I guess I do.  But I don't find it surprising that I have to do manual debugging.  I've not had a great deal of experience with debuggers in anything but SQL lately, but I don't recall a debugger that let's you see what goes on inside a single statement. So if you have

    A:=B+(C+D)/E/F*(G+H)/I;

    the debugger lets you see the values before the statement, and after the statement, but won't show you the intermediate results. 

    But a debugger for a traditional language may offer you the possibility to evaluate for instance

       B+(C+D)/E/F

    The Perl debugger and the VB6 debuggers both permit this.

    Now, if we translate this to SQL, this means that you can run part of a statement, which you can do from a query window. So why would you want to do this in a middle of a stored procedure? Because you have filled a temp table or table variable with some data, and you want to see the result of the query with this data. And it could be permanent tables as well. Maybe you are in a transaction, and the procedure bombs in the next statement on a constraint violation, but you don't understand why.

    Could this be done? Probably. Could Microsoft do this easily? Probably not. Take the last scenario. You have stopped the debugger inside a transaction (which is dangerous in itself, if this is a production system), and you want to retrieve data, and you must use the same connection as the procedure is running on. There are probably quiet a few pieces that have to fall into place. Then Microsoft needs to ask: is it really worth the investment? After all, the debugger have limited usefulness, since it is vey difficult to get it to work when you don't have SQL Server locally. (Firewall, permissions for service accounts etc.)

    Keep in mind that debugging SQL is very different from debugging a client-side language: the code you debug lives in another process, and may be on a different machine.

    Put it on Connect if you think that this is important over many other posisble improvements, but I don't think I'm going to vote for it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, August 01, 2010 9:23 AM

All replies

  • No. A recursive CTE in essence is a single query/statement and this is the lowest level the T-SQL debugger can get to. I am not aware of any other debugger that can show the intermediate steps of recursive CTE. It does not sound easy as in essence it needs to have some kind of hooks into the SQL engine to get the temporary result sets, which probably is not available. Another reason is the "at once" nature of SQL to execute statements (that is not processing sequentially but rather dealing with sets).
    Plamen Ratchev
    Saturday, July 31, 2010 6:58 PM
    Moderator
  • One debugging technique that does work to debug recursive CTE's is to first run just the anchor portion of the cte, in your case

    SELECT e.BusinessEntityID, 
         e.OrganizationNode, 
         p.FirstName, 
         p.LastName, 
         0 
        FROM HumanResources.Employee e 
         INNER JOIN Person.Person p 
          ON p.BusinessEntityID = e.BusinessEntityID 
        WHERE e.BusinessEntityID = @BusinessEntityID 
    
    

    Then run the entire query, setting MAXRECURSION to 1.  That lets you see what the first recursion did (any rows which have been added that were not in the anchor have been added by the first recursion).  Then run with MAXRECURSION set to 2 and see what the second recursion did.  Etc.  In my experience this technique usually quickly leads me to what is wrong if I have a recursive cte that is not doing what I expected.

    Tom

    Saturday, July 31, 2010 8:09 PM
  • > No. A recursive CTE in essence is a single query/statement and this is the lowest level the T-SQL debugger can get to. I am not aware of any other debugger that can show the intermediate steps of recursive CTE. It does not sound easy as in essence it needs to have some kind of hooks into the SQL engine to get the temporary result sets, which probably is not available. Another reason is the "at once" nature of SQL to execute statements (that is not processing sequentially but rather dealing with sets).

    Yes, generally you cannot debug an SQL query piece by piece and for instance look at the result from a CTE. The execution plan may never produce that result, since execution order may be different.

    What could be possible is to have a debugger that permits to look at the output from various operators in the query plan. I would not be suprised if Microsoft internally has such a tool for testing. However, it is not likely to be very useful for the wide audience.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, July 31, 2010 8:42 PM
  • One debugging technique that does work to debug recursive CTE's is to first run just the anchor portion of the cte, in your case


    Tom,

    You mean manual debugging? That's what I would like to automate.


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 9:54 PM
    Moderator
  • No. A recursive CTE in essence is a single query/statement and this is the lowest level the T-SQL debugger can get to.
    Most unfortunate. It would be real fun (and useful) to follow recursive tree processing with the debugger.
    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 10:05 PM
    Moderator
  • Yes, generally you cannot debug an SQL query piece by piece and for instance look at the result from a CTE. The execution plan may never produce that result, since execution order may be different.

    Agreed. What I do (manual debugging) is high-light the first CTE within the parentheses and execute it. This technique does not work for nested CTE-s. You have no choice but put in a debug SELECT * FROM cte.  Why can't the debugger do it? It is not space science. Demo follows:

    ;WITH cteProd AS (
      SELECT ProductID, ProductName=Name, ListPrice, Color
      FROM AdventureWorks2008.Production.Product
      WHERE Color is not null),
    cteColorAvgPrice AS (
      SELECT Color, AvgPrice=AVG(ListPrice)
      FROM cteProd
      GROUP BY Color)
    -- SELECT * FROM cteColorAvgPrice  
    SELECT Color, AvgPrice=CONVERT(DECIMAL(10,1), AvgPrice)
    FROM cteColorAvgPrice
    ORDER BY AvgPrice Desc
    /* Color	AvgPrice
    Red	1402.0
    Yellow	959.1
    Blue	923.7
    Silver	850.3
    Black	725.1
    Grey	125.0
    Silver/Black	64.0
    Multi	59.9
    White	9.2
    */
    

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 10:21 PM
    Moderator
  • You mean manual debugging? That's what I would like to automate.

    Yes, I guess I do.  But I don't find it surprising that I have to do manual debugging.  I've not had a great deal of experience with debuggers in anything but SQL lately, but I don't recall a debugger that let's you see what goes on inside a single statement. So if you have

    A:=B+(C+D)/E/F*(G+H)/I;

    the debugger lets you see the values before the statement, and after the statement, but won't show you the intermediate results.  And, it seems to me, you are asking to see the intermediate results in SQL and I'm not surprised there is no way to do it.

    I agree, it would be nice if you could.

    Tom

    Saturday, July 31, 2010 10:27 PM
  • I agree, it would be nice if you could.

    My last application language was MS C++. I found the C++ debugger really useful, a treasure for boosting programmer's productivity.

    Since I am doing SQL I hardly ever use a debugger due to their limitations. I am not saying never. On occasions I used SQL Server and 3rd party debuggers.


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 10:39 PM
    Moderator
  • Yes, I guess I do.  But I don't find it surprising that I have to do manual debugging.  I've not had a great deal of experience with debuggers in anything but SQL lately, but I don't recall a debugger that let's you see what goes on inside a single statement. So if you have

    A:=B+(C+D)/E/F*(G+H)/I;

    the debugger lets you see the values before the statement, and after the statement, but won't show you the intermediate results. 

    But a debugger for a traditional language may offer you the possibility to evaluate for instance

       B+(C+D)/E/F

    The Perl debugger and the VB6 debuggers both permit this.

    Now, if we translate this to SQL, this means that you can run part of a statement, which you can do from a query window. So why would you want to do this in a middle of a stored procedure? Because you have filled a temp table or table variable with some data, and you want to see the result of the query with this data. And it could be permanent tables as well. Maybe you are in a transaction, and the procedure bombs in the next statement on a constraint violation, but you don't understand why.

    Could this be done? Probably. Could Microsoft do this easily? Probably not. Take the last scenario. You have stopped the debugger inside a transaction (which is dangerous in itself, if this is a production system), and you want to retrieve data, and you must use the same connection as the procedure is running on. There are probably quiet a few pieces that have to fall into place. Then Microsoft needs to ask: is it really worth the investment? After all, the debugger have limited usefulness, since it is vey difficult to get it to work when you don't have SQL Server locally. (Firewall, permissions for service accounts etc.)

    Keep in mind that debugging SQL is very different from debugging a client-side language: the code you debug lives in another process, and may be on a different machine.

    Put it on Connect if you think that this is important over many other posisble improvements, but I don't think I'm going to vote for it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, August 01, 2010 9:23 AM
  •  Because you have filled a temp table or table variable with some data, and you want to see the result of the query with this data. And it could be permanent tables as well.

    Precisely!  Just to see the content of local variables (if any) in the debugger is not very helpful.

    Hyperlinking into CTE-s, temp tables, table variables and permanent tables (changed rows) would be really helpful in the debugger.

     Vote on Connect: https://connect.microsoft.com/SQLServer/feedback/details/582167/debugger-should-hyperlink-into-cte-s-table-variables-temp-tables-and-changed-rows

     

     


     

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008

    Sunday, August 01, 2010 9:41 AM
    Moderator
  • I agree. But I think it will be some trade off to build such functionality. Most likely the query engine needs to be adjusted to allow such outputs at some cost, perhaps slower execution.
    Plamen Ratchev
    Sunday, August 01, 2010 4:48 PM
    Moderator