I am running SQL2000 SP1 on Compaq 8500 with 6 processors.
Here is a problem. I have the following statement:
INSERT MyTable (col1, col2, col3......col20)
SELECT col1, value2, col3.......value20
WHERE col1 = 0x000034235F343F
MyTable has about 2 mln. records in it. There is a non-clustered index defined on col1. The index is not defined as unique, but the values in the column are almost entirely unique. This index seem to be the obvious choice for this query, but SQL Server refuses to utilize it. Instead it does a clustered index scan (which is a table scan). The reason it does what it does, is because it incorrectly estimates the number of records returned by SELECT portion of the query. The estimated execution plan shows that SELECT will return over 64,000 records -- this is wrong! SELECT only returns a single row!!! When I run SELECT separately, it correctly chooses to use index defined on col1 (and correctly estimates that only 1 row will be returned). The indexes on this table are rebuild every night, so statistics should be fine. I even run DBCC SHOW_STATISTICS on this table/index and the results are correct: the index is very, very selective. Any ideas?
P.S. Adding query hint to the query above (to force it to use index on col1) makes it very fast (1 sec versus 72 sec with Clustered Index scan).