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.
- ITFredTry 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.|||
quote:
Originally posted by ITFred
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
I've been running into the exact same issue recently with my database. My c
onfiguration is a MS Access/VBA front end with MSSQL as the backend, and I s
ee 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 res
olve the random index botching which frequently occurs.
Drawbacks to this workaround are that it can't be easily used with queries w
hich are dynamically generated, and it also places queries in a second locat
ion 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 ar
e constructing the SQL string dynamically in VB, but maybe it can give you s
ome inspiration on what else you can try (i.e. dynamically call CREATE and D
ROP PROCEDURE statements to construct a customized stored procedure, run it,
and kill it when you're done, et. al.).
Hope this helps.
Jim|||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. dont use OR to link conditions
2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
3. dont use conditions that begin with wildcards (like %)
4. dont use string functions like "substring()"
If you have any of these in the where-clause, the optimizer cant 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.|||With respect to the issue brought up by A. Finkler above, that's actually a
separate cause of index failure through ODBC (though still noteworthy).
In my most recent situation, my query had the following construction:
UPDATE [Table A]
SET [Table A].[Column 1] = X
FROM
[Table A] INNER JOIN
([Table B] INNER JOIN
[Table C] ON [Table B].[Column G] = [Table C].[Column H])
ON [Table A].[Column P1] = [Table B].[Column P2] AND
[Table A].[Column Q1] = [Table B].[Column Q2] AND
[Table A].[Column R1] = [Table B].[Column R2] AND
[Table A].[Column S1] = [Table C].[Column S2]
Running this query through the Query Analyzer always produced an appropriate
indexing scheme that resulted in fast performance, but launching the exact
same query string through ODBC would randomly cause a table scan.
Jim|||"Cindy Gross (MSFT)" <cgross@.online.microsoft.com> wrote in message
news:bgpaEVoFEHA.3688@.cpmsftngxa06.phx.gbl...
> Try updating your statistics, preferably with the FULLSCAN option. You may
> also need to tune your indexes.
>
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......|||"A. Finkler" <finklerNO_SPAM@.gmx.de> wrote in message
news:94BE566F4finklerNOSPAMgmxde@.192.168.10.250...
> 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. dont use OR to link conditions
> 2. dont use negative conditions: <>, !>, !<, not exists, not in, not like
> 3. dont use conditions that begin with wildcards (like %)
> 4. dont use string functions like "substring()"
> If you have any of these in the where-clause, the optimizer cant 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.
>
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!
