question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked OlafHelper-2800 answered

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

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
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @ahmedsalah-1628
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.