Worse query performance even after improving the query by making it to run in parallel mode

Question

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.

Answer 1

One thing stands out clearly for me - this warning in the XML query plan:

<Warnings>
    <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="N'Public'=CONVERT_IMPLICIT(nvarchar(20),[r].[SecurityLevel],0)"/>
</Warnings>

A little known speed killer in SQL Server is passing an nvarchar() parameter to use in an indexed seek against a varchar() indexed value. What SQL Server does in this case is to convert each value in the indexed field to an equivalent nvarchar() value, and compare them against the parameter.

Why doesn't it convert the nvarchar field to a varchar and use the index? Because all of varchar is a subset of nvarchar, but not all of nvarchar is a subset of varchar. Thus, to do the correct comparison, the varchar values must be converted to nvarchar values.

The effect of this is that the index is not used as an index (and may not be used at all, depending on the fields needed.) Instead, the index is scanned from beginning to end to identify the matches, with each index entry having the value converted, one by one. This is very slow.

There are two ways to handle this:

1. Change the field in the table and index to nvarchar
2. (Recommended) Change the parameter value to varchar before using it in the lookup.

The change can be handled in a number of ways. Either change the parameter type in the stored procedure being called, change the parameter type being passed by the application (There are articles on how to do this for Entity Framework calls to SQL), or add a CAST/CONVERT call in the code (which may be more trouble than it is worth.)