Sql-server – Clustered Index Update on Nonclustered Columns

clustered-indexexecution-plansql serversql-server-2008-r2

Using: SQL Server 2008 R2

I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are being updated are NOT part of the clustered index.

Table:

<table>
    id INT IDENTITY(1,1) -- Clustered Index
    , name VARCHAR(20) -- Nonclustered Index
    , status CHAR(1)
    , quantity INT 
    , price FLOAT

Update statement: 

UPDATE a
SET a.status = @status
    , a.quantity = @quantity
    , a.price = @price
FROM <table> a
WHERE a.name = @name

The execution plan shows an Eager Spool at 36% cost, a Clustered Index Update at 55% cost, and an Index Seek on the name index at 9%, among Compute Scalar and Top items at 0% cost.

Why is the plan showing a clustered index update? What could I do to prevent this, and prevent the eager spool?

Best Answer

When you have a clustered index on a table, the clustered index IS the table!

Mentally you can substitute "table" for "clustered index" in this instance and it will make sense.

The data for every field in every row is in your clustered index. The clustered index just sets the order of the physical pages in the database to be organized by your clustering key(s).

You can always fall back on the phone book analogy for these things, too: in your classic phone book, the data is clustered on Last Name, First Name. Each entry still has PhoneNum, Address at the leaf level but you don't order by that. The pages are in physical order by the the keys.

I can't advise on optimizing the query unless you show us the table and query you are running, but basically this cost will be paid one way or another. If you don't update the clustered index it will be a table update and a table scan.

Related Question