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.)

Answer 2

Please pay attention to the following images

One of the most important things to be careful is that you must be one of your estimated number of row with your actual number of rows Otherwise, in this case, the implementation plan will not properly select the right operators. For example, your estimate will return the value of one, while your actual statistics take a lot of the row. This can be considered because of the failure to update your statistics.

This is well mentioned in the following book : SQL Server Execution Plans

A difference this small is not worth worrying about, but a larger discrepancy can be an indication that the optimizer has used inaccurate estimations of the number of rows that will need to be processed when selecting the plan, which could result in a suboptimal plan choice. There are many possible causes of this. For example, perhaps the optimizer had to generate a plan for a query containing a Predicate on a column with missing or stale statistics, or the optimizer may have reused a plan where the data volume or distribution in a column has changed significantly since the statistics were last created or updated. Alternatively, the data distribution in a column may be very non-uniform, making accurate cardinality estimations difficult, or the query may contain logic that defeats accurate estimations. Parameter sniffing may have occurred, resulting in a plan generated for an input parameter value with an estimated row count that is atypical of the row counts for subsequent input values.

reference:SQL Server Execution Plans, Third Edition, by Grant Fritchey

also :

Actual Number of Rows – the true number of rows returned according to runtime statistics. The availability of this value in actual plans is the biggest difference between these and cached plans (or estimated plans). Look out for big differences between this value and the estimated value.

Estimated Number of Rows – calculated based on the statistics available to the optimizer for the table or index in question. These are useful for comparing to the Actual Number of Rows.

But there is very important point is workload distribution between threads. In fact, you need to check that this split between the threads is almost identical.

Uneven data distribution and outdated statistics are common causes of uneven workload distribution between threads. this Figure shows how workload distribution changes after a statistics update on one of the tables. The left side shows the distribution before the statistics update, and the right side shows it after the update.

reference: Pro SQL Server Internals