Query plan update with strange behavior

259 Views Asked by At

I have a table defined as the script below

CREATE TABLE Schema1.Object1(
    Column1 [int] IDENTITY(1,1) NOT NULL,
    Column2 [int] NOT NULL,
    Column3 [int] NOT NULL,
    Column4 [varchar](255) NOT NULL,
    Column5 [varchar](15) NOT NULL,
    Column6 [varchar](255) NOT NULL,
    Column7 [varchar](100) NOT NULL,
    Column8 [varchar](50) NOT NULL,
    Column9 [varchar](50) NOT NULL,
    Column10 [varchar](100) NOT NULL,
    Column11 [varchar](50) NOT NULL,
    Column12 [varchar](50) NOT NULL,
    Column13 [date] NOT NULL,
    Column14 [int] NOT NULL,
    Column15 [date] NOT NULL,
    Column16 [int] NOT NULL,
    Column17 [date] NOT NULL,
    Column18 [int] NOT NULL,
    Column19 [varchar](255) NOT NULL,
    Column20 [tinyint] NOT NULL,
    Column21 [varchar](100) NOT NULL,
    Column22 [varchar](30) NOT NULL,
    Column23 [tinyint] NULL,
 CONSTRAINT [Object1_key0] PRIMARY KEY CLUSTERED 
(
    Column1 ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = ?) ON Column24
) ON Column24
GO

CREATE NONCLUSTERED INDEX [Object1_custom1] ON Schema1.Object1
(
    Column5 ASC
)
INCLUDE(Column1,Column13) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = ?) ON Column24
GO

On this table I created an indexed view.

I update my table with the script below.

(@0 varchar(15),@1 datetime2(7),@2 datetime2(7),@3 int,@4 varchar(100),@5 int)
UPDATE [Object1]
SET [Column5] = @0, [Column13] = @1, [Column14] = @2, [Column15] = @3, [Column21] = @4
WHERE ([Column1] = @5)

I would expect the first query plan, but from yesterday I'm detecting the second one that hurt the update performance. The index read for view update, when it works as expected, is the primary key of the updated table. When it doesn't works the index read is the index on the updated column and it's a index scan. Why SQL can decide to use the second plan in a simple update as mine that works for primary key only?

enter image description here

enter image description here

0

There are 0 best solutions below