Clustered Index Update  
Author Message
bmjnine





PostPosted: Thu Apr 14 10:56:51 CDT 2005 Top

SQL Server Developer >> Clustered Index Update

In my estimated execution plan for a UPDATE it says I have
a 55% cost to do a "Clustered Index Update/Update". What
is odd is that I am not updating either column in the
PK/Clustered Index. Now I know this is the estimated
execution plan, but why does it say this? The real truth
will be told when I run the update statement, but I'm just
wondering about this mis-read of the execution.

SQL Server191  
 
 
Louis





PostPosted: Thu Apr 14 10:56:51 CDT 2005 Top

SQL Server Developer >> Clustered Index Update Can you post the update? Sounds interesting. Also, can you post the pre-run
plan and the post run plan?

Remember that any update to any column in the table requires an update to
the clustered index, since all columns are part of the index.

--
----------------------------------------------------------------------------

SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)


> In my estimated execution plan for a UPDATE it says I have
> a 55% cost to do a "Clustered Index Update/Update". What
> is odd is that I am not updating either column in the
> PK/Clustered Index. Now I know this is the estimated
> execution plan, but why does it say this? The real truth
> will be told when I run the update statement, but I'm just
> wondering about this mis-read of the execution.


 
 
Mets





PostPosted: Thu Apr 14 13:09:49 CDT 2005 Top

SQL Server Developer >> Clustered Index Update There is not data presently so the statistics reflect
that, perhaps that could be the issue. But as you asked,
here is the resultset of SET SHOWPLAN_ALL. I exported it
to excel and then saved as CSV. You will have to import
and set the delimiter to a comma.


"UPDATE a SET IncntvRevWAncil =
b.totalQualRevOrg , IncntvRevWOAncil =
b.totalQualRevNew FROM dbo.CustomerProfileMonthly
a JOIN DB2.dbo.t_Detail b ON
a.CustNumber = b.CustNumber AND
a.ControlingDate = b.ControlingDate AND
a.ControlingDate = CAST('20040701' AS
DATETIME)",5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL,3.81E-
02,NULL,NULL,UPDATE,0,NULL
" |--Clustered Index Update(OBJECT:([DB1].[dbo].
[CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK]), SET:
([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
[CustomerProfileMonthly].[IncntvRevWAncil]=
[Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
02,NULL,NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
=Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
=Convert([t_detail_2004_07].
[totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
[Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
|--Top(ROWCOUNT est
0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
|--Sort(DISTINCT ORDER BY:([Bmk1000]
ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Compute Scalar(DEFINE:
([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
[totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
[t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
[totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
[t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]=[t_detail_2004_07].
[totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
[totalQualRevNew]",NULL,PLAN_ROW,0,1
" |--Nested Loops(Inner Join,
OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
Loops,Inner Join,OUTER REFERENCES:([a].
[CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
[t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
[totalQualRevOrg]",NULL,PLAN_ROW,0,1
" |--Clustered Index Seek
(OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD)",5,8,7,Clustered Index Seek,Clustered Index
Seek,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
05,107,3.28E-03,"[Bmk1000], [a].
[CustNumber]",NULL,PLAN_ROW,0,1
" |--Clustered Index Seek
(OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD)",5,9,7,Clustered Index Seek,Clustered Index
Seek,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
[PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
[CustNumber]=[a].[CustNumber]) ORDERED
FORWARD","[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
[t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
,,,,,,,,,,,,,,,,,


>-----Original Message-----
>Can you post the update? Sounds interesting. Also, can
you post the pre-run
>plan and the post run plan?
>
>Remember that any update to any column in the table
requires an update to
>the clustered index, since all columns are part of the
index.
>
>--
>----------------------------------------------------------
------------------

>SQL Server MVP
>
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)

message

>> In my estimated execution plan for a UPDATE it says I
have
>> a 55% cost to do a "Clustered Index Update/Update".
What
>> is odd is that I am not updating either column in the
>> PK/Clustered Index. Now I know this is the estimated
>> execution plan, but why does it say this? The real
truth
>> will be told when I run the update statement, but I'm
just
>> wondering about this mis-read of the execution.
>
>
>.
>
 
 
Louis





PostPosted: Thu Apr 14 14:24:42 CDT 2005 Top

SQL Server Developer >> Clustered Index Update I would guess that might be the thing. Since there is no data, there is
very little cost to do the other stuff, but I would hold off worry about
optimzing until you have data :) Seriously, as long as you are careful to
realize that your join criteria must be a 1-1 relationship between table A
and table B, it is probably fine.

--
----------------------------------------------------------------------------

SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)


> There is not data presently so the statistics reflect
> that, perhaps that could be the issue. But as you asked,
> here is the resultset of SET SHOWPLAN_ALL. I exported it
> to excel and then saved as CSV. You will have to import
> and set the delimiter to a comma.
>
>
> "UPDATE a SET IncntvRevWAncil =
> b.totalQualRevOrg , IncntvRevWOAncil =
> b.totalQualRevNew FROM dbo.CustomerProfileMonthly
> a JOIN DB2.dbo.t_Detail b ON
> a.CustNumber = b.CustNumber AND
> a.ControlingDate = b.ControlingDate AND
> a.ControlingDate = CAST('20040701' AS
> DATETIME)",5,1,0,NULL,NULL,1,NULL,1,NULL,NULL,NULL,3.81E-
> 02,NULL,NULL,UPDATE,0,NULL
> " |--Clustered Index Update(OBJECT:([DB1].[dbo].
> [CustomerProfileMonthly].[MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860]))",5,2,1,Clustered Index Update,Update,"OBJECT:
> ([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK]), SET:
> ([CustomerProfileMonthly].[IncntvRevWOAncil]=[Expr2861],
> [CustomerProfileMonthly].[IncntvRevWAncil]=
> [Expr2860])",NULL,1,1.68E-02,0.000001,61,3.81E-
> 02,NULL,NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew])))",5,3,2,Compute
> Scalar,Compute Scalar,"DEFINE:([Expr2860]=Convert
> ([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]=Convert
> ([t_detail_2004_07].[totalQualRevNew]))","[Expr2860]
> =Convert([t_detail_2004_07].[totalQualRevOrg]), [Expr2861]
> =Convert([t_detail_2004_07].
> [totalQualRevNew])",1,0,0.0000001,53,2.13E-02,"[Bmk1000],
> [Expr2860], [Expr2861]",NULL,PLAN_ROW,0,1
> |--Top(ROWCOUNT est
> 0),5,4,3,Top,Top,NULL,NULL,1,0,0.0000001,53,2.13E-
> 02,"[Bmk1000], [t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]",NULL,PLAN_ROW,0,1
> |--Sort(DISTINCT ORDER BY:([Bmk1000]
> ASC)),5,5,4,Sort,Distinct Sort,DISTINCT ORDER BY:
> ([Bmk1000] ASC),NULL,1,1.13E-02,1.00E-
> 04,53,0.02130264,"[Bmk1000], [t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Compute Scalar(DEFINE:
> ([t_detail_2004_07].[totalQualRevOrg]=[t_detail_2004_07].
> [totalQualRevOrg], [t_detail_2004_07].[totalQualRevNew]=
> [t_detail_2004_07].[totalQualRevNew]))",5,6,5,Compute
> Scalar,Compute Scalar,"DEFINE:([t_detail_2004_07].
> [totalQualRevOrg]=[t_detail_2004_07].[totalQualRevOrg],
> [t_detail_2004_07].[totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew])","[t_detail_2004_07].[totalQualRevOrg]=
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]=[t_detail_2004_07].
> [totalQualRevNew]",1,0,0.0000001,53,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevOrg], [t_detail_2004_07].
> [totalQualRevNew]",NULL,PLAN_ROW,0,1
> " |--Nested Loops(Inner Join,
> OUTER REFERENCES:([a].[CustNumber]))",5,7,6,Nested
> Loops,Inner Join,OUTER REFERENCES:([a].
> [CustNumber]),NULL,1,0,0.00001254,554,9.94E-03,"[Bmk1000],
> [t_detail_2004_07].[totalQualRevNew], [t_detail_2004_07].
> [totalQualRevOrg]",NULL,PLAN_ROW,0,1
> " |--Clustered Index Seek
> (OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD)",5,8,7,Clustered Index Seek,Clustered Index
> Seek,"OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD","[Bmk1000], [a].[CustNumber]",1,3.20E-03,7.96E-
> 05,107,3.28E-03,"[Bmk1000], [a].
> [CustNumber]",NULL,PLAN_ROW,0,1
> " |--Clustered Index Seek
> (OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD)",5,9,7,Clustered Index Seek,Clustered Index
> Seek,"OBJECT:([DB2].[dbo].[t_detail_2004_07].
> [PK_t_detail_2004_07]), SEEK:([t_detail_2004_07].
> [CustNumber]=[a].[CustNumber]) ORDERED
> FORWARD","[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",1,3.20E-03,7.96E-
> 05,456,6.65E-03,"[t_detail_2004_07].[totalQualRevNew],
> [t_detail_2004_07].[totalQualRevOrg]",NULL,PLAN_ROW,0,3
> ,,,,,,,,,,,,,,,,,
>
>
>>-----Original Message-----
>>Can you post the update? Sounds interesting. Also, can
> you post the pre-run
>>plan and the post run plan?
>>
>>Remember that any update to any column in the table
> requires an update to
>>the clustered index, since all columns are part of the
> index.
>>
>>--
>>----------------------------------------------------------
> ------------------

>>SQL Server MVP
>>
>>Compass Technology Management - www.compass.net
>>Pro SQL Server 2000 Database Design -
>>http://www.apress.com/book/bookDisplay.html?bID=266
>>Blog - http://spaces.msn.com/members/drsql/
>>Note: Please reply to the newsgroups only unless you are
> interested in
>>consulting services. All other replies may be ignored :)

> message

>>> In my estimated execution plan for a UPDATE it says I
> have
>>> a 55% cost to do a "Clustered Index Update/Update".
> What
>>> is odd is that I am not updating either column in the
>>> PK/Clustered Index. Now I know this is the estimated
>>> execution plan, but why does it say this? The real
> truth
>>> will be told when I run the update statement, but I'm
> just
>>> wondering about this mis-read of the execution.
>>
>>
>>.
>>