Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is that definitely your exact query and table structure? Not sure why an eager spool would be added here, –  Martin Smith Feb 6 '12 at 16:19
    
@MartinSmith It's not, but the remainder of the table contains non-key columns that are proprietary and confidential. : / –  Nick Vaccaro Feb 6 '12 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. –  Martin Smith Feb 6 '12 at 16:23
    
Sure, will update question. –  Nick Vaccaro Feb 6 '12 at 16:24
    
Barking up the wrong tree with this question. Will post a different question... –  Nick Vaccaro Feb 6 '12 at 16:35
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
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 '12 at 18:41
    
@cairnz but it only exists at the leaf level –  JNK Feb 21 '12 at 18:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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