Clustered Index Scan on partitioned table even when there is covering index

I have a partitioned table which is partitioned based on col1 int. I also have a covering index for the query that I am trying to troubleshoot.

https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm

Above is the plan

Left to its wishes SQL Server decides to do a clustered index scan of the entire table which is obviously slow. If I force the index (like in the plan above) the query runs quickly.

What magic logic does SQL Server use to decide that the covered index is not useful? I am not sure if top/orderby and rowgoal has anything to do with it.

My table structure is

Create table object2(col1 int, col3 datetime, col4 int, col5, col6 etc) clusterd on col1

nonclustered non aligned index is on col3,col4 (col1 is clustered so its included in nonclust)

SELECT top(?) Object1.Column1
    FROM Object2 Object1 WITH (NOLOCK,index(Column2))
    WHERE  Object1.Column3 >= ?
    AND Object1.Column4 IN (?)
    ORDER BY Object1.Column1

Edit added Repo

```
CREATE PARTITION FUNCTION PFtest AS RANGE RIGHT FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000)
GO
CREATE PARTITION SCHEME [PStest] AS PARTITION [PFtest] all TO ([PRIMARY]);
GO

create table test([ID] [int] IDENTITY(1,1) NOT NULL primary key clustered,[Created] [datetime] NULL,[Type] [int] NULL,text1 varchar(10),text2 varchar(20))
on pstest(id)
set nocount on

declare @a int =1
declare @type int
while 1=1
begin
if @a%30 =0
insert into test (Created, Type, text1, text2) select getdate(),4,'four','four'
else
insert into test (Created, Type, text1, text2) select getdate(),1,'one','one'
set @a=@a+1
end
create nonclustered index ncl1 on test(created, type)

select min(created),max(created) from test
--2018-08-02 22:46:40.187 2018-08-02 22:49:01.577
SELECT top(10) ID
FROM test
WHERE Created >= '2018-08-02 22:49:01'
AND Type IN (1, 4)
ORDER BY ID -- clustered index scan

SELECT

Yes it is most likely a row goal issue.

SQL Server assesses that scanning the clustered index (to avoid a sort) will find the first ? matches quicker (at which point the scan would stop) than searching the nonclustered index for matches, sorting those, then returning the top ? matches.

On your version of SQL Server, you can test if setting a row goal is the cause by running the query with OPTION (QUERYTRACEON 4138) to disable row goals.

Related Q & A:

• Why is my index not being used in a SELECT TOP?

• How (and why) does TOP impact an execution plan?

• Why is this query not using my nonclustered index, and how can I make it?

StackExchange Database Administrators Q#213896, answer score: 6