I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i see that query runs in parallel(wanted it that way as it is a complex query and serial execution is very slow,so purposefully removed serial forcing components).
I also noticed some keylook up
and i kind of ignored it earlier because the cost of that operation looked small.
Now the query runs slow and and i created covering indexes to remove key lookup,but even after that the plan uses the old index and is not using covering indexes.Not sure why?
Here is the executing plan from Paste the plan
.
https://www.brentozar.com/pastetheplan/?id=Bk1YECnOH
Any findings from the plan,which can help to improve the performance is highly appreciated.
I checked statistics and it is updated today morning ,so it is not the cause. I am using SQL Server 2016 and have 105GB of RAM allocated to SQL Server out of the 128GB.It is enough i believe. I have 10GB of tempdb space allocated.
I don't see any other bad signs other than keylook up and i am not an expert in dicing and slicing execution plan.So any help is greatly appreciated.