Query plan question

Is there a way we can figure out if the query optimiser is using a
particular stat/s ( Non indexed specifically ) to determine a query plan ?
I understand when missing stats are noticed, its obviously looking for it
but if we dont see any, can we tell if its looking at some non indexed stats
to come to a conclusion .
Also how does update stats affect performance on a highly OLTP system ? Does
it include blocking ,etc. ?sql server will use any stats available,
make a table, populate one column with unique values, but
not indexed, then query against it, notice that the
execution plan will have a fairly accurate estimate of the
expected row count, even though it is scanning the entire
table
stats will matter if it influences the plan, notice that
loop joins may exhibit a spike in plan cost at ~130 rows,
so there is a tendency to shift to scan
>--Original Message--
>Is there a way we can figure out if the query optimiser
is using a
>particular stat/s ( Non indexed specifically ) to
determine a query plan ?
>I understand when missing stats are noticed, its
obviously looking for it
>but if we dont see any, can we tell if its looking at
some non indexed stats
>to come to a conclusion .
>Also how does update stats affect performance on a highly
OLTP system ? Does
>it include blocking ,etc. ?
>
>.
>|||> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
When does this happen ? When does it make this table you referred to ? Is
this at the time of update stats or create stats or every time a query runs
.
You also mentioned that it will use any stats available. Does it even use
those that may not be needed ?
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
What scan are you talking about ?
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
> stats will matter if it influences the plan, notice that
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
> >--Original Message--
> >Is there a way we can figure out if the query optimiser
> is using a
> >particular stat/s ( Non indexed specifically ) to
> determine a query plan ?
> >I understand when missing stats are noticed, its
> obviously looking for it
> >but if we dont see any, can we tell if its looking at
> some non indexed stats
> >to come to a conclusion .
> >
> >Also how does update stats affect performance on a highly
> OLTP system ? Does
> >it include blocking ,etc. ?
> >
> >
> >.
> >|||the following white paper contains a good description of the statistics
framework in SQL Server 2000 and probably answers all your questions.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/statquery.htm
Peter Zabback
SQL Server Development
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OFA7oOocDHA.1696@.TK2MSFTNGP10.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> When does this happen ? When does it make this table you referred to ? Is
> this at the time of update stats or create stats or every time a query
runs
> .
> You also mentioned that it will use any stats available. Does it even use
> those that may not be needed ?
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> What scan are you talking about ?
> "joe chang" <jchang6@.yahoo.com> wrote in message
> news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> > stats will matter if it influences the plan, notice that
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> >
> > >--Original Message--
> > >Is there a way we can figure out if the query optimiser
> > is using a
> > >particular stat/s ( Non indexed specifically ) to
> > determine a query plan ?
> > >I understand when missing stats are noticed, its
> > obviously looking for it
> > >but if we dont see any, can we tell if its looking at
> > some non indexed stats
> > >to come to a conclusion .
> > >
> > >Also how does update stats affect performance on a highly
> > OLTP system ? Does
> > >it include blocking ,etc. ?
> > >
> > >
> > >.
> > >
>