0

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 enter image description here

The details of the index scan

enter image description here

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

Improve this question
7
  • 1
    What's the DDL for the table? Any other indexes?
    – vonPryz
    Commented May 28, 2018 at 10:04
  • Do you have an index on (tasklockedby) (with or without INCLUDE)? If there isn't one, what else is left for the optimizer than to do a table scan?
    – ypercubeᵀᴹ
    Commented May 28, 2018 at 10:08
  • @ypercubeᵀᴹ thanks. I expected the optimiser to realise that there is a missing index and suggest a missing one. Instead, it seems content with performing a scan.
    – Luke
    Commented May 28, 2018 at 11:04
  • @vonPryz thanks. The DDL has been provided in the body of the question. There are 9 other indexes on this table, but non cover even one of the required fields.
    – Luke
    Commented May 28, 2018 at 11:05
  • reason may be that : each taskLockedBy have many rows like in above example 28637.so even if you have index on taskLockedBy it won't be utilise .Because optimizer think that it is easier to scan than seek.Optmizer find the best plan "quickly".If each taskLockedBy has fewer rows then it will prompt for missing index message.
    – KumarHarsh
    Commented May 28, 2018 at 11:17

2 Answers 2

Reset to default
1

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.

Improve this answer
5
  • Thanks. I created this table in a test environment and added some indexes. The index with the includes has better performance as it includes the other two fields, and prevents a key lookup which has the same performance cost at the index seek i.e. the index with the includes is twice as fast as the index without the includes. I had to force the use of the index though with a with (index [ix_FluxInbox_tasklockedby])
    – Luke
    Commented May 28, 2018 at 11:36
  • Not sure how you compared the 2 indexes (with INCLUDE and not). I don't think there would be much difference as you are updating the value tasklockedby anyway, so the UPDATE will have to update both the CI and this index. What key lookup is prevented?
    – ypercubeᵀᴹ
    Commented May 28, 2018 at 11:43
  • Have a look at this answer: Why does SQL Server use a better execution plan when I inline the variable? and this superb blog post by Paul White: Parameter Sniffing, Embedding, and the RECOMPILE Options
    – ypercubeᵀᴹ
    Commented May 28, 2018 at 12:39
  • Does timing (and the plan) change when you try the various options described in that article? (using a fixed value instead of a parameter, using OPTIMIZE FOR (@parameter UNKNOWN) or OPTION RECOMPILE, etc)
    – ypercubeᵀᴹ
    Commented May 28, 2018 at 12:40
  • Thanks the links were helpful. Yeah the plan changes (eg estimated row count changes to 1) however the execution plan still does not suggest a missing index. I will create the index later on today though and see if the execution plan will make use of it
    – Luke
    Commented May 28, 2018 at 13:21
1

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.

Improve this answer
2
  • Thanks. The fields in the update statement were added last week. Only 600+ of the 12million records have data in the tasklockedby field. Is this why the estimated row count is so low?
    – Luke
    Commented May 28, 2018 at 12:16
  • Again, in the code yo posted, the optimizer has no knowledge of the contents of the variable. And if there isn't statistics on the columns (which you can check if you want), then there is no knowledge of the density (how many distinct values) either. I'd start with checking of there is statistics. USe DBCC SHOW_STATISTICS, pass the table name and the column name.
    – Tibor Karaszi
    Commented May 29, 2018 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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