I'm calling off a stored procedure from C# (using ADO.net). Sometimes it
returns data but sometimes it throws a timeout exception. I'm running SQL
Server Profiler on the server, and RPC:Completed in each case, even when it
times out at the client. When it times out, Duration is fairly long (around
400,000). I can't seem to see any patterns, like it always times out on
these parameters but not those, etc...

What can I do to find out what's going on here?

One more thing, when I run this sproc from Management Studio it NEVER fails.


"0to60" <holeshot60_nospam (AT) yahoo (DOT) com> wrote

On Sat, 24 Jan 2009 13:09:48 -0600, 0to60 wrote:

Hi 0to60,

Since you never have problems running from Management Studio and the
problems when running from ADO.net are timeouts, it's obvious what the
direct problem is. Management Studio has no default timeout setting, and
ADO.net has. Change your C# code to set a longer timeout or disable it
completely and THAT problem will be gone.

The next problem will then be why the procedure sometimes runs much
slower than other times. My suspicion is parameter sniffing (google it
to get lots of info). But without knowing the code or your tables, I
really can't give any more guidance. Check www.aspfaq.com/5006 if you
need more help.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

"Hugo Kornelis" <hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote

Oddly enough, when I run the sproc in Management Studio it returns in a
normal amount of time. But when this problem happens from ADO.net, it feels
like the sproc is taking forever.

I've gone so far as: 1) run the sproc from ADO.net. 2) when I've looked at
the hourglass for a while, I assume that the problem is happening and I 3)
quickly run the sproc from Management Studio.
When I do this, Mangement Studio runs it just fine. The data is returned in
the normal amount of time. This is WHILE my C# app is still displaying an
hourglass. Then a little while later, I get the timeout error. Its like,
running it from ADO.net is doing something differently from running it in
Management Studio.

0to60 (holeshot60_nospam (AT) yahoo (DOT) com) writes:
First, I agree with Hugo: set the command timeout to 0 in your client
code, unless you know that you absolutely cannot tolerate execution times
longer a certain limit.

Next, there are two possible reasons for this seeminlyly mysterious
difference between your application and Mgmt Studio. The first is blocking.
When the procedure seems to be taking a long time, run sp_who from
Mgmt Studio, and keep an eye of the Blk column. If there is a non-zero
value in that column, that is the spid that is blocking the process on
your other column. If this is the case, I would assume that this is
blocking within your application.

The other reason for the difference has to do with how the query cache
works. This can be tested by issueing this command before you test
the procedure in Mgmt Studio:

SET ARITHABORT OFF

If the procedure now runs for a long time (but eventually completes),
the reason for poor performance is a less appropriate query plan. Note
that ARITHABORT as such most likely has nothing to do with it. It is
just that different settings in ARITHABORT will result in different
cache entries, and Mgmt Studio will then get a plan suited for the
parameters you use in that call.

Whatever the reason is of these two, you are likely to have more
questions, but ask these when you have tested the above first. :-)


--
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/prodinf...ons/books.mspx