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.
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