SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE and Spool operators


CTE and Spool operators

Author
Message
456789psw
456789psw
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 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
Say Hey Kid
Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)Say Hey Kid (676 reputation)

Group: General Forum Members
Points: 676 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
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 3232
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
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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 Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89073 Visits: 45284
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40248 Visits: 38567
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 Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89073 Visits: 45284
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
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 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 Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89073 Visits: 45284
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