Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execution Plan -- Clustered Index Update Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 10:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:58 AM
Points: 109, Visits: 293
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 ... Not the keys"
Post #660590
Posted Thursday, February 19, 2009 11:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #660597
Posted Thursday, February 19, 2009 11:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #660626
Posted Monday, November 29, 2010 9:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 4, 2013 12:39 AM
Points: 95, Visits: 347
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
Post #1027411
Posted Monday, November 29, 2010 10:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
Please note: Almost 2 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1027452
Posted Monday, November 29, 2010 11:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 4, 2013 12:39 AM
Points: 95, Visits: 347
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.
Post #1027481
Posted Wednesday, November 23, 2011 9:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:01 AM
Points: 60, Visits: 133
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


Post #1211143
Posted Friday, June 8, 2012 5:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 4:38 AM
Points: 66, Visits: 342
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
Post #1313072
Posted Friday, June 8, 2012 7:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1313116
Posted Monday, November 5, 2012 5:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 5, 2012 5:41 AM
Points: 1, Visits: 0
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?
Post #1381029
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse