I know this topic is now OVER 2 years old but it's still very relative to a scenario I'm dealing with today.

I have a table, basically the following (reduced for simplicity)

Create Table Item

(

ID Int Identity(1,1) Primary Key,

CreatedOn DateTime,

ModifiedOn DateTime,

Version int not null,

Data Ntext,

Customer_ID nvarchar(10) not null,

SID nvarchar(10) not null

Duration int,

Cost Money

)

There are around a dozen other columns.

Customer_ID and SID are indexed together as a composite index.

I've recorded an update that is taking over 30seconds and the execution plan says "Clustered Index Update" is taking most of the effort.

There is another table called Communication, it's used to filter down the update, it's indexed and as a select statement it runs fine.

My update statement is:

UPDATE Item SET data = @data,

version = @version,

last_modified = GETUTCDATE()

FROM Communication

INNER JOIN Item ON Communication.list_sid = Item.list_sid AND Communication.customer_id = Item.customer_id

WHERE (Communication.sid = @CommunicationSid)

AND (Item.sid = @ItemSid)

AND (Communication.customer_id = @customer_id)

@data is a large chunk of XML, but my understanding is that Ntext is not kept in the same page as the rest of the table, version and modifiedon aren't increasing in length and ID is an identity.

So why would that cause a Clustered Index Update?

Any help appreciated

Giles