0

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

Luca Murzio
  • 37
  • 1
  • 5
  • 1
    The execution plan is trying to update a *view index*. Did someone create an indexed view that involves this table? – Panagiotis Kanavos May 03 '22 at 09:58
  • I don't have any trigger, the second object updated is the indexed view based on my table @MartinSmith. – Luca Murzio May 03 '22 at 09:58
  • @PanagiotisKanavos yes I have an indexed view based on my table, but the first update too update the view – Luca Murzio May 03 '22 at 09:59
  • That's the answer then. The execution plan changed because now it has to modify the index data used by that view. – Panagiotis Kanavos May 03 '22 at 09:59
  • So why it used an index scan to update a single record on the view? – Luca Murzio May 03 '22 at 10:01
  • The server can't guess what the view's results will be without executing it. A view indexed is created based on those results, so when you modify the data on which that index is based, the view has to be executed again – Panagiotis Kanavos May 03 '22 at 10:02
  • In any case, you're asking people to guess what's going on with missing tables, views and indexes. There's no index scan in the execution plan. It looks like this view has *two* indexes that need to be updated. – Panagiotis Kanavos May 03 '22 at 10:08
  • @PanagiotisKanavos you have reason, I wrong the pasted plans – Luca Murzio May 03 '22 at 10:24
  • We can only guess without the view query *and* the indexes. `Index4` may not be very selective, so a seek would make little sense. Or the query may need data that's stored in `Index4` but not the index itself. Perhaps it's the 2nd or third column in the index. Instead of querying the actual table, the database engine will use the much smaller index – Panagiotis Kanavos May 03 '22 at 10:33
  • Index is selective because involve the same primary key of the table that is updating. It's a strange behavior because by design the record to be updated will be at maximum one and not even more @PanagiotisKanavos – Luca Murzio May 03 '22 at 12:02
  • Please see [paste the plan](https://www.brentozar.com/pastetheplan/instructions/) for a better way to include an execution plan in your question. – HABO May 03 '22 at 17:30

0 Answers0