Query Times Out from Web App But Runs Fine from Management Studio

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

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.

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

Insert query times out in C# web app, runs fine from SQL Server Management Studio

ARITHABORT is often misdiagnosed as the cause.

In fact since version 2005 when ANSI_WARNINGS is on (as it is in both your connections) ARITHABORT is implicitly on anyway and this setting has no real effect.

However it does have a side effect. To allow for the cases where ANSI_WARNINGS is off the setting of ARITHABORT is used as one of the plan cache keys which means that sessions with different settings for this cannot share each other's plans.

The execution plan cached for your application cannot be reused when you run the query in SSMS except if they both have the same plan cache key so it gets a new plan compiled that "sniffs" the parameter values that are currently under test. The plan for your application was likely compiled for different parameter values. This issue is known as "parameter sniffing".

You can retrieve and compare both execution plans with something like

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%INSERT INTO GeocacherPoints (CacherID,RegionID,Board,Control,Points)%' 
and attribute='set_options' and text not like '%this query%'

The parameters section in the XML tells you the compile time value of the parameters.

See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries for more.

Execution Plans

You've supplied the estimated execution plans rather than the actual execution plans but it can be seen that only the first query plan is parameterised and it was compiled for the following values.

        <ParameterList>
          <ColumnReference Column="@Dec31" ParameterCompiledValue="'2013-12-31'" />
          <ColumnReference Column="@Jan1" ParameterCompiledValue="'2013-01-01'" />
          <ColumnReference Column="@Board" ParameterCompiledValue="(71)" />
        </ParameterList>

The second execution plan uses variables rather than parameters. That changes things significantly.

DECLARE @Board INT
DECLARE @Jan1 DATE
DECLARE @Dec31 DATE

SET @Board=71
SET @Jan1='January 1, 2013'
SET @Dec31='December 31, 2013'

INSERT INTO GeocacherPoints

SQL Server does not sniff the specific value of variables and generates a generic plan similar to using the OPTIMIZE FOR UNKNOWN hint. The estimated row counts in that plan are much higher than in the first plan.

You don't state which is the fast plan and which is the slow plan. If the one using variables is faster then likely you need to update statistics you may well be encountering the issue described here Statistics, row estimations and the ascending date column if the one using parameters is faster then you will be able to achieve variable sniffing and get it to take account of the actual variable values by using the OPTION (RECOMPILE) hint.

SQL query times out when run from C#, fast in SQL Server Management Studio

Three things to look at, in order of preference:

1. Avoid using the AddWithValue() function, as that can have catastrophic performance implications when ADO.Net guesses a column type wrong. Do what you must to be able to set an explicit DB type for each parameter
2. Look into OPTION RECOMPILE.
3. Look into OPTIMIZE FOR UNKNOWN. Do this only after the others have failed.

SQL query timing out in code but takes a few seconds in management studio

@MicrosoftDN. thanks for the link. its a bit weird but when i SET ARITHABORT OFF, the query still takes 3 secs in management studio but is actually a lot quicker in code and no timeouts happen. I am setting ARITHABORT OFF at the start of the sp and setting it ON at the end of sp.

MSDN says "setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application" but in this case whats happenning is that when I set it OFF it actually runs quicker than before in code. I dont know why this is happenning

SQL Server query runs more time from code than from Management Studio

To solve the crash we've increased the timeout in the IIS.
@Apostrofix has posted this link with an unexpected solution, that I wasn't familiar with before.

A quote from this post is:

The recommended fix for most situations (though it depends on the
structure of your query and sproc) is to NOT use your parameters
directly in your queries, but rather store them into local variables
and then use those variables in your queries.

Sql Timeout in Application but not in Sql Server Mgmt Studio

I met quite a few these kinds of time-out issues in web applications. Here are my findings.

1. SSMS connects to and executes against database directly via sql provider.
2. Web applications go through the whole Microsoft stack from IIS, business layers,..., then the sql provider to database. That is one reason.
3. This is the main reason. your C# code builds the params, and talks to database through a database schema/metadata/information inspection process, which takes considerable longer time. If you set debugger break point around the SqlDataReader, you will find out tons of metadata getting back from database.

My experience is that if it takes more than 1 second to finish the sql statements in SSMS, you likely get time-out issue in default setting of web applications. It happens more often in selects than inserts or updates.

My solutions:

1. Increase the time-out value in web.config;
2. Performance-tune your stored procedure using execution plan;
3. Avoid using SqlDataReader. Use SqlCommand for insert and update;
4. Return like top 100 of records instead of all the records if SqlDataReader must be used.

---

Related Topics