Why clustered index is update on update of field which not included in that index (Ms SQL)?

Question

We are using MS SQL Server 2005.

Hi, i am performing UPDATE statement on a database table. Lets say this table has next colums:

int Id PK
int Column1
int Column2

It also has several Index:

Unique Clustered (Id)
Non-Unique Non-Clustered (Column1)
Non-Unique Non-Clustered (Column2)

I do next operation:

 UPDATE  [dbo].[Table] 
    SET Column1 = @Value1
    WHERE Column1 = @Param1
      AND Column2 = @Param2

Actual execution plan after that looks like this:

Which says that 86% of time was spent on updating clustered index, which does not include column i have just changed.

This operation should run hundreds of thousands times with web application disabled, which means it is very time critical.

So, does anybody have any idea why things are going this way and if it can be fixed somehow? Does this question make any sense? I am ready to provide more information if needed.

Also, the operator shown included 2 index updates. Look at the properties. — usr, Aug 02 '13 at 15:29
Add a condition of and Column1 <> @Value1. Taking the lock is the expensive part. — paparazzo, Aug 02 '13 at 16:54

spencer7593 · Accepted Answer · 2014-03-18T06:01:50.170

The "clustered index" is the actual table. All of the columns of the table are in the "clustered index" (with some exceptions for "out of row" storage for lobs, etc.)

When you change the value of a column, it has to be changed in the table pages, as well as in any index that the column appears in.

In terms of performance for quickly locating the rows to be updated (for your particular query), an index on dbo.Table(Column1,Column2) or dbo.Table(Column2,Column1) would be the most appropriate.

If it's possible that the column being modified already has the value being assigned (i.e. @Param1 and @Value both represent the same value, then adding another predicate may improve performance by avoiding a lock being obtained on the row.

UPDATE [dbo].[Table]
   SET Column1 = @Value1
 WHERE Column1 = @Param1
   AND Column2 = @Param2
   AND Column1 <> @Value1

Why clustered index is update on update of field which not included in that index (Ms SQL)?

1 Answers1