0

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.

  • 1
    Could you show what scalarstring40 until 44 are in your execution plans? They are on the filter operator & the three index scans with residual predicate. An example of this scan + predicate is: schema 1, object 4, index 5 It is harder to give advice on anonimized plans without the query itself – Randi Vertongen Oct 10 at 16:52
  • 1
    Multiple "Repartition Streams" suggests that your partitioning scheme is not optimal (at least for this query), and parallelism may be hurting instead of helping performance. – mustaccio Oct 10 at 17:32
  • I know that parallelism is helping only here because in serial the query returns result in 40+ seconds and once converted to parallel it executes in 10+ seconds.How can i solve the Repartition Streams issue you found out? – user9516827 Oct 10 at 17:53
  • The rightmost part of the plan is suspicious, there are two loop joins between Merge Interval and Index Seek. This is dynamic seek pattern. Dynamic seek cannot utilize parallelism for the Index Seek operation. Likely you have non-sargable predicates on datetime2(7) column. SQL Server tries to make them sargable, but in the way that is not very efficient in the parallel plan. Try to make them sargable explicitly if possible to make those seeks parallel. – i-one Oct 10 at 21:14
2

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

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.