I've never run into this before and have burned two days trying to
figure it out.

I have a relatively performant store procedure that runs in less than
a second when called from Management Studio but when run from a typed
dataset in an asp.net page it sits for 30 seconds and then throws a
SQL timeout exception.

An SQL trace of the both look exactly the same -- even when I through
every option SQL Profiler has to offer.

I am using the same account in both instances and as best I can tell
the connection properties are set the same.

There are numerous writes to the tables that are in the query, but the
reads are all dirty (NOLOCK clause). SP_WHO2 doesn't show any
blocking issues. Also server resource utilization is low. Besides --
if it were a utilization of contention issue, it would affect both
instances.

I am really at a loss...

Exactly the same issue we are facing. In Management studio it executes in
less than a second (over internet) and from our .NET code, it throws up a
timeout error (local network).

Please let me know if you come accross a solution that works. Thanks.

--
Sriram

"Jason Wilson" <wilsonj (AT) ausrad (DOT) com> wrote

Sriram (sriram.nandakumar (AT) gmail (DOT) com) writes:
This is not an uncommon situations, and the seemling bizarre difference
usually has its explanations.

To start with, in SSMS run this command "SET ARITHABORT OFF", and then
run your stored procedure again. Chances are now that it will now run
slowly.

It is not that this command itself has any importance, but different
settings of ARITHABORT gives results in different cache entries. .Net
applications usually run with ARITHABORT OFF, SSMS with the same setting
ON.

Each time SQL Server runs a stored procedure for which there is no
cached plan, the optimizer has to build a plan, and it sniffs the
current input parameters to determine what is a good plan. If these
parameters are atypical, this can lead to bad performance for further
executions.

Assuming the the execution in SSMS was slow with ARITHABORT off, view
the exeuction plan, and open it in XML. At the bottom of the plan
you find the sniffed parameter values. This can give you some ideas
where they come from. If you now say:

EXEC sp_recompile yourprocedure

I would expect the .Net application to run quickly.

I should add that there are more possible explanations, and if the
above does not work out, please give more details on what you actually
are doing, and also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Jason Wilson (wilsonj (AT) ausrad (DOT) com) writes:
Having seen the procedure, I am not surprised that it runs slowly. I am
more surprised that it ever runs fast. A quick fix may be to add
OPTION (RECOMPILE) at the end, so that you get a new query plan
every time. With that query, there is no cached plan that will be
good for you.

You may also be interesting in my article about dynamic search
condition where I discuss several options to slay this kind of beasts.
http://www.sommarskog.se/dyn-search-2005.html



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

As probably mentioned, google (errrr "bing") "Parameter Sniffing" "Sql
Server".

........

One workaround (OPTION RECOMPILE) was mentioned. But I didn't see this
other work around.

create procedure dbo.uspThisRocks
( @SomeParameter int )
AS


declare @SomeParameterLocal int
select @SomeParameterLocal = @SomeParameter


GO


Then refer to the @SomeParameterLocal in your code instead of the variable
name in the proc definition.


You should do the little bit of research (via the google search mentioned
above) to figure out why that might work.




"Jason Wilson" <wilsonj (AT) ausrad (DOT) com> wrote