使用临时表查询的执行计划
我有一个执行类似以下操作的存储过程：
SELECT Id
INTO #temp
FROM table
WHERE ...
DELETE FROM #temp
INNER JOIN table2 ON a=b
WHERE ...
但它运行缓慢。当我尝试查看执行计划时，我无法查看执行计划，因为 SQL Server Management Studio 显示“Msg 208，Level 16，State 0，Line 31 Invalid object name '#temp'”。
有什么方法可以查看此类脚本的执行计划（或执行详细信息（不是计划））？
I've a stored procedure that does something like this:
SELECT Id
INTO #temp
FROM table
WHERE ...
DELETE FROM #temp
INNER JOIN table2 ON a=b
WHERE ...
But it's running slowly. When I try to view the Execution Plan I can't since the SQL Server Management Studio says "Msg 208, Level 16, State 0, Line 31 Invalid object name '#temp'."
Is there any way to view the execution plan (or the execution details (not the plan)) for such script?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
SET SHOWPLAN_TEXT ON（或单击“显示估计执行计划是 SSMS”）创建一个计划而不是执行 SQL因为这会创建 #temp
这将失败
所以解决方案是添加这到顶部（或通过 SSMS 执行等效操作）
SET SHOWPLAN_TEXT ON(or clicking on Display Estimated Execution Plan is SSMS) creates a plan instead of executing the SQLSince this creates the #temp
this will fail
So solution is to add this to the top (or do the equivalent via SSMS)
它应该让您看到第一条语句的估计执行计划。
对于第二条语句，您需要首先创建并填充
#temp表（填充很重要，因为它会向您显示将用于正确行数的计划）。（或者，如果您不是专门想查看估计的计划，您当然可以在 SSMS 中打开“包括实际执行计划”选项并运行整个过程）
It should let you see the estimated execution plan for the first statement fine.
For the second statement you will need to create and populate the
#temptable first (population is important so that it shows you the plan that will be used for the correct number of rows).(Or you can of course just turn on the "Include Actual Execution Plan" option in SSMS and run the whole thing if you aren't specifically trying to see the estimated plan)
用于生成估计执行计划的优化器不执行 T-SQL。它确实通过 algebrizer 运行语句，algebrizer 是前面概述的过程，负责验证数据库对象的名称。
由于查询尚未执行，临时表还不存在。这就是错误的原因。
解决此问题（并测试执行计划）的一种方法是在存储过程中创建 #temp 表并进行
insert into而不是select ... into。The optimizer, which is what is used to generate Estimated Execution plans, doesn't execute T-SQL. It does run the statements through the algebrizer , the process outlined earlier that is responsible for verifying the names of database objects.
Since the query has not yet been executed, the temporary table does not yet exist. This is the cause of the error.
One way to solve this (and test the execution plan) is to create the #temp table before and make a
insert intoinstead ofselect ... intoin the stored procedure.您可以在运行查询之前使用此语句
You can use this statement before running the query
我只是先选择“select into”子句，然后按 F5 执行它。这创建了 tmp 表。
然后我选择了其余的查询并单击“显示计划”（或按 Ctrl+L）。
I simply selected the "select into" clause first and executed it by pressing F5. This created the tmp table.
Then I selected rest of the queries and clicked "Display Plan" (or press Ctrl+L).