Unexpected index scan instead of seek on update

Question

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

score 1 · Answer 1 · answered May 28 '18 at 11:09

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.

score 1 · Answer 2 · answered May 28 '18 at 12:11

Just to add one bit: If the code you posted is indeed is what the application is using, then note that the optimizer has no knowledge of the contents of the variable. Without knowing the value of the variable, the optimizer could use density information, if such exist. If not, it will use hardwired estimates (such as 10% for =, 25% for BETWEEN, 30% for >, or whatever the percentages are. This might be part of the story why an index wasn't suggested.

Unexpected index scan instead of seek on update

2 Answers2