Sql-server – Clustered Index Scan on partitioned table even when there is covering index

optimizationperformancequery-performancesql serversql server 2014

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

Best Answer

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: