i work on sql server 2019 when update table [Parts].[SourcingNotMappedParts]
update still very slow
so how to enhance it
update statment i need to enhance it as below
update s set s.PriorityLevel='I1' FROM Z2DataCore.parts.SourcingNotMappedParts s
inner join extractreports.dbo.SourcingNotMappedPartsIDI1 g on g.SourcingNotMappedPartsID=s.SourcingNotMappedPartsID
estimated execution plan
https://www.brentozar.com/pastetheplan/?id=HkBWmbWZ9
actual execution plan
https://www.brentozar.com/pastetheplan/?id=S16tXbWb5
tables i create with indexes
CREATE TABLE [dbo].[SourcingNotMappedPartsIDI1](
[SourcingNotMappedPartsID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [SourcingNotMappedPartsIDI1_IDX] Script Date: 3/5/2022 8:02:52 AM ******/
CREATE UNIQUE CLUSTERED INDEX [SourcingNotMappedPartsIDI1_IDX] ON [dbo].[SourcingNotMappedPartsIDI1]
(
[SourcingNotMappedPartsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [Parts].[SourcingNotMappedParts](
[SourcingNotMappedPartsID] [int] IDENTITY(1,1) NOT NULL,
[GivenManufacture] [nvarchar](200) NULL,
[CompanyId] [int] NULL,
[SourceTypeID] [int] NULL,
[GivenPartNumber_Non] [nvarchar](200) NULL,
[VCompanyId] [int] NULL,
[PriorityLevel] [nvarchar](10) NULL,
CONSTRAINT [PK_Parts.SourcingNotMappedParts] PRIMARY KEY CLUSTERED
(
[SourcingNotMappedPartsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_NotMapped_NonalphaPartCompany] Script Date: 3/5/2022 7:40:36 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_NonalphaPartCompany] ON [Parts].[SourcingNotMappedParts]
(
[GivenPartNumber_Non] ASC,
[VCompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_NotMapped_SourceType] Script Date: 3/5/2022 7:40:36 AM ******/
CREATE NONCLUSTERED INDEX [IX_NotMapped_SourceType] ON [Parts].[SourcingNotMappedParts]
(
[SourceTypeID] ASC,
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
so how to make update faster