I'm trying to debug the source of a SQL timeout in a web application that I maintain. I have the source code of the C# code behind, so I know exactly what code is running. I have debugged the application right down to the line that executes the SQL code that times out, and I watch the query running in SQL profiler.
我正在调试我维护的web应用程序中的SQL超时源。我后面有c#代码的源代码，所以我确切地知道正在运行什么代码。我将应用程序调试到执行超时的SQL代码的行，并观察在SQL分析器中运行的查询。
When this query executes from the web, it times out after 30 seconds. However, when I cut/paste the query exactly as presented in Profiler, and I put it into SSMS and run it, it returns almost instantly. I have traced the problem to ARITHABORT being set to OFF in the connection that the web is using (that is, if I turn ARITHABORT OFF in the SSMS session, it runs for a long time, and if I turn it back ON then it runs very quickly). However, reading the description of ARITHABORT, it doesn't seem to apply... I'm only doing a simple SELECT, and there is NO arithmetic being performed at all.. just a single INNER JOIN with a WHERE condition:
当这个查询从web执行时，它在30秒后超时。但是，当我按照Profiler中提供的方式剪切/粘贴查询并将其放入SSMS并运行时，它几乎立即返回。我将问题追溯到在web正在使用的连接中设置为OFF的算术运算(也就是说，如果我在SSMS会话中关闭算术运算，它会运行很长一段时间，如果我重新打开它，它会运行得很快)。然而，读到对算术的描述，它似乎并不适用……我只是做了一个简单的选择，根本没有算术运算。只有一个内部连接，其条件为:
Why would ARITHABORT OFF be causing this behavior in this context?? Is there any way I can alter the ARITHABORT setting for that connection from SSMS? I'm using SQL Server 2008.
为什么算术运算会导致这种行为?有什么方法可以改变SSMS连接的算术设置吗?我正在使用SQL Server 2008。
6 个解决方案
#1
37
So your C# code is sending an ad hoc SQL query to SQL Server, using what method? Have you considered using a stored procedure? That would probably ensure the same performance (at least in the engine) regardless of who called it.
那么您的c#代码正在使用什么方法向SQL Server发送一个特别的SQL查询?您是否考虑过使用存储过程?这可能会确保相同的性能(至少在引擎中是如此)，而不管调用者是谁。
Why? The ARITHABORT setting is one of the things the optimizer looks at when it is determining how to execute your query (more specifically, for plan matching). It is possible that the plan in cache has the same setting as SSMS, so it uses the cached plan, but with the opposite setting your C# code is forcing a recompile (or perhaps you are hitting a really BAD plan in the cache), which can certainly hurt performance in a lot of cases.
为什么?当优化器决定如何执行查询时(更具体地说，为计划匹配)，算术运算设置是优化器要考虑的事情之一。缓存的计划可能有相同的设置作为一类,所以它使用缓存的计划,但相反的设置你的c#代码迫使重新编译(或也许你是达到一个非常糟糕的计划在缓存中),这当然可以伤害在很多情况下的表现。
If you are already calling a stored procedure (you didn't post your query, though I think you meant to), you can try adding OPTION (RECOMPILE) to the offending query (or queries) in the stored procedure. This will mean those statements will always recompile, but it could prevent the use of the bad plan you seem to be hitting. Another option is to make sure that when the stored procedure is compiled, the batch is executed with SET ARITHABORT ON.
如果您已经调用了一个存储过程(虽然我认为您没有发布查询)，那么您可以尝试向存储过程中的违规查询(或查询)添加选项(重新编译)。这将意味着这些语句将始终被重新编译，但是它可以防止使用您似乎正在攻击的坏计划。另一个选项是确保在编译存储过程时，批处理是使用SET math - abort执行的。
Finally, you seem to be asking how you can change the ARITHABORT setting in SSMS. I think what you meant to ask is how you can force the ARITHABORT setting in your code. If you decide to continue sending ad hoc SQL from your C# app, then of course you can send a command as text that has multiple statements separated by semi-colons, e.g.:
最后，您似乎想知道如何更改SSMS中的算术运算设置。我认为你想问的是如何在你的代码中强制执行算术设置。如果您决定继续从c#应用程序发送特定的SQL，那么当然您可以将一个命令作为文本发送，其中包含由分号分隔的多个语句，例如:
SET ARITHABORT ON; SELECT ...
For more info on why this issue occurs, see Erland Sommarskog's great article:
有关这个问题发生的原因的更多信息，请参阅Erland Sommarskog的伟大文章:
#2
12
This answer includes a way to resolve this issue:
这个答案包括一个解决这个问题的方法:
By running the following commands as administrator on the database all queries run as expected regardless of the ARITHABORT setting.
通过在数据库上作为管理员运行以下命令，所有查询都按预期运行，而不考虑算术设置。
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Update
It seems that most people end up having this problem occur very rarely, and the above technique is a decent one-time fix. But if a specific query exhibits this problem more than once, a more long-term solution to this problem would be to use Query Hints like OPTIMIZE FOR and OPTION(Recompile), as described in this article.
似乎大多数人最后都很少出现这个问题，而且上面的技术是一个不错的一次性修复。但是，如果一个特定的查询不止一次地显示这个问题，那么更长远的解决方案应该是使用查询提示，如本文中所描述的，如optimization FOR和OPTION(Recompile)。
#3
3
I've had this problem many times before but if you have a stored procedure with the same problem dropping and recreating the stored proc will solve the issue.
我以前多次遇到过这个问题，但是如果您有一个存储过程存在相同的问题，那么删除并重新创建存储的proc将解决这个问题。
It's called parameter sniffing. You need to always localize the parameters in the stored proc to avoid this issue in the future.
它被称为参数嗅探。您需要始终本地化存储的proc中的参数，以避免将来出现这个问题。
I understand this might not be what the original poster wants but might help someone with the same issue.
我知道这可能不是原来的海报想要的，但可能会帮助有同样问题的人。
#4
0
I had the same problem and it was fixed by executing procedure "WITH RECOMPILE". You can also try using parameter sniffing. My issue was related to SQL cache.
我遇到了同样的问题，通过执行“使用RECOMPILE”过程来修复它。您还可以尝试使用参数嗅探。我的问题与SQL缓存有关。
#5
0
If you can change your code to fix parameter sniffing optimize for unknown hint is your best option. If you cannot change your code the best option is exec sp_recompile 'name of proc' which will force only that one stored proc to get a new execution plan. Dropping and recreating a proc would have a similar effect but could cause errors if someone tries to execute the proc while you have it dropped. DBCC FREEPROCCACHE drops all your cached plans which can wreck havoc ok your system up to and including causing lots of timeouts in a heavy transactions production environment. Setting arithabort is not a solution to the problem but is a useful tool for discovering if parameter sniffing is the issue.
如果您可以更改代码以修复参数嗅探优化未知提示是最好的选择。如果您不能更改您的代码，最好的选项是exec sp_recompile 'name of proc'，它只会强制一个存储的proc获得一个新的执行计划。删除和重新创建一个proc会有类似的效果，但如果有人试图在您删除proc时执行该proc，则可能会导致错误。DBCC FREEPROCCACHE删除所有缓存的计划，这可能会破坏您的系统，甚至包括在繁重的事务生产环境中造成大量超时。设置math abort并不是解决问题的方法，但是它是发现参数嗅探是否存在问题的有用工具。
#6
0
I have the same problem when trying to call SP from SMSS it took 2 sec, while from the webapp (ASP.NET) it took about 3 min.
我也遇到过同样的问题，当我试图从SMSS调用SP时，需要2秒，而从webapp (ASP.NET)调用SP需要3分钟。
I've tried all suggested solutions sp_recompile, DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS but nothing fixed my problem, but when tried parameter sniffing it did the trick, and worked just fine.
我已经尝试了所有建议的解决方案sp_recompile、DBCC FREEPROCCACHE和DBCC DROPCLEANBUFFERS，但是没有任何东西可以解决我的问题，但是当尝试参数嗅探时，它就能解决问题，并且运行良好。
#1
37
So your C# code is sending an ad hoc SQL query to SQL Server, using what method? Have you considered using a stored procedure? That would probably ensure the same performance (at least in the engine) regardless of who called it.
那么您的c#代码正在使用什么方法向SQL Server发送一个特别的SQL查询?您是否考虑过使用存储过程?这可能会确保相同的性能(至少在引擎中是如此)，而不管调用者是谁。
Why? The ARITHABORT setting is one of the things the optimizer looks at when it is determining how to execute your query (more specifically, for plan matching). It is possible that the plan in cache has the same setting as SSMS, so it uses the cached plan, but with the opposite setting your C# code is forcing a recompile (or perhaps you are hitting a really BAD plan in the cache), which can certainly hurt performance in a lot of cases.
为什么?当优化器决定如何执行查询时(更具体地说，为计划匹配)，算术运算设置是优化器要考虑的事情之一。缓存的计划可能有相同的设置作为一类,所以它使用缓存的计划,但相反的设置你的c#代码迫使重新编译(或也许你是达到一个非常糟糕的计划在缓存中),这当然可以伤害在很多情况下的表现。
If you are already calling a stored procedure (you didn't post your query, though I think you meant to), you can try adding OPTION (RECOMPILE) to the offending query (or queries) in the stored procedure. This will mean those statements will always recompile, but it could prevent the use of the bad plan you seem to be hitting. Another option is to make sure that when the stored procedure is compiled, the batch is executed with SET ARITHABORT ON.
如果您已经调用了一个存储过程(虽然我认为您没有发布查询)，那么您可以尝试向存储过程中的违规查询(或查询)添加选项(重新编译)。这将意味着这些语句将始终被重新编译，但是它可以防止使用您似乎正在攻击的坏计划。另一个选项是确保在编译存储过程时，批处理是使用SET math - abort执行的。
Finally, you seem to be asking how you can change the ARITHABORT setting in SSMS. I think what you meant to ask is how you can force the ARITHABORT setting in your code. If you decide to continue sending ad hoc SQL from your C# app, then of course you can send a command as text that has multiple statements separated by semi-colons, e.g.:
最后，您似乎想知道如何更改SSMS中的算术运算设置。我认为你想问的是如何在你的代码中强制执行算术设置。如果您决定继续从c#应用程序发送特定的SQL，那么当然您可以将一个命令作为文本发送，其中包含由分号分隔的多个语句，例如:
SET ARITHABORT ON; SELECT ...
For more info on why this issue occurs, see Erland Sommarskog's great article:
有关这个问题发生的原因的更多信息，请参阅Erland Sommarskog的伟大文章:
#2
12
This answer includes a way to resolve this issue:
这个答案包括一个解决这个问题的方法:
By running the following commands as administrator on the database all queries run as expected regardless of the ARITHABORT setting.
通过在数据库上作为管理员运行以下命令，所有查询都按预期运行，而不考虑算术设置。
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Update
It seems that most people end up having this problem occur very rarely, and the above technique is a decent one-time fix. But if a specific query exhibits this problem more than once, a more long-term solution to this problem would be to use Query Hints like OPTIMIZE FOR and OPTION(Recompile), as described in this article.
似乎大多数人最后都很少出现这个问题，而且上面的技术是一个不错的一次性修复。但是，如果一个特定的查询不止一次地显示这个问题，那么更长远的解决方案应该是使用查询提示，如本文中所描述的，如optimization FOR和OPTION(Recompile)。
#3
3
I've had this problem many times before but if you have a stored procedure with the same problem dropping and recreating the stored proc will solve the issue.
我以前多次遇到过这个问题，但是如果您有一个存储过程存在相同的问题，那么删除并重新创建存储的proc将解决这个问题。
It's called parameter sniffing. You need to always localize the parameters in the stored proc to avoid this issue in the future.
它被称为参数嗅探。您需要始终本地化存储的proc中的参数，以避免将来出现这个问题。
I understand this might not be what the original poster wants but might help someone with the same issue.
我知道这可能不是原来的海报想要的，但可能会帮助有同样问题的人。
#4
0
I had the same problem and it was fixed by executing procedure "WITH RECOMPILE". You can also try using parameter sniffing. My issue was related to SQL cache.
我遇到了同样的问题，通过执行“使用RECOMPILE”过程来修复它。您还可以尝试使用参数嗅探。我的问题与SQL缓存有关。
#5
0
If you can change your code to fix parameter sniffing optimize for unknown hint is your best option. If you cannot change your code the best option is exec sp_recompile 'name of proc' which will force only that one stored proc to get a new execution plan. Dropping and recreating a proc would have a similar effect but could cause errors if someone tries to execute the proc while you have it dropped. DBCC FREEPROCCACHE drops all your cached plans which can wreck havoc ok your system up to and including causing lots of timeouts in a heavy transactions production environment. Setting arithabort is not a solution to the problem but is a useful tool for discovering if parameter sniffing is the issue.
如果您可以更改代码以修复参数嗅探优化未知提示是最好的选择。如果您不能更改您的代码，最好的选项是exec sp_recompile 'name of proc'，它只会强制一个存储的proc获得一个新的执行计划。删除和重新创建一个proc会有类似的效果，但如果有人试图在您删除proc时执行该proc，则可能会导致错误。DBCC FREEPROCCACHE删除所有缓存的计划，这可能会破坏您的系统，甚至包括在繁重的事务生产环境中造成大量超时。设置math abort并不是解决问题的方法，但是它是发现参数嗅探是否存在问题的有用工具。
#6
0
I have the same problem when trying to call SP from SMSS it took 2 sec, while from the webapp (ASP.NET) it took about 3 min.
我也遇到过同样的问题，当我试图从SMSS调用SP时，需要2秒，而从webapp (ASP.NET)调用SP需要3分钟。
I've tried all suggested solutions sp_recompile, DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS but nothing fixed my problem, but when tried parameter sniffing it did the trick, and worked just fine.
我已经尝试了所有建议的解决方案sp_recompile、DBCC FREEPROCCACHE和DBCC DROPCLEANBUFFERS，但是没有任何东西可以解决我的问题，但是当尝试参数嗅探时，它就能解决问题，并且运行良好。