ADO.NET and timeouts with SQL Server

[DeeAnn]

We've experienced timeout problems with ADO.NET and SQL Server. The
queries being executed via ADO.NET run fine (1-2 seconds) in tools
like Query Analyzer. When DBCC commands are run to re-index tables,
the problem in ADO.NET execution goes away. However, if indexing was
really the root cause, we'd expect to see delayed response times in
all methods used to execute the query, not just ADO.NET.

Does anyone have any insight into what state SQL Server my get in that
would cause this type of behavior? It seems that the comms between
ADO.NET and SQL Server are causing SQL Server to do additional work
then solely executing a query, and that may account for the observed
behavior.

 

[William Ryan [eMVP]]

See my answer above, but I don't think it's ADO.NET and SQL Server per se.
In addition to what I mention above, you need to isolate when it's
happening. The DBCC issue may just be coincidence...for instance, how long
does it stay 'fixed' after reindexing?

 

[William \(Bill\) Vaughn]

The way that QA handles resultsets is different than the way they're handled in ADO.NET. For example, population might be deferred in the QA case and not in Fill but it is deferred when using a DataReader.

I would take a look a the Profiler to monitor what's getting sent to the SS to execute. If rendering fixes the problem then I would suspect how the queries are being executed or the index structure itself. However, there are other issues here too. The problem is, when you execute a query or SP, (especially a SP) the compiled query plan is cached and reused for subsequent executions. This plan is constructed and optimized based on how the indexes are laid out (statistics) and other factors. These conditions might NOT be true when the query is executed an instant later or the parameter values that made sense for the optimized plan might not work for subsequent executions. There are several approaches to addressing these issues:
a.. Forcing the SP to be recompiled each time (pay a bit more to recompile but possibly reap big benefits by using an optimized query).
b.. Simplifying the SP so that the query plan is correct regardless of the input parameters
c.. Using server-side compiled executables. This is dangerous, but sometimes can yield better performance in these situations.
There are other issues to be concerned with here, but this is the essence of the approach. I'll be talking about this issue at the upcoming DevConnections/SQLConnections conference in Orlando in April.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________