Just update one column, strange execution plan

Question

I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime. The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query. There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

I would not expect the query to update any index, because last_read is not indexed in any way, and book_id is not changed.

What am I missing?

Answer 1

Let's do a small thought exercise:

1. How do you think SQL Server finds the row with that book_id?
2. How do you think it finds (not updates) the value for last_read?

Remember that the clustered index is the table. If you update the table, you must update at least the clustered index.

Let me continue the thought exercise by asking a different question - do you think there is (or should be) a more efficient way of updating this value, than by finding the row via the clustered index, and changing the value there?

Let's leave heaps out of this for now, but the concept is similar there.

Answer 2

Every column in the table is contained within the clustered index this is why you are seeing a clustered index update. The primary key is used to define how the rows are sorted within each page of the clustered index.

From Clustered and Nonclustered Indexes Described

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The key part that specifically relates to your question is here:

Indexes are automatically maintained for a table or view whenever the table data is modified.