ADO.NET中SQL Server查询速度比SSMS慢

Question

我从一个网站上查询数据，需要15-30秒，但是同样的查询在SQL Server Management Studio中只需要0.5秒。使用SQL Profiler没有发现任何锁定问题，我也无法通过SSMS手动重现延迟。一周前，我分离并重新连接了数据库，这似乎神奇地解决了问题。今天问题再次出现时，我尝试仅仅重建索引。这也解决了问题。然而，我不认为这必然是索引问题，因为据我所知，简单的分离/连接操作不会自动重建索引。

有什么想法是什么原因导致了这种延迟吗？我的第一个想法是可能是存储过程参数嗅探（该存储过程运行CTE，如果这很重要）导致了错误的查询计划，这可以解释问题的间歇性。既然分离/重新连接和索引重建都应该理论上使缓存的查询计划失效，这是有道理的，但我不确定如何验证这一点。此外，为什么直接从SQL Profiler复制具有完全相同参数的相同查询，在SSMS中手动运行时没有表现出相同的延迟呢？

您有什么想法吗？

- Chris

---

SSMS是否传递了一些提示或不同的连接字符串，而ADO.net的方式则不同？ - shahkalpesh

是的，我正在使用不同的凭据进行连接。不幸的是，目前问题已经消失了，所以在问题再次出现之前，我无法使用相同的连接设置进行测试。 - Chris

@Chris：你能发一篇自己的答案，分享一下你在这个问题上的发现吗？ - shahkalpesh

Answer 1

我知道我对这个话题的发言已经很晚了，但我想发布一个解决方案，因为我遇到了类似的问题。简而言之，在我的存储过程开头添加SET ARITHABORT ON命令，使网站查询性能与从SQL Server工具中看到的性能相一致。当你从QA或SSMS运行查询时，此选项通常被设置在连接上（你可以更改该选项，但它是默认值）。

在我的情况下，我有大约15个不同的存储过程在相当大的数据集（10到100万行）上进行数学聚合（SUM、COUNT、AVG、STDEV）-添加SET ARITHABORT ON选项将它们从每个运行3-5秒移动到20-30毫秒。

希望这能帮助其他人。

- Jeremy A

---

+1 因为你的回答让我找到了解决问题的方法。我的问题是，当从 ADO 调用 sproc 时速度很慢，但在 SSMS 中很快。将 sproc 更改为包括 SET ARITHABORT ON 使其加速。（另一方面，这可能只是强制重新编译并使缓存的执行计划无效。）我还在这个网址上发现了一个有趣的讨论：http://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query我认为底线是 SSMS 获取了不同的执行计划，因为它默认设置该选项，而 ADO 没有。 - Holistic Developer

Answer 2

如果一个糟糕的计划被缓存，那么在使用相同参数运行完全相同的查询时，应该从SSMS中使用相同的糟糕计划。

找到根本原因是没有更好的解决方案。尝试查看和修改各种设置以期望解决问题永远不会让你确信它已经真正解决了。此外，下一次系统可能会出现不同的问题，你会认为这个相同的问题重新浮现并应用错误的解决方案。

最好的尝试是捕获错误执行计划。 Showplan XML Event Class Profiler事件是您的朋友，您可以获取ADO.Net调用的计划。这是一个非常重的事件，所以您应该附加Profiler并仅在问题表现出来时，在短时间内捕获它。

查询IO统计信息也可能会有所帮助。RPC：已完成和SQL：批处理已完成事件都包括读取和写入，因此您可以比较ADO.Net调用与SSMS调用执行的逻辑IO量。对于完全相同的查询和参数，巨大的差异表明存在不同的计划。sys.dm_exec_query_stats是另一个调查途径。您可以在其中找到查询计划并检查执行统计信息。

所有这些都应该有助于确定问题是否是一个糟糕的计划或其他问题，作为开始。

- Remus Rusanu

---

“坏”查询的读取次数明显高于来自SSMS的查询的读取次数。我也认为如果输入完全相同的查询，则SSMS应该使用相同的查询计划，但是是否存在这种情况？ - Chris

如果任何@参数类型不同或连接设置不同，则无法重用计划，SSMS将编译自己的计划。你的查询中有任何@变量吗？ - Remus Rusanu

我敢打赌是连接设置的问题。我使用Windows身份验证登录SSMS，而ASP.NET使用SQL用户。现在这个问题解决了。接下来，我们要抓住执行计划不良的问题.. :( - Chris

现在SQL Server也具有使用“USE PLAN”提示锁定计划的能力，请参见http://msdn.microsoft.com/en-us/library/ms186954.aspx。 - Remus Rusanu

Answer 3

我一直遇到同样的问题。唯一解决方法是设置ARITHABORT ON，但不幸的是，当再次出现问题时，我必须将ARITHABORT OFF。

我不知道ARITHABORT与此有什么关系，但它有效。我已经遇到这个问题超过2年了，仍然没有解决方案。我正在使用的数据库超过300GB，所以可能是一个大小问题...

最接近解决这个问题的方法是从早期的帖子中得到的：Google Groups post 如果您已经完全解决了这个问题，请告诉我，因为这非常令人沮丧！

- Eli Perpinyal

Answer 4

你的 ADO.NET 查询是否可能在系统忙于其他事情后运行，导致它需要的数据不再在 RAM 中？而当你在 SSMS 上测试时，它是存在的吗？

在运行查询之前，你可以通过从 SSMS 运行以下两个命令来检查：

CHECKPOINT
DBCC DROPCLEANBUFFERS

如果这导致SSMS查询运行缓慢，那么您可以在ADO.NET方面使用一些技巧来帮助它更快地运行。

- RickNZ

Answer 5

Simon Sabin在“查询计划出错时”（http://sqlbits.com/Sessions/Event5/When_a_query_plan_goes_wrong）上有一个很棒的会话，讨论了如何通过使用各种“优化为”提示等来解决此问题，以帮助过程生成一致的计划而不使用默认参数嗅探。

然而，我遇到了一个问题，即在非过程化的查询中，SSMS计划和ASP计划完全相同-聚集索引/表扫描-但ASP查询需要3分钟以上，而不是1秒钟。（在这种情况下，表扫描恰好是获取结果的合适答案。）

有人能解释一下吗？

- Will