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.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.
----
Louis Davidson - drsql@.hotmail.com
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 :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:126401c54102$af790090$a601280a@.phx.gbl...
> 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.|||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 S
(OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
[MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
[ControlingDate]='Jul 1 2004 12:00AM') ORDERED
FORWARD)",5,8,7,Clustered Index S,Clustered Index
S,"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 S
(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 S,Clustered Index
S,"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.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>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 :)
>"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:126401c54102$af790090$a601280a@.phx.gbl...
have
What
truth
just
>
>.
>|||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.
----
Louis Davidson - drsql@.hotmail.com
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 :)
"Mets Fan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3e01c5411d$25d96b70$a401280a@.phx.gbl...
> 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 S
> (OBJECT:([DB1].[dbo].[CustomerProfileMonthly].
> [MerchantProfileMonthly_PK] AS [a]), SEEK:([a].
> [ControlingDate]='Jul 1 2004 12:00AM') ORDERED
> FORWARD)",5,8,7,Clustered Index S,Clustered Index
> S,"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 S
> (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 S,Clustered Index
> S,"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
> ,,,,,,,,,,,,,,,,,
>
> you post the pre-run
> requires an update to
> index.
> --
> interested in
> message
> have
> What
> truth
> just

Clustered Index Scan Operator has like a warning sign

When i did a "Display Execution Plan", I noticed there was a yellow
triangular sign with an exclamation mark for the Clustered Index Scan
Operator. Why is that so ?
So this is what i see
select * from table1 where date1 = '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator without the warning sign
select * from table1 where date1 > '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator with the warning sign
Do I need to do something to make the warning sign go ?Try updating statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?|||Hi Hassan,
If you hover the mouse cursor over the node with the warning, it should
display an explanation of the warning. Your two queries both look fine - it
will depend on what the actual warning is.
Cheers,
Jim
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?

Clustered Index Scan Operator has like a warning sign

When i did a "Display Execution Plan", I noticed there was a yellow
triangular sign with an exclamation mark for the Clustered Index Scan
Operator. Why is that so ?
So this is what i see
select * from table1 where date1 = '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator without the warning sign
select * from table1 where date1 > '2007-11-10 11:00' -- This query shows a
clustered Index Scan operator with the warning sign
Do I need to do something to make the warning sign go ?Try updating statistics.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?|||Hi Hassan,
If you hover the mouse cursor over the node with the warning, it should
display an explanation of the warning. Your two queries both look fine - it
will depend on what the actual warning is.
Cheers,
Jim
"Hassan" <hassan@.test.com> wrote in message
news:%23jhqgDOSIHA.5264@.TK2MSFTNGP02.phx.gbl...
> When i did a "Display Execution Plan", I noticed there was a yellow
> triangular sign with an exclamation mark for the Clustered Index Scan
> Operator. Why is that so ?
> So this is what i see
> select * from table1 where date1 = '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator without the warning sign
> select * from table1 where date1 > '2007-11-10 11:00' -- This query shows
> a clustered Index Scan operator with the warning sign
> Do I need to do something to make the warning sign go ?