sql server odbc: I have been struggling with an MSSQL problem for a couple of weeks and
hope this is something someone else has already solved.

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 central table now has over 750K records in it.
Recently we started receiving occasional "Timeout Expired" errors in pretty
much random fashion --- 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
"feature" of MSSQL, but does not manifest on smaller tables because
the malfunction does not cause a timeout on smaller tables, just an
inexcusable and random 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 that this is a behavioral issue with
SQL2K that needs to be addressed.

- ITFred

Try updating your statistics, preferably with the FULLSCAN option. You may
also need to tune your indexes.

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

I've been running into the exact same issue recently with my database.
My configuration is a MS Access/VBA front end with MSSQL as the
backend, and I see similar behavior when I allow ODBC to pick the
indices to use.

One workaround I've found for this problem is to encapsulate the query
into a stored procedure, then call the stored procedure via ODBC in
pass-through mode with something like this:

EXECUTE sp_my_query

This takes ODBC out of the picture as far as indexing goes, and seems
to resolve the random index botching which frequently occurs.

Drawbacks to this workaround are that it can't be easily used with
queries which are dynamically generated, and it also places queries in
a second location outside of your client code where you might have a
debugging error.

Its not the most elegant solution, and may not work for you at all if
you are constructing the SQL string dynamically in VB, but maybe it can
give you some inspiration on what else you can try (i.e. dynamically
call CREATE and DROP PROCEDURE statements to construct a customized
stored procedure, run it, and kill it when you're done, et. al.).

Hope this helps.


Jim



--
Jim Tran
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message163387.html

I ran into the same problem recently, and the reason was the way the SQL
queries were written.
In order to have the optimzier process a query correctly, the where-clause
has to follow a few rules:
1. don´t use OR to link conditions
2. don´t use negative conditions: <>, !>, !<, not exists, not in, not like
3. don´t use conditions that begin with wildcards (like %)
4. don´t use string functions like "substring()"

If you have any of these in the where-clause, the optimizer can´t use
indexes and has to perform a table scan. This works as long as a table is
small enough, but once it has too many rows you run into timeouts.

hope this helps..
A.

[quoted text, click to view]

Because this application is mission-critical, we have been doing index
regeneration during slow times on weekends as a part of the standard
database maintenance setup. As a double-check I went in with STATS_DATE and
verified that the stats were in fact getting rebuilt at that time, and it
looks OK. I know it is not doing a FULLSCAN during these times, but
manually executing an update with FULLSCAN in the past does not seem to have
been of any help especially as the failure is intermittent: executing the
same query 20 times over the same ODBC link from the same machine might
produce one or two timeouts, and the rest are normal timings.

Working with the Execution Plan tool does not seem to address the problem,
since the optimizer always seems to behave itself when I run a query
(cut-and-pasted from the app) in the Query Analyzer. It always picks
suitable indices.

The same seems to apply to the Index Tuning Wizard -- I would assume that,
since it watches primarily for the occurrence of table scans, and those
scans only occur a percentage of the time for a given query, and since there
is already a suitable index in place which should have been used but wasn't,
it cannot make any suggestion. After all, the suggestion would be to create
the index that already exists!

Still very frustrating.......

[quoted text, click to view]

Useful suggestions to be sure. But I have spent a lot of time with the
Execution Plan tool trying to avoid such issues. Of course it is not always
possible to follow all of these rules (#1 and #2 are typically mutually
exclusive for certain queries) but I would still expect the optimizer to
behave consistently whether the query originates in the Query Analyzer or
over an ODBC connection. For that matter, it should be consistent if the
identical query is issued multiple times. As I mentioned, I have had some
success by using hints, but hints specifically cannot be used where
column-OR'ing is being used, and besides I consider it really bad form to
have the wording of the query defined by the availability and
characteristics of the indices --- makes for some serious code maintenance
issues.

Nevertheless I will continue to explore this.... thank you!