NONCLUSTERED index seek on mslid is slow


  • QA Engineer

    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



  • 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 https://www.mssqltips.com/sqlservertip/3296/why-the-sql-server-forcescan-hint-exists/ 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 https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2