Please see below query and execution plan - Any advice on how to get rid of SORT operator cost (nothing coming on missing indexes recommendation)

Execution Plan

  • 5
    could you please upload actual execution plan using pastetheplan.com ? – NikitaSerbskiy Nov 11 '20 at 17:26
  • If you can't upload the execution plan XML for some reason, it would be most helpful if you could provide table and index definitions for the tables involved in this query. – Josh Darnell Nov 11 '20 at 18:31

Your plan is an image and this is bad.

Your tables are heaps and this is worse.

With knowing nothing about uniqueness in your tables and deducing it only from table names, I suggest you to add PK clustered to your DimCorp:

alter table DimCorp
add constraint PK_DimCorp primary key clustered(DimCorpId)

And clustered (I don't think it is unique) on your FactCallDetail:

create clustered index ix_cl_DimCorpId on FactCallDetail(DimCorpId)
  • probably it would be better to use DayKeyId as clustered index for FactCallDetail – NikitaSerbskiy Nov 11 '20 at 18:33
  • He did not say anything about other queries on FactCallDetail, if great part of them joins to DimCorp clustered on DimCorpId would be better. If the most part joins on vw_dim_calendar DayKeyId is a better choice. – sepupic Nov 11 '20 at 18:41
  • 1
    I think this is a kind of "garbage in, garbage out" question. If the only thing that he wants is to get rid of sort operator, 2 clustered on the join column is what he wants. If he wanted general recommendations on his table structures he would describe both tables and queries involved – sepupic Nov 11 '20 at 18:46

As sepupic's answer points out, the reason you're getting a sort operator (and poor performance) is because you're missing clustered indexes on your tables, and therefore their underlying data structure is a heap.

In order for SQL to be able to "efficiently" join them together with the nested loop operator, it first sorts one of the heaps, which in this case has millions of rows causing the sort to be extra heavy of an operation.

If you add clustered indexes on the DimCorpId field in the FactCallDetail and DimCorp tables, then the data will be saved to disk pre-sorted by DimCorpId in a B-Tree data structure. Not only will this eliminate the sort operator from that part of the execution plan, it'll tremendously increase performance of the join between both tables on DimCorpId.

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