Create the following index:
create index pf_idx
on Parts.Nop_PartsFamilyAttribute([Key],PartFamilyID)
include ([Value])
The problem currently is that even though it is "seeking", it's not doing so efficiently, plus it is estimating +4MM rows returned from those seeks into the hash-join, which basically is where all of the time is going. It appears that the Seek is only matching the [Key] value and then relying on the hash-join to do the PartFamilyID filtering, but at 4MM rows it's a lot of overhead. The index that I suggested above should alleviate much of that by enabling it to more efficiently seek (or maybe even scan) the rows that it needs, matching both [Key] and PartFamilyID.
----
As @MartinSmith pointed out, you have a much bigger problem with your second join which I missed:
INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.Value
The problem here is that while pl.ZIPID is an INT, fm.Value is an NVARCHAR which makes the whole JOIN https://www.brentozar.com/blitzcache/non-sargable-predicates/ .
The solution to this is as follows:
Assuming that you cannot just change the Nop_PartsFamilyAttribute.Value column to INT, then add a persisted calculated column:
->
`ALTER Parts.Nop_PartsFamilyAttribute
ADD IntValue AS (Try_Parse([Value] AS INT)) PERSISTED;`
Create the following index (instead of the one I proposed above).
->
create index pf_idx2
on Parts.Nop_PartsFamilyAttribute([Key],IntValue,PartFamilyID)
Change your second join to:
INNER JOIN ExtractReports.dbo.TPls pl WITH (NOLOCK) ON pl.ZPLID = fm.IntValue
Finally, change the column list of your query to this:
->
SELECT DISTINCT
FT.PartId,
TN.Code,
FT.CodeTypeID,
FT.SourceTypeID,
FT.RevisionID,
CAST(fm.IntValue AS Nvarchar(2000)) AS [Value],
FT.PartLevel,
GETDATE(),
1