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

by

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:

More Related Contents:

  1. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  2. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  3. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  4. Unique constraint on multiple columns
  5. The request failed or the service did not respond in a timely fashion?
  6. If condition in SQL Server 2008
  7. Comma separated results in SQL
  8. sql server 2008 management studio not checking the syntax of my query
  9. Understanding PIVOT function in T-SQL
  10. Which are more performant, CTE or temporary tables?
  11. Using RegEx in SQL Server
  12. What is the best way to auto-generate INSERT statements for a SQL Server table?
  13. Import Excel spreadsheet columns into SQL Server database
  14. SqlServer: Login failed for user
  15. SQL Server 2008 Empty String vs. Space
  16. How do I use the CONCAT function in SQL Server 2008 R2?
  17. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  18. How do I get date/time information from a TIMESTAMP column?
  19. How to add xml encoding to xml Output in SQL Server
  20. What happens to the primary key Id when it goes over the limit?
  21. SQL Recursive CTE: Finding objects linked by property
  22. Convert decimal time to hours and minutes
  23. Sort Bullets in Database
  24. FOR JSON PATH results in SSMS truncated to 2033 characters
  25. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  26. In SQL Server, how can I find everywhere a column is referenced?
  27. ‘Incorrect SET Options’ Error When Building Database Project
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. Why insert-select to variable table from XML variable so slow?
  30. Generate MD5 hash string with T-SQL

Leave a Comment