Poor performance by LINQ to SQL generated query execution plan

Question

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve. The query is generated from the .Net application using LINQ to SQL. I can see large sort operation in the plan and i have indexes for SORT columns.Also i see OR in where conditions. Can anyone please direct me where the issue is?I am still learning on how to debug from the execution plan.

Execution Plan

Even after hard-coding the linq parameters it takes 23 seconds to run. Since it is generating from .net application,i have less control over the query and i tried adding different indexes(some covering indexes to avoid key lookups) etc.,but of no help till now. Appreciate any kind of help in providing more insight into the plan on problem areas.

Answer 1

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.

I don't see any indication that there is likely to be useful optimizations that involve just indexes and statistics. The entire query isn't included in the plan, but it's a monstrous, frightful thing, and it needs to be rewritten.

The main problem with the query is that it's a TOP 100 query, but is not able to cheaply identify the 100 rows, and then perform the rest of the JOINs. Instead it creates a 7M row result, sorts it and takes the first 100 rows.

This really needs to be addressed by whoever wrote the query (here the application developer). You can perhaps help by helping them articulate what the intent of the query is, and suggesting a better way.