SQL Server: Query times out when executed from web, but super-fast when executed from SSMS

Question

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.

Answer 1

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 WITH RECOMPILE to the stored procedure definition. This will mean the procedure 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 ...