英文:
Sql query plan taking forever to create for simple query
问题
以下是翻译好的部分：
我有一个简单的查询，应该在1秒内完成。但它却花费了很长时间，这是意外的，而且是上周才开始发生的。我打开了查询计划，发现它花了10分钟来生成这个简单查询的查询计划。它只在9:30和9:29的时候运行，其中9:29的时间是用来生成查询计划的。它从记录不是太多的表中返回不到4,000行。
关于查询计划突然花费很长时间的原因有什么想法吗？
我不认为查询本身有关，因为它在几个不同的查询上都有相同的情况。计算机已经重新启动，所有补丁都已应用等等。我仍然看到为不仅仅是选择前1000行的查询计划花费了很长时间。似乎一旦它弄清楚了，随后的运行速度会变快，几乎正常。但任何新查询都比应该的时间长得多。我会感激有关要查看或如何修复的任何建议。
我尝试运行一个查询，并期望它花费1-5秒，但却花了10分钟。
编辑：
我正在与从一个表中选择前1000行进行比较。我有一个非常简单的查询，涉及到与其他两个小表的连接，花费了很长时间，所以我知道它不仅仅是一个复杂的查询。一旦执行计划计算出来，之后的时间就不会太长。
我还有一个稍微复杂一些的查询，上周在不到1秒内运行，但今天却花了9:30。执行计划在这里：https://www.brentozar.com/pastetheplan/?id=r1CxfNUeh。
当我重新运行它时，我无法重现相同的结果。一旦执行计划创建完成，它在3秒内运行。似乎它需要很长时间才能弄清楚执行计划。
英文:
I have a simple query that should take <= 1 second. It is taking forever, which is unexpected and just started happening last week. I turned on the query plan to find that it's taking that 10 minutes to come up with the query plan for this simple query. It just ran in 9:30 and 9:29 of that was the query plan. It returns <4k rows from tables with not too many more records than that.
Any ideas on what could be causing the query plan to suddenly take forever?
I don't think the query is relevant as it is doing the same thing on several different queries. The machine has been restarted, all patches applied, etc. I still am seeing insane time taken to create a query plan for what is not much more than a select top 1000. It seems that once it figures it out, it subsequently runs faster/ almost normal. But any new query again takes much longer than it should. I would appreciate any tips on what to look at or how to fix.
I tried to run a query and was expecting it to take 1-5 seconds and it's taking 10 minutes.
Edit:
I was comparing to a select top 1000 from a table. I had a very simple one with about 2 joins to 2 other small tables that was taking a a long time, so I knew it wasn't just an ugly query. Once the execution plan is calculated it doesn't take a ton of time thereafter.
I have a slightly more complex one that ran in <1 second last week and is taking 9:30 today. Execution plan here: https://www.brentozar.com/pastetheplan/?id=r1CxfNUeh.
When I re-run it I can't re-create the same results. It runs in 3 seconds once the execution plan has been created. It just seems to take forever for it to figure out the execution plan.
答案1
得分: 1
以下是翻译好的部分：
"你发布的执行计划没有显示长时间的编译时间，但计划是从缓存中检索的。
对于你的第三个查询，执行计划引用了表#Membership两次，表#Participation 13次，而这两个表的行数都为零（TableCardinality="0"）。
我最近遇到了一个问题，关于查询连接空表的长时间编译时间。似乎新的基数估算器在这种情况下可能会极其错误地估算行数，并因此花费太长时间来寻找更便宜的计划。
你可以尝试添加一个查询提示 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); 来让该查询使用传统的基数估算器。"
英文:
The execution plan you posted doesn't show long compile times but the plan was retrieved from cache.
The execution plan for your query 3 does reference a table, #Membership, twice and a table #Participation 13 times and both of these tables have zero rows (TableCardinality="0").
I did recently encounter an issue with long compile times for a query joining empty tables myself. It seems the new cardinality estimator can get the estimated row counts very wrong in this type of case and end up spending far too long trying to find cheaper plans as a result.
You can try adding a OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); query hint to get that query to use the legacy cardinality estimator
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论