February 19, 2009 at 10:43 am
When looking at the execution plan of a query it shows a 'Clustered Index Update' @ a cost of 85%. I understand what it is ... but does anyone know what can cause that number to jump so high and how to get it down to some manageable level ??
Any and all help is welcome and greatly appreciated.
Enjoy
"Give them the tools:crazy: ... Not the keys:smooooth:"
February 19, 2009 at 11:05 am
Since that's the actual update of the actual table, there really isn't a way to directly reduce the cost of it.
It wouldn't happen to be updating one or more of the columns you cluster on, would it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 11:29 am
Is that a particularly expensive query? The total cost of all the operators in the query will be 100, so if it's a simple query, there's nothing wrong with one operator being 85% of the total cost.
Can you perhaps post the exec plan for us to take a look at?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 9:47 am
A clustered index must be re-ordered when it is updated. That means the whole index is basically reorganized, and that is why it's expensive. A primary key (PK) is usually a clustered index but not necessarilly always, however, I would advise that a PK should be clustered.
But also, a custered index is not necessarilly always a primary key. Columns that regulary get updated should preferrably NOT be part of a clustered index, especially in cases where the table row count grows large. If a column (or more), other than the PK, must be part of an index, then make the index non-clustered where possible. If the table however will always stay relatively small in rows count, then updating clustered indexes shouldn't have that much of a performance impact. However, each scenario is unique, and is heavily dependant on how many times the update statement is called (think concurrency on a busy server).
Also, what GilaMonster said is true. The query may look expensive, but the percentage is relative to the other steps in the batch/query. It only tells you which part cost you the most.
Somewhere in the query/batch there is most likely an update statement that updates the value of the PK column(s) of a table, or at least a column (or more) that is part of a clustered index. If the index is not a primary key, consider changing the index to a non-clustered index. Otherwise, if you can determine that the index is not used very much or not at all, then you can consider removing the index entirely.
Hope this helped 🙂
November 29, 2010 at 10:38 am
Please note: Almost 2 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 11:03 am
GilaMonster (11/29/2010)
Please note: Almost 2 year old thread.
I know 🙂
If it doesn't help the author, it might help someone who was searching for answers :-). I found this post via a search. The post may now be considered "more complete/helpful" even if very old.
November 23, 2011 at 9:18 am
Thank you Gila! This post helped. I had a similar question and got my answer by this thread even if it's already 2 yrs old 🙂
June 8, 2012 at 5:50 am
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
June 8, 2012 at 7:23 am
Because the clustered index includes at the leaf level every single column of the table, regardless of the physical implementation of their storage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2012 at 5:46 am
One of my query execution plan shows 'Clustered Index Update' @ a cost of 152%. From the above threads, I understand what it is. But cost is too much. What should be my next steps to figure out the issue and resolve it? How to get it down?
November 9, 2012 at 4:15 am
amarendra.sahoo (11/5/2012)
One of my query execution plan shows 'Clustered Index Update' @ a cost of 152%.
are you using any third party tool like sql sentry ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 18, 2016 at 10:43 pm
I have a similar kind of issue with my Execution Plan showing as Clustered Index Update cost as 98%
Whereas in my Update query I am not even updating the Clustered Index/Primary key column.
I am just using the primary key column in the where clause for equating with the parameter value.
My update script is:
BEGIN TRAN
update [MM_TItemCurrentStock] set
[OpeningStock] = 0,
[InwardStock] = 0,
[OutwardStock] = 0,
[ClosingStock] = 0,
[OpeningRate] = 0,
[OpeningValue] = 0,
[InwardRate] = 0,
[InwardValue] = 0,
[OutwardRate] = 0,
[OutwardValue] = 0,
[ClosingRate] = 0,
[ClosingValue]= 0,
[DirectSaleStock]= 0,
[DirectSaleRate]= 0,
[DirectSaleValue]= 0,
[TransferStock]= 0,
[TransferRate]= 0,
[TransferValue]= 0
where (OrganizationUnitID = @LoginOrganizationUnitID)
COMMIT
Table MM_TItemCurrentStock has primary key constraint on columns: ItemID and OrganizationUnitID
Can anybody please help me to resolve this higher Clustered Index Update Cost issue. Thanks in advance!!
July 19, 2016 at 2:02 am
You are updating the clustered index, as the clustered index *is* the table. All of the columns in the table are part of the clustered index (not the key, but in the index)
There's nothing at all wrong with a 98% cost for the clustered index update. The costs are percentages, they have to add to 100, so all that tells you is that the only part of the plan that has a significant cost is the clustered index scan.
Don't look just at the %. Is the query slow, that's got to be the first thing to check. Also, the goal of tuning is not to change percentages of operators in a plan. It's to make the query faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply