Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE and Spool operators


CTE and Spool operators

Author
Message
456789psw
456789psw
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 831
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?
Arjun Sivadasan
Arjun Sivadasan
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 976
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.
456789psw
456789psw
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 831
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
sharky
sharky
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 441
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 37978
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



??????

Cool
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)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


456789psw
456789psw
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 831
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search