Sql-server – Execution plan push sort operator to avoid too many PK lookups

execution-planindexperformance-tuningsql server

For a query like this:

select top 40 ColumnNotInIdexCausingPKLookup from table 
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex

Any way to push up a sort/filter operator, so the PK lookup isn't that heavy (40 records vs 4.797)? All columns that are being used in the filter/sort are in the index "included column" list so there is enough information in the index to not have 4797 PK lookups

+- 20K reads
Expensive execution plan

If i change the index so the columns are in de index fields instead of the "include" fields then the execution plan does filter first, using the index itself. However, that only works with that specific set of orderby/filterby. Which changes if the user filters/sorts on another field.

+- 700 reads

Fast Execution plan

SQL Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) – 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 18363: )

Steps to reproduce

create database ExecutionPlanTestDB
go
use ExecutionPlanTestDB

create table ExecutionPlanTest (
    id int not null identity(1,1) primary key,
    ColumnInIndex bigint,
    ColumnInIndex_Include bigint,
    ColumnNotInIndex bigint,
)

insert into ExecutionPlanTest(ColumnInIndex,ColumnInIndex_Include,ColumnNotInIndex)
select
    top 10000000
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id,
    o1.object_id+o2.object_id+o3.object_id+o4.object_id
    from sys.objects o1
inner join sys.objects o2 on 1=1 and o2.object_id<100
inner join sys.objects o3 on 1=1 and o3.object_id<100
inner join sys.objects o4 on 1=1 and o4.object_id<100
where o1.object_id<100

create index IX_EPT_ColumnInIndex on ExecutionPlanTest(ColumnInIndex) include(ColumnInIndex_Include)

-- execute with execution plan from here
select top 5 ColumnNotInIndex from ExecutionPlanTest
where ColumnInIndex=307 --should have +-2k results
order by ColumnInIndex_Include

Query plan: https://www.brentozar.com/pastetheplan/?id=S1odVbOgu

One way to solve this seems to be changing it to this structure. Although I would rather not convert all the queries like this. It seems to be only logical that sql server first sorts/filters/tops the data in the index itself before sending it further in the execution plan?

select ColumnNotInIdexCausingPKLookup  from table where tableID in (
    select top 40 tableid from table 
    where ColumnIncludedInIndex=A
    order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
)

This results in the better plan (+-700 reads now instead of 20k)
enter image description here

Best Answer

I think you answered your own question, SQL Server will only use an index for sorting if the columns are in the index definition keys (the sorted part) not in the included column list (as then the values are not sorted). If your users can choose one of multiple sorts you will need multiple supporting indexes, and some carefully written queries.

Related Question