Post reply

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

  • May 17, 2022 at 2:31 am

    #4034023

    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

  • May 17, 2022 at 6:34 am

    #4034105

    You cannot index all those nvarchar fields.  So the sql engine has to do a full table scan to look for the matches.

    How to post data/code on a forum to get the best help.

  • May 17, 2022 at 1:28 pm

    #4034308

    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 2 (of 2 total)

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