Using 64 Bit server running 64 bit SQL 2005 sp2...
I saw a comment that Statistics can exist that aren't associated with an
index. (See
http://decipherinfosys.wordpress.com...nd-statistics/ )

I have found that seems to be true, as i have 3 such examples (in a 12 Gb
database of 229 tables). Of the 3, 2 are on one table.

What purpose do statistics serve if not associated with an index?


--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

Blue Sky,

SQL Server does this when it feels that the statistics would it develop
a better query plan.

http://www.microsoft.com/technet/pro...stats.mspx#ERC
SQL Server creates single-column statistics automatically when compiling
queries. These statistics are created for columns where the optimizer would
otherwise have to estimate the approximate density or distribution.

RLF

"Blue Sky" <BlueSky@discussions.microsoft.com> wrote in message
news:F338031D-A4B7-49DE-A1A4-6D04C75D1CC9@microsoft.com...
> Using 64 Bit server running 64 bit SQL 2005 sp2...
> I saw a comment that Statistics can exist that aren't associated with an
> index. (See
> http://decipherinfosys.wordpress.com...nd-statistics/ )
>
> I have found that seems to be true, as i have 3 such examples (in a 12 Gb
> database of 229 tables). Of the 3, 2 are on one table.
>
> What purpose do statistics serve if not associated with an index?
>
>
> --
> The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

In my example, where it only occurs 3 times (out of perhaps 1000+ stats...)
are these 3 opportunities for tuning? Does anyone else see these and take
some particular action?
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)


"Russell Fields" wrote:

> Blue Sky,
>
> SQL Server does this when it feels that the statistics would it develop
> a better query plan.
>
> http://www.microsoft.com/technet/pro...stats.mspx#ERC
> SQL Server creates single-column statistics automatically when compiling
> queries. These statistics are created for columns where the optimizer would
> otherwise have to estimate the approximate density or distribution.
>
> RLF
>
> "Blue Sky" <BlueSky@discussions.microsoft.com> wrote in message
> news:F338031D-A4B7-49DE-A1A4-6D04C75D1CC9@microsoft.com...
> > Using 64 Bit server running 64 bit SQL 2005 sp2...
> > I saw a comment that Statistics can exist that aren't associated with an
> > index. (See
> > http://decipherinfosys.wordpress.com...nd-statistics/ )
> >
> > I have found that seems to be true, as i have 3 such examples (in a 12 Gb
> > database of 229 tables). Of the 3, 2 are on one table.
> >
> > What purpose do statistics serve if not associated with an index?
> >
> >
> > --
> > The Spirit gives life; the flesh counts for nothing! (Jn 6:63)
>
>
>

> In my example, where it only occurs 3 times (out of perhaps 1000+
> stats...)
> are these 3 opportunities for tuning? Does anyone else see these and take
> some particular action?

These might indicate tuning opportunities but maybe not. Just because
auto-created statistics exist doesn't necessarily mean that the columns
should be indexed or that indexing will improve performance (same applies to
sys.dm_db_missing_index views). Even without indexes, the stats allow the
optimizer to make more informed execution plan choices.


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/