Query times out when executed from web, but super-fast when executed from SSMS
Asked Answered
W

9

51

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.

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:

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.

Willams answered 11/2, 2010 at 22:7 Comment(0)
W
46

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.

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.

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.

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.:

SET ARITHABORT ON; SELECT ...

For more info on why this issue occurs, see Erland Sommarskog's great article:

Wolgast answered 11/2, 2010 at 23:27 Comment(5)
The query is actually being generated by an O/R Mapper (LLBLGen) so I don't think I have much control over it. I did search on their forums for ARITHABORT with only a few hits, but it did lead me to turn ARITHABORT ON for the default setting on the server. Not my ideal solution, so I'm still hoping to understand why this setting caused this behavior. I didn't post the query because I don't think it's particularly relevant - I use the EXACT query (cut/paste) in SSMS that was used in the web, so it should be using the same plan, except, as you say, for the ARITHABORT setting. Continued.....Willams
I also had tried dumping the plan cache with DBCC FREEPROCCACHE but that didn't seem to have any effect - the web continued to be slow. After I set the ARITHABORT setting on the server to ON the problem cleared from the web. I actually was asking how I could affect the setting of the OTHER connection from my SSMS connection, but I don't think that is possible.Willams
No comment about using a stored procedure instead of queries generated by an O/R Mapper? Once the O/R Mapper has generated the query, you are certainly free to encapsulate that code in a stored procedure, and then call the stored procedure from your web site code. This just gives you more control over the query and the semantics surrounding the call itself.Wolgast
+1 I had the exact same thing. Timeout when run through web app, < 1 sec when run through SMSS. Adding SET ARITHABORT ON to the sproc definition fixed it. Thank you! PS What the hell does it mean?Modigliani
@Modigliani - Just to be clear ARITHABORT itself isn't the cause and adding SET ARITHABORT ON to the stored proc definition doesn't mean the problem won't re-occur. The issue is parameter sniffing.Wadi
T
18

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.

Update 2

SQL Server has had some improvements made to its query execution plan algorithms, and I find problems like this are increasingly rare on newer versions. If you are experiencing this problem, you might want to check the Compatibility Level setting on the database that you're executing against (not necessarily the one you're querying, but rather the default database--or "InitialCatalog"--for your connection). If you are stuck on an old compatibility level, you'll be using the old query execution plan generation techniques, which have a much higher chance of producing bad queries.

Tenenbaum answered 6/10, 2011 at 15:53 Comment(4)
I disagree the linked answer is any better than the answer already on this question.Wadi
@MartinSmith: I changed my answer to avoid calling the linked answer a "more full explanation," but I think providing an actual solution that fixes the problem is better than setting ARITHABORT ON (which is just a short-term hack really). The linked question also indicates that the problem can occur equally well on a stored procedure, so just using a stored procedure isn't necessarily going to fix things either.Tenenbaum
Flushing the entire procedure cache is not much of a solution TBH it is only a very expensive short term fix. And why on earth are you also running DBCC DROPCLEANBUFFERS? The correct thing to do is to investigate and fix the parameter sniffing problem which means the two execution plans are different. See http://www.sommarskog.se/query-plan-mysteries.htmlWadi
@MartinSmith: That looks like an interesting article. I'll have to read it over. Would you be willing to write up an answer for this question that talks about parameter sniffing, how it applies to queries that aren't part of stored procedures, and how to go about fixing a query that appears to be having this problem?Tenenbaum
P
5

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.

It's called parameter sniffing. You need to always localize the parameters in the stored proc to avoid this issue in the future.

I understand this might not be what the original poster wants but might help someone with the same issue.

Panne answered 17/8, 2012 at 15:35 Comment(1)
Here is a good article that explains parameter sniffing sommarskog.se/query-plan-mysteries.html#otherreasonsJackqueline
G
1

If using Entity Framework you must be aware that query parameters for string values are sent to database as nvarchar by default, if database column to compare is typed varchar, depending on your collation, query execution plan may require an "IMPLICIT CONVERSION" step, that forces a full scan. I could confirm it by looking in database monitoring in expensive queries option, which displays the execution plan.

Finally, a explanation on this behavior in this article: https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

Guillory answered 20/7, 2018 at 22:59 Comment(0)
C
1

Just using ARITHABORT wont solve the problem, especially if you use parameterised stored procedures.

Because parameterised stored procedures can cause "parameter sniffing", which uses cached query plan

So, before jumping into conclusion, please check below link.

the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server

Cognizance answered 18/6, 2020 at 12:10 Comment(2)
this question for 10 years ago and your answer is duplicate . please don't repeat duplicate answer. this question no need answer and this is a negative point for your activity in stack. i hope you wish the bestFollower
Thanks Amir, didnt check the date!Cognizance
A
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.

Argo answered 9/6, 2014 at 18:58 Comment(0)
L
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.

Lax answered 8/1, 2016 at 19:20 Comment(0)
U
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.

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.

Unseasonable answered 29/3, 2017 at 18:2 Comment(0)
A
0

I had a similar issue, and solved it by adding a "with (nolock)" hint to the stored procedure.

-Update-

After hitting the same issue weeks later with the same stored procedure, it appears that droping and recreating the stored procedure, or running the command sp_recompile 'sp_xxx' where sp_xxx is your strored procdure name works just as well. The act of adding the lock hint would have recompiled the stored procedure.

Avrilavrit answered 28/11, 2023 at 9:49 Comment(1)
That's not an uncommon thing to do, but you should know that this will cause the query to return data that has not been committed in transactions.Willams

© 2022 - 2024 — McMap. All rights reserved.