Execution Plan -- Clustered Index Update

  • SQLDraggon

    Right there with Babe

    Points: 719

    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:"

  • GSquared

    SSC Guru

    Points: 260824

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

    Old Hand

    Points: 371

    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 🙂

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

    Old Hand

    Points: 371

    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.

  • marilou

    SSC-Addicted

    Points: 404

    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 🙂

  • giles.clapham

    SSCommitted

    Points: 1976

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

    SSC Enthusiast

    Points: 109

    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?

  • Bhuvnesh

    SSC Guru

    Points: 59344

    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;-)

  • nikhil.satam

    SSC Journeyman

    Points: 77

    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!!

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply