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

Question

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

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

Answer 1

Hi @ahmed salah
As far as i think, the 98% is relative figure. This is a cost as per total query flow and it does not co-relate with the time.
However, if this query is using Clustered Index and taking time, then you can create a Non-Clustered covering Index that contains all the columns needed to enhance the query.
Like this:

CREATE NONCLUSTERED INDEX [IDX_ChemicalID] ON [Parts].[FMDMaster] ([ChemicalID] ASC)  
INCLUDE ([StrSubstance], [StrMass], [strCASNumber], [strHomogeneousMaterialName], [strHomogeneousMaterialMass])  
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  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

high cost on clustered index scan

An index scan means the engine uses the index to fetch all data from the table; similar to a table scan.
And the engine does this, because there is no suitable index.
And you can't create one, because all columns do have the data type nvarchar(3500); what's that for a table design?

At the moment you have to live with the scans.