January 5, 2022 at 5:22 pm
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 :
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]
January 6, 2022 at 3:36 pm
The query being executed for those curious and not wanting to click the link:
insert into [ExtractReports].[dbo].[TradeCodesDelete](PartID,Code,CodeTypeID,SourceTypeID,RevisionID,ZPLID,PartLevel,CreatedDate,FlagDelete)
----select count(1) from [ExtractReports].[dbo].[TradeCodesDelete]
---update [ExtractReports].[dbo].[TradeCodesDelete] set FlagDelete=2 where FlagDelete=1
SELECT distinct FT.PartId,TN.Code,FT.CodeTypeID,FT.SourceTypeID,FT.RevisionID,fm.[Value],FT.PartLevel,getdate(),1
FROM Parts.Nop_Part pt with(nolock)
INNER JOIN Parts.Nop_PartsFamilyAttribute fm with(nolock) ON pt.PartsFamilyID=fm.PartFamilyID AND fm.[Key]=20281007
INNER JOIN ExtractReports.dbo.TPls pl with(nolock) ON pl.ZPLID=fm.Value
INNER JOIN Parts.TradeCodes FT with(nolock) ON pt.PartID=ft.PartID AND FT.PartLevel=0
INNER JOIN ExtractReports.dbo.TPLNewData TN with(nolock) ON TN.PartID = FT.PartID AND TN.CodeTypeID = FT.CodeTypeID
left join [ExtractReports].[dbo].[TradeCodesDelete] d on d.partid=FT.partid and d.codetypeid=FT.codetypeid and d.partlevel=0
WHERE
TN.Code <> FT.Code and FT.MappingDoneFlag=0 and d.partid is null
My opinion, which is similar to what I posted in a previous discussion about a similar query - cross database queries cannot use statistics, so you are going to get bad estimates on those tables and thus bad plans. Copy the data to a temp table or table variable and it will help with performance.
Now, with those hash match and index seek, are those REALLY the heavy performance hitters OR are your estimates bad? You have several indexes in there where the estimated number of rows is less than 10, some are 1, so the estimated plan is LIKELY not a realistic view of the actual execution plan.
I would post the ACTUAL execution plan and that 57% and 40% numbers will likely change and you will discover you are trying to tune the wrong thing. I have a feeling that your nested loops and/or parallelism are going to be a bigger bottleneck than an index seek across 4 million rows.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 6, 2022 at 3:51 pm
I would look at trying to find a way to reduce the number of rows returned from Parts.Nop_PartsFamilyAttribute [Estimated Number of Rows = 4305960]. Maybe look at changing the INER JOIN to a CROSS APPLY.
It would also be good to have an index on Parts.Nop_PartsFamilyAttribute that is keyed as either (PartFamilyID, [Key]) or ([Key], PartFamilyID). Which one would depend on which is more selective (the one with the most distinct values must be 1st).
SELECT DISTINCT FT.PartId,TN.Code,FT.CodeTypeID,FT.SourceTypeID,FT.RevisionID,fm.[Value],FT.PartLevel,GETDATE(),1
FROM Parts.Nop_Part pt WITH(NOLOCK)
OUTER APPLY (SELECT fma.[value] FROM Parts.Nop_PartsFamilyAttribute AS fma WITH(NOLOCK)
WHERE fma.PartFamilyID = fm.PartsFamilyID
AND fma.[Key]=20281007
) as fm
INNER JOIN ExtractReports.dbo.TPls pl WITH(NOLOCK) ON pl.ZPLID=[value]
INNER JOIN Parts.TradeCodes FT WITH(NOLOCK) ON pt.PartID=ft.PartID AND FT.PartLevel=0
INNER JOIN ExtractReports.dbo.TPLNewData TN WITH(NOLOCK) ON TN.PartID = FT.PartID AND TN.CodeTypeID = FT.CodeTypeID
LEFT JOIN [ExtractReports].[dbo].[TradeCodesDelete] d ON d.partid=FT.partid AND d.codetypeid=FT.codetypeid AND d.partlevel=0
WHERE TN.Code <> FT.Code AND FT.MappingDoneFlag=0 AND d.partid IS NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply