Click here to Skip to main content
15,891,033 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on sql server i face issue index seek noncluster index so what this message mean execution plan as below https://www.brentozar.com/pastetheplan/?id=Bk3HHR6aY

so how to enhance performance from exection plan

table name as below

What I have tried:

CREATE TABLE [Parts].[ManufacturingData](
     [LeadFinishId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [PartID] [int] NOT NULL,
     [LeadFinishMaterial] [varchar](50) NULL,
     [CreatedDate] [datetime] NULL,
     [CreatedBy] [int] NULL,
     [ModifiedDate] [datetime] NULL,
     [Modifiedby] [int] NULL,
     [DeletedDate] [datetime] NULL,
     [DeletedBy] [int] NULL,
     [Revision_Id] [int] NULL,
     [BaseMaterialID] [int] NULL,
     [MSLID] [int] NULL,
     [MSLSource_Revision_id] [int] NULL,
     [MaximumReflowTemperatureID] [int] NULL,
     [ReflowTemperatureSource_Revision_Id] [int] NULL,
     [MaximumWaveTemperatureID] [int] NULL,
     [WaveTemperatureSource_Revision_ID] [int] NULL,
     [ReflowSolderTimeID] [int] NULL,
     [WaveSolderTimeID] [int] NULL,
     [NumberOfReflowCycleID] [int] NULL,
     [LeadFinishPlatingID] [int] NULL,
     [Comment] [varchar](100) NULL,
     [LeadfinishSourceTypeID] [int] NULL,
     [MSlSourceTypeID] [int] NULL,
     [ReflowTemperatureSourceTypeID] [int] NULL,
     [BasedOnID] [int] NULL,
     [LeadFreeProcessCapabilityID] [int] NULL,
     [BaseMaterialRevisionID] [int] NULL,
     [BaseMaterialSourceTypeID] [int] NULL,
     [UnderplatingRevisionID] [int] NULL,
     [UnderplatingSourceTypeID] [int] NULL,
     [ShelfLifeCondition] [int] NULL,
  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 ) ON [Customer]
    
 GO
    
 SET ANSI_PADDING ON
 GO

index seek used as below

 CREATE NONCLUSTERED INDEX [IDX_MSLID] ON [Parts].[ManufacturingData]
 (
     [MSLID] 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 [Customer]
 GO

USE [Z2DataCore]
GO


 ALTER TABLE [Parts].[ManufacturingData] ADD  CONSTRAINT [PK_PartID] PRIMARY KEY CLUSTERED 
 (
     [PartID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Customer]
 GO
Posted
Updated 29-Jan-22 2:52am

1 solution

Well,

By looking your execution plan the problem is not in index rather it is the insert that taking significant time. Inserts usually don't take that much time so you need to check with DBA why inserts are taking that much time.

A quick solution for your problem is to modify your IDX_MSLID and make partId part of this index. This will reduce the lookup because all data is now present in the index itself.

SQL
CREATE NONCLUSTERED INDEX [IDX_MSLID] ON [Parts].[ManufacturingData]
 (
     [MSLID] ASC,
     [PartId] ASC
 )
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900