在 SQL Server 执行计划中摆脱 Table Spool
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1931100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting rid of Table Spool in SQL Server Execution plan
提问by Abe Miessler
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?
我有一个查询，它创建了几个临时表，然后将数据插入其中。据我了解，这是 Table Spool 的潜在原因。当我查看我的执行计划时，我的大部分处理都花在了 Table Spool 上。有没有什么好的技术可以改善这些类型的性能问题？与临时表相比，使用视图或 CTE 是否会给我带来任何好处？
I also noticed that when I mouse over each table spool the output list is from the same temporary table.
我还注意到，当我将鼠标悬停在每个表线轴上时，输出列表来自同一个临时表。
回答by Remus Rusanu
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:
如果您不能向我们展示查询，我当然理解。但是真的很难猜测为什么在不知道任何细节的情况下引擎会优先考虑假脱机。我建议您阅读 Craig Freedman 的博客，他是查询优化器团队的一名工程师，并解释了 SQL 2005/2008 优化器的许多内部工作原理。以下是我可以很快找到的一些条目，它们以一种或另一种形式触及假脱机的主题：
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...
SQL 客户支持团队在http://blogs.msdn.com/psssql/ 上也有一个有趣的博客，
并且“sqltips”（关系引擎的团队博客）有一些技巧，例如查询计划中的假脱机运算符...
