Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!
Consider clustering on DateTime1.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!