SQL times out in web, but not in Management Studio

SQL times out in web, but not in Management Studio
With ADO.NET, SQL calls that run fast in management studio may run slow or time out when called using the .NET ADO commands.
This is because ADO adds a bunch of options to the call that are not in Management Studio.
In addition, the syntax can be different, or wrapped in an 'exec' call that ends up hitting a different cached plan than you are getting.

ADO options:
Here is an example of options ADO.NET might be adding:
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
This results in ADO giving you a different execution plan than from SSMS.

Parameter Sniffing can also be an issue. Change the name of your parameters or use Option (Recompile) if calling the same query with different param values is causing bad execution plans to be cached. I

f in LINQ, see the other article 'Support Option Recompile on LINQ' ....
Or, if using Entity Framework v 6.0 +, see the other article 'Support Option Recompile in Entity Framework' ..

A simpler way to solve this is to disable parameter sniffing at the level of the entire database using this command: DBCC TRACEON (4136,-1); this will affect every query.

If you are calling a stored procedure, you can put the fix in the procedure itself by adding
Option (Recompile)
or
OPTION (OPTIMIZE FOR UNKNOWN)
after the order by clause.

Created By: amos 3/18/2013 5:45:51 PM
Updated: 5/12/2022 2:40:54 PM