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

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:

• Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

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.

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.