statistics auto create indexes what are they?

statistics auto create indexes what are they?

Post by Fran » Sat, 25 Jan 2003 17:29:32



I'm finding several indexes like this
**_WA_Sys_phyn_type_6FE99F9F type nonclustered,
statistics, auto create located on PRIMARY on column(s)
phyn_type **. Where do they come from? I can't seem to
find any documentation on them. Thanks
 
 
 

statistics auto create indexes what are they?

Post by Geoff N. Hite » Sat, 25 Jan 2003 17:37:35


Statistics are stored as indexes within SQL. Look up 'Statistical
Information' in BOL for a general description of statistics and how they are
used.

--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com


Quote:> I'm finding several indexes like this
> **_WA_Sys_phyn_type_6FE99F9F type nonclustered,
> statistics, auto create located on PRIMARY on column(s)
> phyn_type **. Where do they come from? I can't seem to
> find any documentation on them. Thanks


 
 
 

statistics auto create indexes what are they?

Post by Fran » Sat, 25 Jan 2003 17:56:23


Thanks Geoff, are these created by Sql or were they
created by someone. Can I drop them since the datatbase
maintains statistics? Thanks

Quote:>-----Original Message-----
>Statistics are stored as indexes within SQL. Look
up 'Statistical
>Information' in BOL for a general description of

statistics and how they are
>used.

>--
>Geoff N. Hiten
>Senior Database Administrator
>Careerbuilder.com



>> I'm finding several indexes like this
>> **_WA_Sys_phyn_type_6FE99F9F type nonclustered,
>> statistics, auto create located on PRIMARY on column(s)
>> phyn_type **. Where do they come from? I can't seem to
>> find any documentation on them. Thanks

>.

 
 
 

statistics auto create indexes what are they?

Post by Tibor Karasz » Sat, 25 Jan 2003 18:00:00


They are created by SQL Server. Leave them there. SQL Server created them for a reason in the
first place. :-)

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Thanks Geoff, are these created by Sql or were they
> created by someone. Can I drop them since the datatbase
> maintains statistics? Thanks

> >-----Original Message-----
> >Statistics are stored as indexes within SQL. Look
> up 'Statistical
> >Information' in BOL for a general description of
> statistics and how they are
> >used.

> >--
> >Geoff N. Hiten
> >Senior Database Administrator
> >Careerbuilder.com



> >> I'm finding several indexes like this
> >> **_WA_Sys_phyn_type_6FE99F9F type nonclustered,
> >> statistics, auto create located on PRIMARY on column(s)
> >> phyn_type **. Where do they come from? I can't seem to
> >> find any documentation on them. Thanks

> >.

 
 
 

statistics auto create indexes what are they?

Post by Geoff N. Hite » Sat, 25 Jan 2003 18:25:46


To clarify.  SQL uses statistics to optimize query performance.  You CAN
drop them, but it is usually a bad idea.  They were most likely created when
the table or index was created if 'auto-create statistics' is set on(default
is ON) for that particular database.

--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com



Quote:> They are created by SQL Server. Leave them there. SQL Server created them
for a reason in the
> first place. :-)

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



> > Thanks Geoff, are these created by Sql or were they
> > created by someone. Can I drop them since the datatbase
> > maintains statistics? Thanks

> > >-----Original Message-----
> > >Statistics are stored as indexes within SQL. Look
> > up 'Statistical
> > >Information' in BOL for a general description of
> > statistics and how they are
> > >used.

> > >--
> > >Geoff N. Hiten
> > >Senior Database Administrator
> > >Careerbuilder.com



> > >> I'm finding several indexes like this
> > >> **_WA_Sys_phyn_type_6FE99F9F type nonclustered,
> > >> statistics, auto create located on PRIMARY on column(s)
> > >> phyn_type **. Where do they come from? I can't seem to
> > >> find any documentation on them. Thanks

> > >.

 
 
 

1. Indexes created when auto update statistics is set to On

Hi,

I noticed that SQL Server creates some indexes on columns that are not
indexed when auto update statistics is set to on whenever the unindexed
column is used in a query.  The name of the index looks something like this:
_WA_Sys_first_name_06ED0088 in the sysindexes table.

My question is what kind of indexes are these?  Are these some kind of
special indexes to track statistics or are they real indexes that could
potentially cause som overhead and deadlocks.  Could not find much
information from Books online.  I am debating whether to turn off the auto
update statistics on.

Any thoughts?

Thanks,

Srinivas

2. Problem scheduling a DTS Package

3. Auto create/ auto update statistics, server 7.0

4. Timeout problem

5. CREATE STATISTICS needed when creating an index?

6. Performance Issue

7. Auto Create/Update Statistics and insert performance in SQL 7.0

8. OI1.2 Replicator

9. nonclustered, statistics, auto create located on PRIMARY

10. sp_dboption-Auto Create Statistics

11. auto create statistics

12. Auto Create Statistics disabled?

13. AUTO CREATE STATISTICS