I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly.
Below is the actual execution plan
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.
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.