Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CTE and Spool operators Expand / Collapse
Author
Message
Posted Tuesday, April 09, 2013 3:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:53 PM
Points: 387, Visits: 749
I commonly find CTE using spool operators in the execution plan. According to Microsoft the spool operator uses the temp table.

"The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input."

So this leads to believe its possible that CTE's are using tempdb? so are the results stored in memory or tempdb? or does is DEPEND?
Post #1440588
Posted Wednesday, April 10, 2013 2:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:30 AM
Points: 403, Visits: 744
Read this http://www.sqlservercentral.com/blogs/scarydba/2009/09/09/spools-in-execution-plans/

If spooling happens, it will show in the execution plan.
Post #1440703
Posted Wednesday, April 10, 2013 8:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:53 PM
Points: 387, Visits: 749
If anyone is curious like most things it depends..

If the CTE is using recursion then its uses a spool operator therefore tempdb is used....therefore not much performance benefit vs just creating a temp table

If the CTE is non recursive then then the main qry is repeated and performance is that of using a cursor..the main anchor query is repeated therefor performance is no good!



http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/11/common-table-expressions-cte-s-how-it-works-how-recursion-works-using-with-adjacency-list.aspx
Post #1440834
Posted Wednesday, April 10, 2013 8:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
Do you have the process that you are looking at as it might give people some pointers as to why this is happening.

with my limited knowledge tempdb is used to hold worktables and so the CTE could be spilling results to the worktables when it needs to make way for new data coming in especially if the CTE has a DISTINCT, Group by or UNION in it.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1440838
Posted Wednesday, April 10, 2013 8:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 66, Visits: 437
A spool operator is used to save intermediate query results in a work table in tempdb. CTE will temporary store these results in these spools.
Although it will use tempdb, it will most probably use tempdb pages in memory anyway.

Post #1440843
Posted Wednesday, April 10, 2013 8:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
456789psw (4/10/2013)
CTE's seem to be using tempdb just as using a temp table or table variable.


CTEs are nothing more than a named subquery. They are not temp tables or table variables.

Just like a normal subquery, they can include spools or objects that spill to TempDB, if they do, they will use Tem[DB, if they don't, then they won't use TempDB. No different from if you created a view instead of using a CTE or used a subquery instead of the CTE.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1440856
Posted Wednesday, April 10, 2013 9:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
456789psw (4/10/2013)
If anyone is curious like most things it depends..

If the CTE is using recursion then its uses a spool operator therefore tempdb is used....therefore not much performance benefit vs just creating a temp table

If the CTE is non recursive then then the main qry is repeated and performance is that of using a cursor..the main anchor query is repeated therefor performance is no good!



http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/11/common-table-expressions-cte-s-how-it-works-how-recursion-works-using-with-adjacency-list.aspx



??????



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1440865
Posted Wednesday, April 10, 2013 9:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
456789psw (4/10/2013)
If the CTE is using recursion then its uses a spool operator therefore tempdb is used....therefore not much performance benefit vs just creating a temp table


How exactly would you replace a recursive CTE with a temp table?

If the CTE is non recursive then then the main qry is repeated and performance is that of using a cursor..the main anchor query is repeated therefor performance is no good!


If the CTE is non-recursive, it doesn't have an anchor query (anchor query is only for recursive CTEs), nor is there any query to repeat, I'm not sure what you're getting at here.

A non-recursive CTE is just a named subquery or temporary view, whichever you prefer to imagine it as, and performs as such.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1440911
Posted Wednesday, April 10, 2013 10:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:53 PM
Points: 387, Visits: 749
Hi Gail,
I originally was trying to find out if CTE's use TEMPDB? I was always told to think of them as you said.
"CTEs are nothing more than a named subquery."They are not temp tables or table variables."

But doing more reading, its seems just because they are named subquery does not mean a temp table(worktable) is not created in the background. It looks to me that table spools are used for recursive CTE there for a spool operator is used.

Thanks sorry for the confusion


http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx
Post #1440934
Posted Wednesday, April 10, 2013 10:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
456789psw (4/10/2013)
But doing more reading, its seems just because they are named subquery does not mean a temp table(worktable) is not created in the background.


True, but the same goes for any query whatsoever in SQL. A query with no CTE can have a worktable, a query with a subquery can have a worktable, a query that uses a view can have a worktable. Nothing specific to CTEs there. A query with a CTE does not automatically use TempDB any more than any other query in SQL.

It looks to me that table spools are used for recursive CTE there for a spool operator is used.


Yup, spools are used with recursive CTEs, as well as in other places as the optimiser decides (like some update queries)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1440936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse