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!"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)
>
Try with @.StartDate and @.EndDate replaced with hard-coded date literals.
SQL Server is reluctant to commit to using a date index for a date range
with bind variables specifying the ends, because the using the index might
be pretty cheap when @.StartDate and @.EndDate are close together, but
horribly expensive when @.StartDate and @.EndDate are far apart. So this
might be one of those rare times when query hints are desirable.
You can also change the indexing scheme to make this query more attractive.
Options include changing the clustered index or adding
(integer3,integer4,integer5) to the non-clustered index to make it a
covering index for this query.
David|||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!