当包含执行计划时，SSMS 中的 SQL Server 查询速度更快
在 SQL Server 2005 管理工作室中，我正在测试一个使用一些表变量的查询，其中一个表变量具有集群唯一约束。我注意到，当我包含实际执行计划来分析时，总执行时间实际上会减少很多。
这是什么原因，我是否应该只在包含执行计划的选项关闭时测试总执行时间。
谢谢！
In SQL Server 2005 management studio I am testing a query which uses some table variables, one of which has a clustered unique constraint on it. I am noticing that the total execution time actually goes down by a good amount when I include the actual execution plan to analyze it.
What is the reason for this, and should I only test for the total execution time when the option to include the execution plan is off.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
老实说，听起来有点奇怪。您确定您所看到的差异不是由缓存造成的吗？
我总是通过不包括执行计划来测试存储过程的性能，并且我会在每次运行之前清除缓存，以便进行公平的比较（在测试/开发数据库服务器上，而不是生产上）。
Sounds a bit odd to me tbh. Are you sure that the difference you're seeing is not down to caching?
I would always test the performance of a sproc by NOT including the execution plan, and I would clear the cache down before each run in order to have a fair comparison (on a test/dev db server, not production).
我刚刚遇到了同样的问题，在尝试了解包括执行计划如何或为何会加快速度之后，我现在得出的结论是 SSMS 只是误报了总执行时间。因此，您的问题的答案是，遗憾的是，它实际上根本没有运行得更快，并且是的，您应该在不包含执行计划的情况下执行计时。
当最初执行测试时，时间为几百毫秒，很难诊断，但是一旦我能够用较慢的查询重现问题，就变得更容易看到。下面的屏幕截图显示了存储过程的五次运行，该存储过程返回多个结果集。前三次运行没有返回执行计划，第四次和第五次则返回执行计划。尽管响应速度明显加快（约 200 毫秒，而不是约 5 秒），但查询实际上仍需要约 5 秒才能完成。我猜想客户端统计信息中存在一个错误，该错误对返回的执行计划进行计时，而不是在某些情况下对完整查询进行计时。
I just ran into this same issue, and after trying to understand how or why including the execution plan was speeding things up, I have now come to the conclusion that SSMS simply misreports the total execution time. The answer to your question is therefore that it isn't really running faster at all, sadly, and that yes, you should perform timings without the execution plan being included.
When initially performing tests where times were a few hundred milliseconds it was hard to diagnose, but once I was able to reproduce the issue with a slower query it became easier to see. In the screenshot below are five runs of a stored procedure which returns multiple result sets. The first three runs were without returning the execution plan, and the fourth and fifth were with. Despite the appearance of significantly faster responses (~200ms instead of ~5s), the query actually still took ~5s to complete. I guess there's a bug in the client statistics somewhere that is timing the execution plan being returned and not the full query under certain cirmcumstances.