question

ultrawidelife avatar image
ultrawidelife asked

simple select - performance problem

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.

performance-tuningsql2016
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
SSGC avatar image
SSGC answered

Try diff query like sort it first to enforce optimizer use index?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.