Indexes created when auto update statistics is set to On

Indexes created when auto update statistics is set to On

Post by Srinivas Chagant » Tue, 18 Jul 2000 04:00:00



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

 
 
 

Indexes created when auto update statistics is set to On

Post by Carlos Eduardo Roja » Tue, 18 Jul 2000 04:00:00


These are not indexes, there are statistics that SQL Server creates
automatically, to optimize access to tables.
Since they are just statistics (not indexes), they won't cause any deadlocks
on your database.
HTH...
--
------------------------------------
Carlos Eduardo Rojas

Quote:> 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


 
 
 

Indexes created when auto update statistics is set to On

Post by Brian Mora » Tue, 18 Jul 2000 04:00:00


adding to the original question... you should really leave this setting on.
In most cases it's highly beneficial to have it on... yes might be some
cases where it could be less effecient, but... don't take this the wrong
way... if you have to ask what the _WA stats are... you probally don't have
the background to judge in which cases auto stats should be disabled... I
suspect you're much better off with them on...

--
Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


> These are not indexes, there are statistics that SQL Server creates
> automatically, to optimize access to tables.
> Since they are just statistics (not indexes), they won't cause any
deadlocks
> on your database.
> HTH...
> --
> ------------------------------------
> Carlos Eduardo Rojas


> > 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

 
 
 

1. Auto create/ auto update statistics, server 7.0

        I was inspired by another posting to this NG and checked the statistics
on a few tables in my DB. I am having problems for quite a long time
that SQL server sometimes chooses very stupid execution plans. I found
out that statistics on one of the very frequented tables was updated
last time at the end of March. But auto create/update statistics is
turned on. Is this normal? At what moments SQL server decides to update
the statistics? Should I rather schedule the job that would force an
update of the statistics at that moment?
        I could hardcode the execution plan into my SQL queries, but there are
those rare moments when SQL server comes up with really good plans (even
better than mine :) and I also do not fancy the idea of writing hints
into the code.

Ivan

2. rst.Delete Takes 30-60 seconds in an OBBCdirect workspace

3. statistics auto create indexes what are they?

4. trigger on delete

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

6. Need references on database query languages (Ariel, SQL, DIL, etc.)

7. Performance Issues with UPDATE STATISTICS WITH FULLSCAN on _WA_Sys auto statistics

8. SQL server could not be started

9. Update Statistics vs. Drop/Create Index

10. update statistics created unecessary indexes

11. Auto create, auto update stats:

12. CREATE STATISTICS needed when creating an index?

13. auto update statistics