Thanks, Simon, informative article, but ...

.... it doesn't really explain the stuff, that I wrote. The closest I get to
an explanation, when reading this is 'These statistics are created for
columns where the optimizer would have to estimate the approximate density
or distribution otherwise'.

I knew this, but I still do not know, why the optimizer needs to know the
density and/or distribution?? I can see no valid reason, and therefore I can
see no good reason for enabling auto-creation of stats.

What I probably looking for is a good example, where the use of an
automatically created stat saves time, cycles and IOs

Best Rgds - Jesper

"Simon Hayes" <sql@hayes.ch> skrev i en meddelelse
news:4072f05a$1_2@news.bluewin.ch...
>
> "Jesper Jensen" <moellemand@post.tdcadsl.dk> wrote in message
> news:40727f2e$0$237$edfadb0f@dread12.news.tele.dk. ..
> > Hello group.
> >
> > I have an issue, which has bothered me for a while now:
> >
> > I'm wondering why the column statistics, which SQL Server wants me to
> > create, if I turn off auto-created statistics, are so important to the
> > optimizer?
> >
> > Example: from Northwind (with auto create stats off), I do the
following:
> >
> > SELECT * FROM Customers WHERE Country = 'Sweden'
> >
> > My query plan show a clustered index scan, which is expected - no index
> > exists for Country. BUT, the query plan also shows, that the optimizer
is
> > missing a statistic on Country, which tells me, that the optimizer would
> > benefit from knowing this.
> >
> > I cannot see why? (and I've been trying for a while now).
> >
> > If I create the missing statistics, nothing happens in the query plan
(and
> > why should it?). I could understand it, if the optimizer suggested an
> index
> > on Country - this would make sense, but if creating the missing index,
> query
> > analyzer creates the statistics with an empty index, which seems to me
to
> be
> > less than usable.
> >
> > I've been thinking long and hard about this, but haven't been able to
> reach
> > a conclusion It has some relevance to my work, because allowing the
> > optimizer to create missing statistics limits my options for designing
> > indexes (e.g. covering) for some rather wide tables, so I'm thinking why
> not
> > turn it off altogether. But I would like to know the consequences - hope
> > somebody has already delved into this, and knows a good explanation.
> >
> > Rgds
> > Jesper
> >
> >
>
>
http://msdn.microsoft.com/library/de.../statquery.asp
>
> Simon
>
>
>