Incorrect Query Plan | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Incorrect Query Plan

Hi, I’ve got a table with 50 milion rows. It has a composite non-clustered index on a smalldatetime field and a smallint field. When I SELECT from the table specifying a date in the WHERE clause, it does a table scan rather than using the index. If I supply a hint to force it to use the index the query is much quicker. There are no other indexes on the table. The indexes are rebuilt daily (DBREINDEX). The SELECT with the hint takes 8 secs, without takes 50 secs. Does not anyone know why a table scan is being used.
The table does not have cluster index? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Changed index to clustered – query plan now uses this in the SELECT – takes 8 secs. Doesn’t explain why the index was not used when it was non-clustered and instead did a table scan.
]]>