NONCLUSTERED index seek on mslid is slow

I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly.

Below is the actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK

How can I enhance performance?

Below is table definition

 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

In addition to other right answers, you could also try an alternative method to improve the performance, that could be to create indexed view as below:

CREATE VIEW parts.manufacturingdata_Indexed
WITH SCHEMABINDING AS
select partid,mslid from parts.manufacturingdata m with(nolock)
where mslid is  not null

After creating above view create index as below:

CREATE UNIQUE CLUSTERED INDEX IX_manufacturingdata
    ON parts.manufacturingdata_Indexed
     (partid,mslid);

Post creation of above View and Index, you may re-write your query as:

insert into ExtractReports.dbo.manufactureparts
(select * from parts.manufacturingdata_Indexed)

I am changing the query to insert into instead of select * into for reusability of code. In case of select into, you always need to drop the underlying table.

Please let us know if this helps.

I’d be interested in the number of total rows you have in your Parts.ManufacturingData table and what the total runtime currently is for your query. Does the number of rows the execution plan says it’s returning (roughly 32 million) actually make sense for your query?

Maybe you’ll find a filtered index saves you a little bit of time since it’ll pre-materialize only the data you want. How about an index with this definition, does it make any difference?

CREATE NONCLUSTERED INDEX IX_ManufacfuringData_MSLID_Filtered ON Parts.ManufacturingData (MSLID) WHERE MSLID IS NOT NULL;

Note you shouldn’t need to do anything different to your query to use the above filtered index once it’s created. But you should check the execution plan to ensure the optimizer chose this new index over any other indexes on your table when the query runs.

If that doesn’t make any difference, the other thing you can try is adding the FORCESCAN hint to your query like so:

select  partid,mslid 
into ExtractReports.dbo.manufactureparts
from parts.manufacturingdata m with(nolock, FORCESCAN)
where mslid is  not null

This would tell the optimizer to use a scan operation instead of seek against your data. This would generally be more performant if roughly a majority of your data in the table meets the criteria of your WHERE clause. I.e. it’s generally faster to scan the entire table at that point and filter out the unwanted rows, than to seek against so many rows. But hard to say if this’ll help your circumstances without knowing your data or testing it.

Please note query hints should be used cautiously and only in circumstances where alternative optimization methods are not possible. Some query hints limit the number of available execution plans that the optimizer can choose from, and therefore can result in an error being thrown when certain queries using those hints try to execute.

In this case, I think using the FORCESCAN hint is likely ok, as your query is simple, and I don’t believe it limits the number of query plans as much as other hints do.

Mybe you can try with a nonclustered columnstore index on the same fileds.
This could possibly lead to a batch mode & compression so you can spare something in reading.

microsoft doc

example:

CREATE NONCLUSTERED COLUMNSTORE INDEX 
[NCCIX_Parts_ManufacturingData_MSLID] ON [Parts].[ManufacturingData]
 (
     [MSLID] ASC
 )
GO

Your index seek is returning 32 million rows. You have a good plan, that’s just a lot of data to be reading.

You could consider explicitly creating the destination table before your try to insert into it.