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

  • 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
  • You cannot index all those nvarchar fields.  So the sql engine has to do a full table scan to look for the matches.

  • Very closely related to https://www.sqlservercentral.com/forums/topic/slow-execution-when-update-chemical-id-on-table-chemicalhash-so-how-to-enhance-i#post-4034307.

    As with that one, review your data types.

    Why is almost every column [nvarchar](3500)? It looks like some tool auto-generated types, and nobody corrected them. Is every column really so identical in nature that they justify the same data type and size? Do they all actually require unicode? Are they all even strings, or are some numeric values?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply