I have a table with 30 million rows - 135 GB
there are one million rows for each last 30 days (datetime column). table has only last 30 days data.
i have a clustered index on identity int. and a non clustered index on datetime column
select top 10 * from table where <datetime column>= '2019-11-20 00:00:00.000' -- takes 75 sec , uses clustered index scan
select top 10 * from table
with (index(non clustered index)
where <datetime column>='2019-11-20 00:00:00.000' -- takes 1 sec, uses nc + key lookup
I don't get it why the optimizer doesn't pick non clustered index automatically?
one reason maybe since number of rows returned by <datetime column>= '2019-11-20' is larger percent of total table so optimizer thinks its better to scan clustered index but I have top 10 also.
I have already rebuilt the indexes/ updated stats with full scan.
Any idea how to make optimizer use non clustered index? or approach this problem.
