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

Question

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](int) 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 top(10) ID
 FROM test 
 WHERE Created >= '2018-08-02 22:49:01.577'
 AND Type IN (1, 4)
 ORDER BY ID-- index seek of ncl1

Answer 1

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?

• DMDM: Thanks you. The trace 4138 worked. Is there other way to force the sql server to use that index. Sorry havent read eveything about rowgoals and planning to do today.

Answer 2

In general and most basic term, If cost of Index scan is less than Index Seek then Optimiser may decide to Index Scan.

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

In this case there lot of records which is greater than '2018-08-02 22:49:01' and only 10 record is desire.

so If Optimiser was to retrieve record from Index then cost would be more.

So it Index Scan and this "Good Enough Plan Found"

SELECT top(10) ID
 FROM test 
 WHERE Created >= '2018-08-02 22:49:01.577'
 AND Type IN (1, 4)
 ORDER BY ID-- index seek of ncl1

Here there are very less record which is greater than '2018-08-02 22:49:01.577' So out of these 10 record is desire which Optimiser go for Index Seek.

Here you will notice that Index Seek operator return all rows which is greater than '2018-08-02 22:49:01.577'.Actual number of rows are all rows which qualify greater than '2018-08-02 22:49:01.577'. Then Sort operator slice it to 10.

Therefore it may not be "Good Enough Plan Found".