How to reduce high cost index seek and hash match inner join when analysis execution plan issue?

Question

0.00/5 (No votes)

See more:

I work on sql server 2014 I have issue on my execution plan below

https://www.brentozar.com/pastetheplan/?id=SJCzRrmht

index seek is high 57 what this mean and how to solve it

and how mean hash match inner join 40 AND HOW TO REDUCE
also compute scalar 1 so what this mean also

can any one help me

table script as below :

What I have tried:

SQL

CREATE TABLE [Parts].[Nop_PartsFamilyAttribute](
     [PartFamilyAttributeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartFamilyID] [int] NOT NULL,
     [Key] [int] NOT NULL,
     [Value] [nvarchar](2200) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
  CONSTRAINT [PK_Nop_PartsFamilyAttribute30] PRIMARY KEY CLUSTERED 
 (
     [PartFamilyAttributeID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer],
  CONSTRAINT [UK_PartFamilyID_Key30] UNIQUE NONCLUSTERED 
 (
     [PartFamilyID] ASC,
     [Key] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 ) ON [Customer]

what I try

I create index

create index pf_idx on Parts.Nop_PartsFamilyAttribute([Key]) include ([Value])
but nothing affect index seek

Posted 5-Jan-22 6:18am

ahmed_sa

Updated 5-Jan-22 10:29am

v2

Add a Solution

Comments

ahmed_sa 5-Jan-22 13:12pm

ReplyModify the comment. Delete the comment.
i create index
create index pf_idx on Parts.Nop_PartsFamilyAttribute([Key]) include ([Value])
but nothing affect index seek

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2022-01-05T10:29:00

First of all, you're looking at the percentages; What is the cost of each operation compared to others in this query.
Since it's a percentage, the sum will always be 100 so if one percentage is reduced another gets higher. So looking only at the percentages is a wild goose chase.

The obvious question: Is the query slow and causing problems? If not, no need to go further.

If the query is causing problems you should consider which plan would produce the result as easy as possible. Since we don't have your database nor your data it's impossible to do this on your behalf but some food for thought
- What is the conditions that restricts the most is it (FT.MappingDoneFlag=0 and FT.PartLevel=0) or d.partid is null. Which ever is the most restrictive, place that table first. Looking at the join types, probably ft since d is left join. Actually this is not always necessary but helps the mindset
- If the condition (FT.MappingDoneFlag=0 and FT.PartLevel=0) fetches only small portion of rows from the table, ensure that those columns are indexed, if possible in a single index having the most restrictive column first
- Ensure that Parts.Nop_Part.PartID is indexed
- Ensure that Parts.Nop_PartsFamilyAttribute.PartFamilyID is indexed preferably along with [Key] as the second column in the index
- Ensure that ExtractReports.dbo.TPls.ZPLID is indexed
- Ensure that ExtractReports.dbo.TPLNewData.PartID is indexed with CodeTypeID
- Ensure that [ExtractReports].[dbo].[TradeCodesDelete].partid is indexed along with codetypeid and partlevel. Again the most restrictive column first

Having that said you also have a peculiar condition. You make a left join to

[ExtractReports].[dbo].[TradeCodesDelete]

with

d.partid=FT.partid and d.codetypeid=FT.codetypeid and d.partlevel=0

however in WHERE clause you explicitly state that partid must be null

d.partid is null

So d.partid will never be equal to FT.partid, is this intentional?