logo标签列表

从Web执行查询超时，但在SSMS中执行非常快

sql-serversql-server-2008ssmsquery-performancearithabort
51
我正在尝试调试一个我维护的Web应用程序中SQL超时的源代码。我有C#代码的源代码，所以我知道正在运行哪些代码。我已经对应用程序进行了调试，直到执行SQL代码的行，并且我可以在SQL分析器中看到查询正在运行。
当此查询从Web执行时，它会在30秒后超时。但是，当我完全按照Profiler中呈现的方式剪切/粘贴查询，并将其放入SSMS中运行时，它几乎立即返回。我已经追踪到问题在于连接中设置了ARITHABORT为OFF（也就是说，如果我在SSMS会话中关闭ARITHABORT，则它会运行很长时间，如果我将其打开，则它会非常快地运行）。但是，阅读ARITHABORT的说明，它似乎不适用...... 我只是简单地使用WHERE条件执行一次单个内部连接，根本没有执行任何算术运算：
为什么在这种情况下ARITHABORT OFF会导致此行为？是否有任何方法可以从SSMS更改该连接的ARITHABORT设置？我正在使用SQL Server 2008。
9个回答
46

你的C#代码是通过什么方法向SQL Server发送一个即席SQL查询的？你考虑过使用存储过程吗？这可能会确保无论谁调用它，性能都相同（至少在引擎中）。

为什么呢？当优化器确定如何执行查询时（更具体地说，是用于计划匹配），ARITHABORT设置是优化器查看的内容之一。缓存中的计划可能与SSMS具有相同的设置，因此它使用缓存的计划，但使用相反的设置，则您的C#代码正在强制重新编译（或者您正在命中缓存中的一个非常糟糕的计划），这在许多情况下肯定会影响性能。

如果您已经调用了存储过程（尽管我认为您想要发布查询），则可以尝试在存储过程中为有问题的查询（或查询）添加OPTION（RECOMPILE）。这将意味着这些语句将始终重新编译，但它可以防止您似乎遇到的不良计划的使用。另一个选择是确保在编译存储过程时，使用SET ARITHABORT ON来执行批处理。

最后，您似乎在问如何更改SSMS中的ARITHABORT设置。我认为您想要询问如何在代码中强制ARITHABORT设置。如果您决定继续从C#应用程序发送即席SQL，则当然可以将多个语句作为文本发送一个命令，以分号分隔的方式，例如：

SET ARITHABORT ON; SELECT ...

想要了解更多关于这个问题出现的原因，可以参考Erland Sommarskog写的一篇非常好的文章：

查询实际上是由一个O/R映射器（LLBLGen）生成的，所以我认为我对它没有太多控制权。我在他们的论坛上搜索了ARITHABORT，只有几个命中结果，但这确实让我将服务器上的默认设置ARITHABORT打开。这不是我的理想解决方案，所以我仍然希望了解为什么这个设置会导致这种行为。我没有发布查询，因为我认为它并不特别相关 - 我在SSMS中使用了与Web中使用的完全相同的查询（剪切/粘贴），因此应该使用相同的计划，除了像你说的那样，ARITHABORT设置。继续..... - Michael Bray
2
我也尝试使用DBCC FREEPROCCACHE清除计划缓存，但似乎没有任何效果 - 网站仍然很慢。在我将服务器上的ARITHABORT设置为ON后，网站的问题得到了解决。实际上，我想知道如何影响我的SSMS连接之外的其他连接的设置，但我认为这是不可能的。 - Michael Bray
1
没有评论使用存储过程代替 O/R Mapper 生成的查询吗？一旦 O/R Mapper 生成了查询，您肯定可以将该代码封装在存储过程中，然后从网站代码中调用存储过程。这只是为您提供了更多控制查询和调用本身语义的方式。 - Aaron Bertrand
4
+1 我也遇到了同样的问题。在 Web 应用程序中运行时超时，在 SMSS 中运行时小于 1 秒。将 SET ARITHABORT ON 添加到 sproc 定义中即可解决。谢谢！顺便问一句，这是什么意思？ - David
4
为了明确一点，ARITHABORT本身不是原因，将SET ARITHABORT ON添加到存储过程定义中也不能保证问题不会再次发生。问题出在参数嗅探上。 - Martin Smith
18

这个答案提供了一种解决此问题的方法：

通过在数据库上以管理员身份运行以下命令，所有查询都可以按预期运行，无论ARITHABORT设置如何。

 DBCC DROPCLEANBUFFERS
 DBCC FREEPROCCACHE

更新

看起来大多数人很少遇到这个问题，上述技术是一个不错的一次性解决方案。但如果某个查询多次出现此问题，则更长期的解决方案是使用类似 OPTIMIZE FOR 和 OPTION(Recompile) 的查询提示，如 这篇文章中所述。

更新2

SQL Server 对其查询执行计划算法进行了一些改进，我发现类似于此类问题在新版本上越来越少见。如果您遇到此问题，您可能需要检查正在执行的数据库（不一定是您查询的数据库，而是连接的默认数据库或“InitialCatalog”）的兼容级别设置。如果您停留在旧的兼容级别上，则将使用旧的查询执行计划生成技术，这种技术产生错误查询的概率更高。

1
我不同意链接的答案比已经在这个问题上的答案更好。 - Martin Smith
1
@MartinSmith：我修改了我的答案，避免称链接的答案为“更全面的解释”，但我认为提供一个实际解决问题的解决方案比设置ARITHABORT ON（这只是一个短期的hack）更好。链接的问题还表明，该问题同样可能发生在存储过程上，因此仅使用存储过程也不一定能解决问题。 - StriplingWarrior
5
清空整个过程缓存并不是一个很好的解决方案，说实话这只是一个非常昂贵的短期修复。而且你为什么还要运行 DBCC DROPCLEANBUFFERS？正确的做法是要调查和修复参数嗅探问题，这意味着两个执行计划是不同的。请参阅http://www.sommarskog.se/query-plan-mysteries.html。 - Martin Smith
@MartinSmith：看起来是一篇有趣的文章。我得仔细阅读一下。你能否为这个问题写一个答案，谈谈参数嗅探，它如何适用于不属于存储过程的查询，以及如何修复似乎存在此问题的查询？ - StriplingWarrior
5

我以前遇到过这个问题，但如果您有一个具有相同问题的存储过程，删除并重新创建存储过程将解决该问题。

这被称为参数嗅探。 您需要始终在存储过程中本地化参数，以避免将来出现此问题。

我知道这可能不是原始帖子想要的，但可能会帮助遇到相同问题的人。

2
这是一篇很好的文章，解释了参数嗅探http://www.sommarskog.se/query-plan-mysteries.html#otherreasons - weilin8
1
如果使用Entity Framework，您必须知道字符串值的查询参数默认情况下会作为nvarchar发送到数据库，如果要比较的数据库列类型是varchar，根据排序规则，查询执行计划可能需要一个"IMPLICIT CONVERSION"步骤，这将强制进行全扫描。我可以通过在昂贵的查询选项中查看数据库监视来确认它，该选项显示执行计划。
最后，在这篇文章中解释了这种行为： https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/
1

仅使用ARITHABORT无法解决问题，尤其是当您使用参数化存储过程时。

因为参数化存储过程可能会导致“参数嗅探”，从而使用缓存的查询计划。

所以，在得出结论之前，请检查下面的链接。

大象和老鼠或SQL Server中的参数嗅探

这个问题是10年前的，你的回答是重复的。请不要重复重复的答案。这个问题不需要回答，这会对你在Stack上的活动造成负面影响。我希望你一切顺利。 - Amirhossein
1
谢谢Amir，我没注意日期！ - Prashanth Shivasubramani
0
我遇到了类似的问题，并通过在存储过程中添加一个 "with (nolock)" 提示来解决它。
0

我有同样的问题，通过执行"WITH RECOMPILE"过程进行了修复。你也可以尝试使用参数嗅探。我的问题与SQL缓存有关。

0

如果您可以更改代码以修复参数嗅探优化未知提示是最佳选择。如果您无法更改代码，则最佳选择是执行exec sp_recompile 'proc名称'，这将强制仅为该存储过程获取新的执行计划。删除并重新创建过程会产生类似的效果，但如果有人尝试在您删除过程时执行过程可能会导致错误。DBCC FREEPROCCACHE会删除所有缓存的计划，这可能会对您的系统造成严重影响，甚至包括在繁忙的事务生产环境中导致大量超时。设置arithabort不是解决问题的方法，但是它是发现参数嗅探问题的有用工具。

0

当我尝试从SMSS调用SP时，遇到了同样的问题，需要2秒钟，而从Web应用程序（ASP.NET）需要大约3分钟。

我尝试了所有建议的解决方案sp_recompile，DBCC FREEPROCCACHE和DBCC DROPCLEANBUFFERS，但没有解决我的问题，但是当尝试参数嗅探时，它起作用了，并且正常工作。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接