Click here to Skip to main content
15,310,056 members
Please Sign up or sign in to vote.
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
Updated 5-Jan-22 10:29am
v2
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

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?
   
Comments
ahmed_sa 6-Jan-22 7:09am
   
when create index on key and value both
as
CREATE INDEX Tradecodessd_idx on Parts.Nop_PartsFamilyAttribute([key],[value])
i get error
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'Tradecodessd_idx' has maximum length of 4404 bytes. For some combination of large values, the insert/update operation will fail.
Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 2004 bytes for the index 'Tradecodessd_idx' exceeds the maximum length of 1700 bytes for nonclustered indexes.
The statement has been terminated.
Wendelius 6-Jan-22 10:28am
   
Yes that's a length limitation for an index. Based on the SQL statement you've shared I see no reason to include value in the index.

Instead I'd index columns PartFamilyID and Key together as previously suggested.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900