Query times out from web app but runs fine from management studio

by

This is what I’ve learned so far from my research.

.NET sends in connection settings that are not the same as what you get when you log in to management studio. Here is what you see if you sniff the connection with Sql Profiler:

-- network protocol: TCP/IP  
set quoted_identifier off  
set arithabort off  
set numeric_roundabort off  
set ansi_warnings on  
set ansi_padding on  
set ansi_nulls off  
set concat_null_yields_null on  
set cursor_close_on_commit off  
set implicit_transactions off  
set language us_english  
set dateformat mdy  
set datefirst 7  
set transaction isolation level read committed

I am now pasting those setting in above every query that I run when logged in to sql server, to make sure the settings are the same.

For this case, I tried each setting individually, after disconnecting and reconnecting, and found that changing arithabort from off to on reduced the problem query from 90 seconds to 1 second.

The most probable explanation is related to parameter sniffing, which is a technique Sql Server uses to pick what it thinks is the most effective query plan. When you change one of the connection settings, the query optimizer might choose a different plan, and in this case, it apparently chose a bad one.

But I’m not totally convinced of this. I have tried comparing the actual query plans after changing this setting and I have yet to see the diff show any changes.

Is there something else about the arithabort setting that might cause a query to run slowly in some cases?

The solution seemed simple: Just put set arithabort on into the top of the stored procedure. But this could lead to the opposite problem: change the query parameters and suddenly it runs faster with ‘off’ than ‘on’.

For the time being I am running the procedure ‘with recompile’ to make sure the plan gets regenerated each time. It’s Ok for this particular report, since it takes maybe a second to recompile, and this isn’t too noticeable on a report that takes 1-10 seconds to return (it’s a monster).

But it’s not an option for other queries that run much more frequently and need to return as quickly as possible, in just a few milliseconds.

More Related Contents:

  1. Insert an image in a column of a table that already has 5 columns
  2. Insert results of a stored procedure into a temporary table
  3. How to SELECT FROM stored procedure
  4. SET versus SELECT when assigning variables?
  5. How important is the order of columns in indexes?
  6. Is there any difference between IS NULL and =NULL
  7. sql query to return differences between two tables
  8. SQL Server – transactions roll back on error?
  9. How to print VARCHAR(MAX) using Print Statement?
  10. SQL statement to select all rows from previous day
  11. Concatenate values based on ID
  12. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  13. Splitting delimited values in a SQL column into multiple rows
  14. Insert Picture into SQL Server 2005 Image Field using only SQL
  15. Execute Stored Procedure from a Function
  16. varbinary to string on SQL Server
  17. SQL : in clause in stored procedure:how to pass values
  18. SQL GROUP BY CASE statement with aggregate function
  19. separate comma separated values and store in table in sql server
  20. Best way to do nested case statement logic in SQL Server
  21. Restrict an SQL Server connection to a specific IP address
  22. SQL Server 2005 ROW_NUMBER() without ORDER BY
  23. Can an INNER JOIN offer better performance than EXISTS
  24. SQL server identity column values start at 0 instead of 1
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. How to compare dates in SQL Server
  27. How do I expand comma separated values into separate rows using SQL Server 2005?
  28. Is there a quick way to check if ANY column is NULL?
  29. Why do SQL Server Scalar-valued functions get slower?
  30. SQL query for parent-child chain

Leave a Comment