Timeouts with ADO.NET

[DeeAnn]

We've experienced problems whereby our database gets into some state
that casues queries to timeout when executed by ADO.NET, but they same
queries still run fine in tools like Query Analyzer. By running the
DBCC commands to update indexes, the problem goes away. However, if
indexes were the root causes, queries would not run well using any
tool. As such, we're trying to understand the underlying cause.
Behavior points to a SQL Server database state that doesn't work well
with ADO.NET. Does anyone have any insights to what is really going on
here? Similar experiences?

 

[William Ryan [eMVP]]

First, is it a connection timeout or a command timeout?

THere are many things that coudl make it appear to run slow in ADO.NET but
fine in QA. For instance, if I have a job that reindexes my tables and you
run your program, there's going to be a table lock and in all likelihood a
command timeout. Then 2 minutes later when the job is done, I run the same
query in QA and viola' it runs fine. Most folks dont' reindex their tables
in the middle of a production day, but there may well be locks on the db
(probably are) that are causing this. When you rerun the query at some
later poitn, the locks aren't still in place hence it performs better.

However, does it run slow and timeout via your program, run fine in QA each
time and then a DBCC fixes it? That sounds kind of suspicious. Which DBCC
commands are you running? Does it happen in a predictable manner (like
12:00 - 12:30 every day)? If so, I'd look to see if any jobs may be
causing it and /or if there is certainly queries holding locks that fire
more at that time period. Also, if it's predictable, you can use Profiler
and see what's happening quite easy. Otherwise, you may need to run
profiler for a longer period of time...

Now, if you need a bandaid solution...you can change the commandtimeout
(just reset the property in your code) to a higher interval, or set the
connectiontimeout if it's a connection timeout in your connectstring (since
it's readonly in the connection object, this is the only place you can
manipulate it).

Also, if it's connection timeouts, does it happen predictably? From the
sounds of it you are having command timeouts but you may be running out of
available connections if you are getting conection timeouts instead.

HTH,

Bill

 

[DeeAnn]

Actually, we've isolated the problem to a corrupted Query Plan.
Interestingly enough, the query plan created for an ODBC (Query
Analyzer) and OLEDB (driver we use for ADO.NET comms) are different.
So, when the plan for the OLEDB gets in a bad state, it causes SQL
Server to hang, while the stored procedure still executes fine in
Query Analyzer, because it's accessing a different plan. The
reindexing of tables cleared the problem becasue reindexing causes the
query plans to be cleared from the procedure cache. You can also use
the DBCC FREEPROCCACHE to clear the plan. If you use sp_recompile,
that will remove the plan as well but works in the same fashion as
creating the plan, i.e. ODBC invokation clears the ODBC plan, OLEDB
the OLEDB one. So, using sp_recompile through Query Analyzer will not
fix the problem.

What causes the plan to get in a bad (corrupted) state is
undetermined. This problem has only affected us twice in the last
four months, in the middle of a normal day. No special jobs running,
just normal data entry and data access stuff. While the underlying
root cause is still a mystery, at least we know what has gone wrong
and how to fix it quickly.