SQL Server Performance

Why is it doing a Clustered Index Update??

Discussion in 'Performance Tuning for DBAs' started by makallus, May 5, 2004.

  1. makallus New Member

    Why does the following update do a Clustered Index Update even though the column I am updating is not in the clustered index (as per the execution plan)? Any wisdom or reference would be appreciated!


    CREATE TABLE test
    (
    oneint,
    twochar(4),
    threechar(4),
    fourchar(4)
    primary key (one,two)
    )

    go

    INSERT INTO test
    SELECT 1,'one','one','one'
    UNION SELECT 2,'two','two','two'
    UNION SELECT 3,'thre','thre','thre'

    go

    SET SHOWPLAN_ALL ON

    go
    UPDATE test
    SET three = 'zero'
    WHERE one = 1 AND two = 'one' AND three = 'one'
  2. Luis Martin Moderator

    According your script, create table has not index at all.
    If you don't create a cluster index, SQL create a phanton cluster index and, may be, is what you see in update script.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

  3. gaurav_bindlish New Member

    Luis can you please elaborate on the phantom clustered Index?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  4. Luis Martin Moderator

  5. gaurav_bindlish New Member

    I knew that was coming.... If you look into the end, the common consensus was there is no phantom index. This was my initial mistake,

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. Luis Martin Moderator

    Yes, reading carefully, you are rigth.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

  7. makallus New Member


    The "primary key (one,two)" will create a "clustered, unique, primary key located on PRIMARY" which consists of the columns [one] and [two]. If I update column [three] why would SQL want to do a Clustered Index Update -- a very costly job in SQL land.
  8. gaurav_bindlish New Member

    Just a shot in the dark.... could it be because SQL Server is updating the data page which is part of a clustered index. But this condition will be always true ain't it?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  9. makallus New Member


    I had considered some of that. The test table you see only has fixed char(4) columns to test if SQL needed to update the clustered index with new page pointers from shifting data lengths. Perhaps it needs to to that under the hood anyway -- but I was hoping for a more solid explaination.
  10. Luis Martin Moderator

    From Microsoft:<br /><br />Clustered Index Update<br />The Clustered Index Update physical operator updates input rows in the clustered index specified in the Argument column.<br /><br />If a WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />) predicate is present, only those rows that satisfy this predicate are updated. If a SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />) predicate is present, it indicates the value to which each updated column is set. If a DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />) predicate is present, this lists the values that this operator defines. These values may be referenced in the SET clause or elsewhere within this operator and elsewhere within this query.<br /><br /><br />HTH<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br />
  11. makallus New Member

    Luis,<br /><br />I see each of those components in the query execution plan but I fail to understand why the execution plan thinks that by updating a column unrelated to the clustered index that it then needs to update the clustered index. I see here that the execution plan wants to update the PK__ which is generated from the "primary key" statement in the table create script. Columns [one] and [two] belong to the primary key. Column [three] does not. Furthermore I can run an update statement on column [three] with no WHERE critera and there is still a Clustered Index Update. Just the nature of the beast??<br /><br />Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[lme].[dbo].[test].[PK__test__79A81403]), <br />SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test].[three]=Convert([@1])), <br />DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ConstExpr1006]=Convert([@1])), <br />WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test].[one]=Convert([@2])))
  12. satya Moderator

    Check the following:
    The table in question contains a composite clustered index.
    Updates are made to one of the clustered index keys.
    The table contains a unique nonclustered index on one of the clustered index keys.
    The table has a CHECK constraint that checks for null values on any of the clustered index keys.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. gaurav_bindlish New Member

    Satya, I guess none of the conditions is true here.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  14. Chappy New Member

    I cannot explain this. I have ran some tests and the only time a clustered index update was not used, was when there was not a clustered index present.<br /><br />The only time a plain Index update was used, was when I was updating a field in the clustered index, and even then a clustered index update was used additionally. I guess this is understandable because updating the clustered field would require updating of the non clustered index. But as to why an index update wasnt used when clearly available is beyond me. Could it be we have misunderstood the semnatics of a clustered index update node appearing in the execution plan?<br /><br />For people wanting to play, heres a little test script<br /><pre><br />set nocount on<br />CREATE TABLE dbo.test1 (a int NOT NULL, b int NOT NULL, c int)<br />GO<br />ALTER TABLE dbo.test1 WITH NOCHECK ADD CONSTRAINT PK_a_b PRIMARY KEY CLUSTERED (a, b)<br />GO<br />declare @a int, @b int, @c int, @i int<br />select @i = 40000<br />while (@i &gt; 0)<br />begin<br /> select @a = floor(rand()*100000), @b = floor(rand()*100000), @c = floor(rand()*100000), @i=@i-1<br /> insert into dbo.test1 (a,b,c) values (@a, @b, @c)<br />end<br /><br />SELECT '------------ WITH CLUSTERED INDEX ---------------'<br />GO<br />SET SHOWPLAN_TEXT ON<br />GO<br />update dbo.test1 set c = 5 <br />update dbo.test1 set c = 5 where a &gt; 50000<br />update dbo.test1 set c = 5 where c &gt; 50000<br />GO<br />SET SHOWPLAN_TEXT OFF<br />GO<br /><br />SELECT '------------ WITH NONCLUSTERED INDEX ALSO ---------------'<br />CREATE INDEX idx_a_b ON dbo.test1 (a, b)<br />GO<br />SET SHOWPLAN_TEXT ON<br />GO<br />update dbo.test1 set c = 5 <br />update dbo.test1 set c = 5 where a &gt; 50000<br />update dbo.test1 set a = 5 where c &gt; 50000<br />GO<br />SET SHOWPLAN_TEXT OFF<br />GO<br />DROP TABLE dbo.test1<br />set nocount off<br /></pre><br /><br />and my (tidied up) output<br /><pre><br />------------------------------------------------- <br />------------ WITH CLUSTERED INDEX ---------------<br /><br />update dbo.test1 set c = 5<br /><br />StmtText <br />------------------------------------------------------------------------------------------- <br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[@1]))<br /> |--Top(ROWCOUNT est 0)<br /> |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]))<br /><br /><br /><br />update dbo.test1 set c = 5 where a &gt; 50000<br /><br />StmtText <br />------------------------------------------------------------------------------------------------------------------------ <br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[@1]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[a] &gt; 50000))<br /><br />(1 row(s) affected)<br /><br /><br /><br /><br />update dbo.test1 set c = 5 where c &gt; 50000<br /><br />StmtText <br />-------------------------------------------------------------------------------------------------------------- <br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[@1]))<br /> |--Top(ROWCOUNT est 0)<br /> |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]&gt;50000) ORDERED)<br /><br />(3 row(s) affected)<br /><br /><br /><br /> <br />--------------------------------------------------------- <br />------------ WITH NONCLUSTERED INDEX ALSO ---------------<br /><br />update dbo.test1 set c = 5<br /><br />StmtText <br />------------------------------------------------------------------------------------------- <br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[@1]))<br /> |--Top(ROWCOUNT est 0)<br /> |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[idx_a_b]), ORDERED FORWARD)<br /><br /><br /><br />update dbo.test1 set c = 5 where a &gt; 50000<br /><br />StmtText <br />-------------------------------------------------------------------------------------------------------------- <br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[@1]))<br /> |--Top(ROWCOUNT est 0)<br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[idx_a_b]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[a] &gt; 50000) ORDERED FORWARD)<br /><br /><br />update dbo.test1 set a = 5 where c &gt; 50000<br /><br />StmtText <br />-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- <br /> |--Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[idx_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[b1007]=[test1].<b>, [a1006]=RaiseIfNull([test1].[a]), [IdxBmk1005]=RaiseIfNull([Bmk1003])))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[a] ASC, [test1].<b> ASC, [Bmk1003] ASC, [Act1004] ASC))<br /> |--Split<br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]=[test1].[c], [test1].<b>=[test1].<b>, [test1].[a]=RaiseIfNull([test1].[a])))<br /> |--Collapse(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[a], [test1].<b>))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[a] ASC, [test1].<b> ASC, [Act1004] ASC))<br /> |--Split<br /> |--Top(ROWCOUNT est 0)<br /> |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DBA].[dbo].[test1].[PK_a_b]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test1].[c]&gt;50000) ORDERED)<br /></pre>
  15. gaurav_bindlish New Member

    So that's what I though is happening. Clustered index update means the update of the data page and not necessarily an index page of the clustered index.

    Good work Chappy!

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  16. makallus New Member


    Beautiful work Chappy. Your findings are about the same as my own. I can see that we may infer that a clusterd index update means something a little different than "I am updating my clustered index now" as perhaps as bindlish says, "I am updating a page where the clustered index resides" or perhaps something even more exotic like "I am making sure my clustered index pointers are correct and updating some statistics". I appreciate all your consideration.
  17. mike_ot New Member

    Wow. Thanks for the great info guys. I could not figure out why a Clustered Index Update was happening on simple triggerless table while updating a non-indexed column.

Share This Page