September 26, 2002 at 10:27 am
Hi everyone,
I was just wondering if someone could explain why I sometimes have to use Index hints for what appear to me to be relatively simple queries.
Example:
SELECT column1, column2, column3
FROM TABLEA
WHERE (datecolumn4 < 'yyyy-mm-dd' or datecolumn1 is null)
AND datecolumn5 > 'yyyy-mm-dd'
I have an Index built on datecolumn4 and datecolumn5 so I would suspect that the normal Index selection process would use this index but NO it decides to run off and do a Clustered Index Scan across 6.5 Million rows to find the correct rows when all I want is 10,500 rows.
If I put in the hint WITH (INDEX=36) where 36 happens to be the indexid from sysindexes
the query runs like the proverbial train.
The Index was newly created.
Any thoughts, and again thank you.
P.S.
Best SQL Server Site on the net!
Nigel Moore
======================
September 26, 2002 at 2:16 pm
Am running into the same problem -
I have a brand new database server that I am building to replace our sluggish production database server.
1. I restored a copy of the original prod db onto the new db
2. I moved all the indices to a different drive
3. I ran shrinkdatabase + Diskkeper Defrag + DBREINDEX
4. Everything looks great (no fragmentation), except for one pesky problem.
5. A simple query on an indexed field on a specific table is executed without the index by the optimizer.
6. I have looked at the Statistics, DBCC SHOWCONTIG, sp_helpndex and everything looks good. The only problem is that the same query is executed on the old database with an index scan and on the new database with a table scan.
6. If I force the index selection with a table hint, the query screams.
SETROWCOUNT 100
SELECTT1.SHIP_OU_ID
FROMS_ORDER T1 with (INDEX S_ORDER_ORDER_DT_X)),
S_ORDER_TYPE T2
WHERET1.ORDER_TYPE_ID = T2.ROW_ID
ANDT2.NAME = 'Sales Order'
ORDER BYT1.ORDER_DT
Without the hint, the query results in a table scan.
The best explanation I have heard so far is
1) Statistics are not updated (which in my
case they are)
2) The current workload plus state of the Statistics leads the optimizer to not use the index. In other words, relax things might get better under different workload conditions. My problem is that if I cannot get it to work flawlessly with one user, I am certainly not going to put a full production load.
3) A possible bug in MS SQL Server 7.0 sp3. I am currently installing SP4 to seeif this makes a difference.
The wierd thing is that the problem is always on a specific table. I have rebuilt the database from previous backups thrice - I had the problem the first time, did not have it the second time and it is back again on the the third try.
Any suggestions?
September 27, 2002 at 12:26 pm
Here is an update to my earlier post:
1. Completed upgrade to SP4 for Microsoft SQL7
2. Reindexed the tables via DBREINDEX
The Execution Plan is now correctly choosing the Index and all is well.
Sounds like a bug in Microsoft SQL Server sp3.
Has anyone run into this situation before with SP3 of Microsoft SQL Server 7.0?
September 30, 2002 at 11:51 pm
Maybe the parser and the bug are incorrectly assuming that datime1>’yyyy-mm-dd’ requires the datetime data type to be converted to a string (a varchar, for properly SQL speaking).
Would you mind try this an tell us if it works?
SELECT column1, column2, column3
FROM TABLEA
WHERE (datecolumn4 < convert(datetime, 'yyyy-mm-dd', 120) or datecolumn1 is null)
AND datecolumn5 > convert(datetime, 'yyyy-mm-dd', 120)
quote:
Hi everyone,I was just wondering if someone could explain why I sometimes have to use Index hints for what appear to me to be relatively simple queries.
Example:
SELECT column1, column2, column3
FROM TABLEA
WHERE (datecolumn4 < 'yyyy-mm-dd' or datecolumn1 is null)
AND datecolumn5 > 'yyyy-mm-dd'
I have an Index built on datecolumn4 and datecolumn5 so I would suspect that the normal Index selection process would use this index but NO it decides to run off and do a Clustered Index Scan across 6.5 Million rows to find the correct rows when all I want is 10,500 rows.
If I put in the hint WITH (INDEX=36) where 36 happens to be the indexid from sysindexes
the query runs like the proverbial train.
The Index was newly created.
Any thoughts, and again thank you.
P.S.
Best SQL Server Site on the net!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply