Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I work on sql server 2019

i have clustered index scan 98 percent how to minimize it please

i have execution plean have high cost on clustered index scan
as
https://www.brentozar.com/pastetheplan/?id=HkpoQtlwq

table i have issue on it

What I have tried:

CREATE TABLE [Parts].[FMDMaster](
 [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [ChemicalID] [int] NULL,
 [HashSubstance] [nvarchar](3500) NULL,
 [HashMass] [nvarchar](3500) NULL,
 [StrSubstance] [nvarchar](3500) NULL,
 [StrMass] [nvarchar](3500) NULL,
 [strCASNumber] [nvarchar](3500) NULL,
 [strHomogeneousMaterialName] [nvarchar](3500) NULL,
 [strHomogeneousMaterialMass] [nvarchar](3500) NULL,
 [HashstrCASNumber] [nvarchar](3500) NULL,
 [HashstrHomogeneousMaterialName] [nvarchar](3500) NULL,
 [HashstrHomogeneousMaterialMass] [nvarchar](3500) NULL,
PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
 [ChemicalID] 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
/****** Object:  Index [IDX_ChemicalID]    Script Date: 5/17/2022 4:20:22 AM ******/
CREATE NONCLUSTERED INDEX [IDX_ChemicalID] ON [Parts].[FMDMaster]
(
 [ChemicalID] 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
Posted
Updated 16-May-22 22:23pm

1 solution

This is your query
SQL
merge #tmpParts h
   using Parts.FMDMaster f on h.strsubstances=f.StrSubstance and h.strmass=f.strmass and h.strcasnumber=f.strcasnumber AND h.strHomogeneousMaterialName=f.strHomogeneousMaterialName AND h.strHomogeneousMaterialMass=f.strHomogeneousMaterialMass
  when matched then 
  update set h.chemicalid=f.chemicalid ;
None of strsubstances, strmass, strcasnumber, strHomogeneousMaterialName or strHomogeneousMaterialMass appear in the indices you have set up.

Try adding a non-clustered index that covers the data you are using to join in your query or re-evaluate your clustered scan.

There are plenty of resources covering how to address clustered index issues e.g. https://www.sqlshack.com/sql-server-query-execution-plan-beginners-clustered-index-operators/[^]. You can search for others
 
Share this answer
 

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