2

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
4
  • check actual execution plan brentozar.com/pastetheplan/?id=Sk3-4JGAK Jan 28 at 21:46
  • 2
    How many total rows are there in the [Parts].[ManufacturingData] table? I'm surprised to see such a huge index seek operation.
    – J.D.
    Jan 29 at 0:16
  • 3
    What are you planning on doing with the 32 million rows now that they exist in both tables? Jan 29 at 10:10
  • 1
    There is almost no way to speed this up at all, as you are reading the exact data you want. You might want to look at your disk performance, you are reading and writing from the same disk, perhaps use different filegroups. Copying 32 million rows isn't really something you normally need to do, I suggest you rethink why you want to do it in the first place Jan 30 at 13:51

4 Answers 4

1

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.

4

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.

5
  • I think you have a misunderstanding of Scan vs Seek. "generally faster to scan the entire table at that point and filter out the unwanted rows, than to seek against so many rows" is only true if you are seeking each row individually. However this is a single range seek on anything that is not null (in other words, seek to the first non-null row and scan forward), so it's certainly more performant this way. Look carefully at the plan XML, you will see <SeekKeys><IsNotNull>....MSLID The filtered index will not save any time, as the same amount of rows will be read, it will save space Jan 30 at 13:44
  • @Charlieface Hmm interesting. I'm not as familiar with looking through the plan XML so I definitely missed this minute detail that would make a significant difference. Under what case would "seeking each row individually" occur instead and how would one identify this in the plan instead?
    – J.D.
    Jan 30 at 15:32
  • 1
    That would be immediately obvious even from the graphical part: a seek for each row would show up on the lower side of a join, where the lookup key is pushed through from another part of the plan. Whereas here there is nowhere for a key to come from, the seek key is a fixed value NULL. Also Number Of Executions is 1 which is another tell-tale sign of this type of seek (here it's 10 because it's parallel on 10 threads), whereas a lookup key would have an execution for every row. As I said, this seek is fundamentally different, and does not suffer the same perf problems as a key lookup Jan 30 at 17:35
  • @Charlieface I think I follow you. You're saying in the case of a non-covering nonclustered index that needs to do an additional key lookup operation, the index seek would be substantially slower because of each individual lookup that needs to occur. But because OP's index is covering, and furthermore because the predicate is against a fixed value (as opposed to a set of values in a column) only a single index seek needs to occur to find the branch / subtree of rows WHERE MSLID IS NOT NULL?...Then it's essentially a single scan operation that occurs to fetch those non-null leaves?
    – J.D.
    Jan 30 at 18:00
  • 2
    @J.D. Yes, the term you are looking is a "range scan". See also an excellent article from Paul White Jan 30 at 18:03
2

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
0
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.