Unexpected index scan instead of seek on update We have a third party application which is performing badly. An update statement is performing a scan instead of a seek, and is not suggesting a missing index. Why is this happening?

I was expecting the execution plan to suggest a missing index like

create nonclustered index [ix_FluxInbox_tasklockedby] on Flux.dbo.FluxInbox (tasklockedby) include (tasklockedrole, tasklockedon)


Below is the execution plan


The details of the index scan



As requested, the DDL of the table

CREATE TABLE [dbo].[FluxInbox](
    [id] [numeric](19, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [appStatus] [varchar](20) NULL,
    [branchCode] [int] NULL,
    [groupName] [varchar](200) NULL,
    [inboxType] [varchar](20) NULL,
    [origin] [varchar](50) NULL,
    [parentPid] [numeric](19, 0) NULL,
    [pid] [numeric](19, 0) NULL,
    [priority] [int] NULL,
    [processCreatedOn] [datetime] NULL,
    [processStatus] [varchar](20) NULL,
    [processTarget] [varchar](50) NULL,
    [tag01] [varchar](100) NULL,
    [tag02] [varchar](100) NULL,
    [tag03] [varchar](100) NULL,
    [tag04] [varchar](100) NULL,
    [tag05] [varchar](100) NULL,
    [tag06] [varchar](100) NULL,
    [tag07] [varchar](100) NULL,
    [tag08] [varchar](100) NULL,
    [tag09] [varchar](100) NULL,
    [tag10] [varchar](100) NULL,
    [taskActivatedOn] [datetime] NULL,
    [taskCommand] [varchar](20) NULL,
    [taskCompletedOn] [datetime] NULL,
    [taskCreatedBy] [varchar](50) NULL,
    [taskCreatedOn] [datetime] NULL,
    [taskDescription] [varchar](100) NULL,
    [taskScheduledTime] [datetime] NULL,
    [taskStatus] [varchar](20) NULL,
    [taskTarget] [varchar](50) NULL,
    [tid] [numeric](19, 0) NULL,
    [userName] [varchar](50) NULL,
    [channel] [varchar](50) NULL,
    [processCompletedOn] [datetime] NULL,
    [taskLockedBy] [varchar](50) NULL,
    [taskLockedRole] [varchar](100) NULL,
    [taskLockedOn] [datetime] NULL,
 CONSTRAINT [PK__FluxInbo__3213E83F349C8E92] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO


There are 9 other indexes, but none cover even one of the required fields
Since there is no index on (tasklockedby) or any index that has that column in any column position, the only option for the optimizer to find which rows need to be updated is to do a table scan (so clustered index scan in this case).

I don't know why an index suggestion was not made.

I would suggest a simple index on (tasklockedby), without any INCLUDE columns, if you want to improve the efficiency of the UPDATE statement. If you have other (select) queries that need to get more columns in the result set, then you many be better with a different one.