sql server odbc: Hi there,

I've got something kind of weird going on. I have a stored procedure that
normally takes less than one second to execute, but it has started taking
over 30 seconds from time to time.

The SP is called by an ASP page on a web server, running on a separate
machine. The database server is running SQL Server 2000. The ASP page
makes an ODBC connection, using TCP/IP and creates & sends the stored
procedure call.

From time to time, something goes haywire, and the ASP page returns
[Microsoft][ODBC SQL Server Driver]Timeout expired

Then, the ASP page will not work no matter what we do, even if we leave it a
day or two. (No locks on the database, sometimes not even any connections!)

The only way to fix it is to run the stored procedure through Query
Analyzer. It will take a little over 30 seconds, but finish successfully.
Then, if I run the exact same SP again, it will complete in 0 seconds, and
the web page will work fine again.

I've seen this before, but I cannot for the life of me remember what we did
to fix it. We tried changing the ODBC connection to Named Pipes instead of
TCP/IP but that didn't help.

Any suggestions?

Beverley

I am having the same issue and the solution provided by SQL crafter in the Sql Server Timeout expired post is not working. Could someone help us with this issue

Thanks

I have been struggling with a similar problem for a couple of weeks and came
to this group in search of a solution. Perhaps a pooling of information
will help.

In my situation, I have a VB interactive front-end on multiple machines
accessing a SQL2K database through ODBC over TCP/IP. It has been in service
for over 2 years and the master table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much the same manner as you describe --- a query that would normally take
1-2 seconds would suddenly take over 90 and crap out. I ran database
integrity checks, reconstructed the indexes, created additional indexes, ran
numerous original and reconfigured queries through the Index Analyzer. And
of course spent hours trying various searches in the MS KB. I have reached
the following conclusions:

The problem (in my case, at least) originates with the Query Optimizer. A
very specific query run through the Query Analyzer will *always* select an
appropriate index or mix of indexes. The *identical* query submitted
through ODBC, however, will sometimes select a completely inappropriate
index mix, or no index at all, wind up executing full-table scans or other
time-wasting substitutes, and time out. This effect, I suspect, is a
permanent feature of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable waste of time.

I proved this theory by adding index hints to my ODBC-originated queries
(which Books Online says should never be necessary), and observing the
performance stabilize. Problem is, I consider this a completely hokey
solution since if I ever decide to reconfigure my indexes, I will have
dozens of coordinating code changes to perform. Also, there are several
queries in which I cannot use index hints because the query requires
column-level 'OR'-ing with which hints are incompatible.

So I am still looking for a "proper" solution from one of you out there, or
at least an acknowledgment from MS that this is a behavioral issue with
SQL2K that needs to be addressed.

- Fred


[quoted text, click to view]