0

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

5
  • To make an informed guess at why the query planner is working this way, and therefore how to help it make a better choice for you, we'd need to see the queries and the indexes present, also full details from the plans would be helpful (getting the XML plan details and submitting to brentozar.com/pastetheplan is usually the preferred way to do that). It is also worth stating which version of SQL Server you are using as the planner has learned new tricks over time. Feb 3, 2021 at 8:31
  • @DavidSpillett I wish I could share the execution plan but so much information in it ... any way to send it to you directly?
    – MichaelD
    Feb 3, 2021 at 9:41
  • @MichaeID - I'm not offering private consultancy ATM, and taking things "offline" defeats the purpose of StackExchange being a public resource anyway. It is unlikely that the object names contained in a query plan are really commercially sensitive or otherwise need to be super-secret - if they are then perhaps repeat the tests and obtain plans from a copy of the DB with renamed objects? Feb 3, 2021 at 10:15
  • @DavidSpillett You are right. Question edited with reproduceable steps + query plan link
    – MichaelD
    Feb 3, 2021 at 11:15
  • Having a nonclustered column store index can be a good solution for query patterns like this. Feb 3, 2021 at 13:20

1 Answer 1

1

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.

3
  • That would mean one would have to have an index per permutation of the possible filter/sort columns including the sort order. This would result in massive amounts of indexes
    – MichaelD
    Feb 3, 2021 at 8:50
  • 1
    which is why offering limitless free sorting options is not always a great application design Feb 3, 2021 at 8:57
  • Even with a small amount of sorting options you quickly have a lot of permutations if you include the sort direction. There must be a better way :)
    – MichaelD
    Feb 3, 2021 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.