1

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.

2

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.

  • I understand your suggestion.The query is generating from the .net application(Linq to SQL)and i am not sure whether we have a lot of control over it.Since i am able to hack the code,i am not sure,if i can avoid sort operation or add any kind of filtering before rendering the result. – user9516827 Jun 14 '18 at 22:10

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.