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.


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

Update statement:

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?

  • Is that definitely your exact query and table structure? Not sure why an eager spool would be added here, Feb 6, 2012 at 16:19
  • @MartinSmith It's not, but the remainder of the table contains non-key columns that are proprietary and confidential. : / Feb 6, 2012 at 16:21
  • Well can't you just anonymize them with Col1, Col2 etc? The eager spool will probably be there for Halloween Protection but the query you posted and indexes you say you have don't appear to require this. Feb 6, 2012 at 16:23
  • Sure, will update question. Feb 6, 2012 at 16:24
  • Barking up the wrong tree with this question. Will post a different question... Feb 6, 2012 at 16:35

1 Answer 1


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.

  • Could also add that the non-clustered index also has the clustering key in the non-clustered index, just for additional information. In this case the IX_Name index (or whatever it is called), also has the id column since it is the clustering key
    – cairnz
    Feb 21, 2012 at 18:41
  • @cairnz but it only exists at the leaf level
    – JNK
    Feb 21, 2012 at 18:47
  • I know this answer was 5 years ago but thank you. I've been looking for the answer to this forever!
    – Kalmino
    Mar 1, 2018 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.