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.


Show quoteHide quote

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.

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@yahoo.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. :-)

SQL Maintenance Plans and Atomic Backups of a Set of Databases
No trace of maintenance plan checkdb in sql server error log?!?
Backup stratagy Overview
SQL Management Studio and Small Business Server
SQL Server 2005 Database Tuning Advisor
automatic bak filename generation
Calling sqlcmd in a loop?
DTE recommends an index that already exists but with a different .
Help with SQL Profiler
Management Studio: Omit "Set" when modifying a proc