相同的查询，不同的执行计划
我正在尝试为一个让我抓狂的问题找到解决方案...
我有一个查询在 QA 服务器中运行得非常快，但在生产中却非常慢。我意识到他们有不同的执行计划...所以我尝试重新编译，清理执行计划的缓存，更新统计信息，检查排序规则的类型...但我仍然找不到发生了什么
...运行查询的数据库完全相同，并且 SQL Server 也具有相同的配置。
任何新想法将不胜感激。
谢谢， 答：
我刚刚意识到 QA 服务器正在运行 SP3，而在生产环境中则运行 SP2。这对这个问题有什么影响吗？
I am trying to find a solution for a problem that is driving me mad...
I have a query which runs very fast in a QA Server but it is very slow in production. I realized that they have different execution plans... so I have try recompiling, cleanning the cache for the execution plans, update statistics, check the type of collation... but I still can't find what's going on...
The databases where the query is running are exactly the same and the SQL Servers have also the same configuration.
Any new ideas would be much appreciated.
Thanks,
A.
I just realised the the QA server is running SP3 and in production is SP2. Could this have any impact on this issue?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
生产服务器的数据库大小是否可能更大？该计划可能会有所不同，因为它基于其包含的数据的统计数据。
Is it possible the production server has a larger database size? The plan can be different because it is based on statistics on the data it contains.
我认为这可能是由于存在的数据量。我们曾经遇到过这样的情况：查询实际上在 QA 服务器中飞翔，但在生产中却非常慢。经过一段时间的思考后，我们发现 QA 服务器有 15K 行，而生产服务器有 150 万行。
华泰
I think it could be due to the volume of data present. It happened to us one time where the query literally flew in QA server but was incredibly slow in the production. After breaking our heads for a while we found out that QA server had 15K rows where as production had 1.5 million.
HTH
如果执行计划相同并且执行计划很慢，则可能是数据库负载、硬件、锁定/阻塞等原因。
但是，如果执行计划不同，则两个数据库之间就会出现不同的情况。两者的统计数据是否都是最新的，具有完全相同的模式、相同的索引、相似的行数、相同的 PK 和索引值分布等。QA 数据来自哪里，随机数据还是从生产中恢复？
If the execution plan was the same and one was slow, it would be database load, hardware, locking/blocking, etc.
However, if the execution plans are different something is different between the two databases. Are statistics up to date in both, have the exact same schemas, same indexes, similar number of rows, same distribution of PK and index values, etc. Where did the QA data come from, random data or is it a restore from production?
在生产环境中禁用并行查询执行:)
Disable parallel query execution on production :)
我最近遇到了这个问题，这就是我发现的。
我有两个数据库，它们本质上是彼此的副本。在一个版本上，TVF 运行需要 1 秒，而在另一个版本上运行需要 15 分钟。
底层SQL代码的执行计划有很大不同。我能够通过重建 TVF 依赖的一些索引来修复它。执行计划不一样，但确实改变了很多。执行时间也回落至一秒左右。
现在，两个版本的索引都高度碎片化。我的假设是历史统计或执行计划信息允许快速版本继续寻找最佳执行计划。
总结一下：即使索引具有相同的结构或相似的碎片率，也要确保查看索引的碎片情况。
I ran into this recently and here's what I found.
I had two databases that were essentially copies of each other. On one version a TVF was taking 1 second to run, while on the other version took 15 minutes to run.
The execution plans of the underlying SQL code were very different. I was able to fix it by rebuilding some indexes that the TVF relied on. The execution plans aren't the same, but it did change a lot. And the execution time is back down to around a second.
Now, both versions had indexes that were highly fragmented. My assumption is that historical statistic or execution plan information allowed the fast version to continue to find an optimal execution plan.
So to sum up: make sure you look at the fragmentation of your indexes even if they have the same structure or similar rates of fragmentation.