Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?

Question

1.00/5 (1 vote)

See more:

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 16-May-22 16:35pm

ahmed_sa

Updated 16-May-22 22:23pm

Add a Solution

1 solution

Add a Solution

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

CHill60 · Answer 1 · 2022-05-16T22:23:00

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

Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?

1 solution

Solution 1

Add your solution here

Preview 0

Why execution plan high cost on clustered index scan and how to mimize cost of clustered index scan ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us