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,
Version int not null,
Customer_ID nvarchar(10) not null,
SID nvarchar(10) not null
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()
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