16

I have a query that creates several temporary tables and then inserts data into them. From what I understand this is a potential cause of Table Spool. When I look at my execution plan the bulk of my processing is spent on Table Spool. Are there any good techniques for improving these types of performance problems? Would using a view or a CTE offer me any benefits over the temp tables?

I also noticed that when I mouse over each table spool the output list is from the same temporary table.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • 1
    Spooling doesn't have anything to do with _your_ temporary tables. Spooling is effectively _internal_ temporary tables used by the query planner. In fact it's quit e possible that if you use a few more summary temporary tables, it won't spool anymore. Wait... this was asked in 2009?! – Nick.Mc Mar 31 '20 at 09:03
  • 5
    @Nick.McDermaid - finally I can tune my query! My boss is going to be STOKED – Abe Miessler Apr 01 '20 at 20:14
  • 6
    Haha. But has it finished running yet? – Nick.Mc Apr 01 '20 at 21:44

1 Answers1

23

Well, with the information you gave I can tell only that: the query optimizer has chosen the best possible plan. It uses table spools to speed up execution. Alternatives that don't use table spools would be even slower.

How about showing the query, the table(s) schema and cardinality, and the plan.

Update

I certainly understand if you cannot show us the query. But is really hard to guess why the spooling is preffered by the engine whithout knowing any specifics. I recommend you go over Craig Freedman's blog, he is an engineer in the query optimizer team and has explained a lot of the inner workings of SQL 2005/2008 optimizer. Here are some entries I could quickly find that touch the topic of spooling in one form or another:

SQL customer support team also has an interesting blog at http://blogs.msdn.com/psssql/
And 'sqltips' (the relational engine's team blog) has some tips, like Spool operators in query plan...

Dave Black
  • 7,305
  • 2
  • 52
  • 41
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Unfortunately I think my work wouldn't like me sharing that kind of info. My understanding of spooling is limited, but I thought that spooling was used when you refer back to a temporary table. Wouldn't putting that information into an indexed view get us away from that without adversely affecting performance? – Abe Miessler Dec 18 '09 at 23:56